『ORACLE』 对永久表空间进行DML操作（11g）
SQL> create tablespace ts_users datafile
'/u01/app/oracle/oradata/enmo1/ts01.dbf' size 10m autoextend on maxsize 20m,
'/u01/app/oracle/oradata/enmo1/ts01.bak.dbf' size 10m autoextend on maxsize 20m;
Tablespace created.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_USERS ONLINE
7 rows selected.
增加数据文件
SQL> alter tablespace ts_users add datafile '/u01/app/oracle/oradata/enmo1/ts01.bak1.dbf' size 10M;
Tablespace altered.
SQL> select tablespace_name,file_id,file_name from dba_data_files where tablespace_name = 'TS_USERS';
TABLESPACE_NAME FILE_ID FILE_NAME
----------------------- ---------- ----------------
TS_USERS 6 /u01/app/oracle/oradata/enmo1/ts01.dbf
TS_USERS 7 /u01/app/oracle/oradata/enmo1/ts01.bak.dbf
TS_USERS 8 /u01/app/oracle/oradata/enmo1/ts01.bak1.dbf
修改数据文件大小
SQL> alter database datafile 8 resize 20M;
Database altered.
SQL> select file_id,bytes / 1024 / 1024 || 'M' from dba_data_files where tablespace_name = 'TS_USERS';
FILE_ID BYTES/1024/1024||'M'
---------- -----------------------------------------
6 10M
7 10M
8 20M
SQL> select file_id,file_name,autoextensible,maxbytes from dba_data_files where tablespace_name = 'TS_USERS';
FILE_ID FILE_NAME AUT MAXBYTES
---------- ------------------------------------------------------- --------------- -------------
6 /u01/app/oracle/oradata/enmo1/ts01.dbf YES 20971520
7 /u01/app/oracle/oradata/enmo1/ts01.bak.dbf YES 20971520
8 /u01/app/oracle/oradata/enmo1/ts01.bak1.dbf NO 0
SQL> alter database datafile 8 autoextend on next 5M maxsize unlimited;
Database altered.
SQL> select file_id,file_name,autoextensible,maxbytes from dba_data_files where tablespace_name = 'TS_USERS';
FILE_ID FILE_NAME AUT MAXBYTES
---------- ------------------------------ ------------------
6 /u01/app/oracle/oradata/e YES 20971520
nmo1/ts01.dbf
7 /u01/app/oracle/oradata/e YES 20971520
nmo1/ts01.bak.dbf
8 /u01/app/oracle/oradata/e YES 3.4360E+10
nmo1/ts01.bak1.dbf
删除表空间
SQL> drop tablespace TS_USERS including contents and datafiles;
Tablespace dropped.
SQL> select tablespace_name,file_id,file_name
2 from dba_data_files where tablespace_name = 'TS_USERS';
no rows selected
