Oracle10gR2:New Features-drop empty datafile

在Oracle10gR2中，Oracle允许我们直接从表空间中drop掉空的数据文件，这个删除的文件再也不会讨厌的显示在dba_data_files视图中了。

SQL> create tablespace test;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ORADG/danaly/datafile/system.264.600016955
+ORADG/danaly/datafile/undotbs1.265.600016969
+ORADG/danaly/datafile/sysaux.266.600016977
+ORADG/danaly/datafile/users.268.600016987
+ORADG/danaly/datafile/test.271.600082589

SQL> alter tablespace test add datafile;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ORADG/danaly/datafile/system.264.600016955
+ORADG/danaly/datafile/undotbs1.265.600016969
+ORADG/danaly/datafile/sysaux.266.600016977
+ORADG/danaly/datafile/users.268.600016987
+ORADG/danaly/datafile/test.271.600082589
+ORADG/danaly/datafile/test.272.600082607

6 rows selected.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+ORADG/danaly/datafile/system.264.600016955
+ORADG/danaly/datafile/undotbs1.265.600016969
+ORADG/danaly/datafile/sysaux.266.600016977
+ORADG/danaly/datafile/users.268.600016987
+ORADG/danaly/datafile/test.271.600082589
+ORADG/danaly/datafile/test.272.600082607

6 rows selected.

SQL> alter tablespace test drop datafile '+ORADG/danaly/datafile/test.272.600082607' ;

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+ORADG/danaly/datafile/system.264.600016955
+ORADG/danaly/datafile/undotbs1.265.600016969
+ORADG/danaly/datafile/sysaux.266.600016977
+ORADG/danaly/datafile/users.268.600016987
+ORADG/danaly/datafile/test.271.600082589

SQL>

虽然还有一定的限制，但是这仍然是前进了一步。

3 Comments