DROP TABLESPACE时数据文件是否会自动删除

OMF和非OMF管理的数据文件在DROP TABLESPACE时是否会自动删除，做了测试：

SQL> alter system set db_create_file_dest='/oradata/data/standby' scope=both;

System altered.

SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oradata/data/standby
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
SQL> create tablespace t3 datafile size 10m;

Tablespace created.

SQL> select * from v$tablespace;

       TS# NAME                           INC
---------- ------------------------------ ---
         0 SYSTEM                         YES
         1 UNDOTBS1                       YES
         2 TEMP                           YES
         3 INDX                           YES
         4 USR                            YES
         5 KONG                           YES
         6 TEST                           YES
         9 T1                             YES
         8 LOGMNRTS                       YES
        10 T2                             YES
        11 T3                             YES

11 rows selected.

SQL> select name from v$datafile
  2  where ts# = 11;

NAME
--------------------------------------------------------------------------------
/oradata/data/standby/o1_mf_t3_464smo7s_.dbf

SQL> create table t100 (id int) tablespace t3;

Table created.

SQL> drop tablespace t3;
drop tablespace t3
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


SQL> drop tablespace t3 including contents
  2  ;

Tablespace dropped.

SQL> !ls -l /oradata/data/standby/o1_mf_t3_464smo7s_.dbf
ls: 0653-341 The file /oradata/data/standby/o1_mf_t3_464smo7s_.dbf does not exist.

看到在DROP TABLESPACE时，就算没有加上 and datafiles是选项，ORACLE也会自动删除数据文件；

SQL> create tablespace t3 datafile '/oradata/data/standby/data/t3.dbf' size 10m;

Tablespace created.

SQL> create table t100 (a int) tablespace t3;

Table created.

SQL> drop tablespace t3 ;
drop tablespace t3
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


SQL> drop tablespace t3  including contents;

Tablespace dropped.

SQL> !ls -l /oradata/data/standby/data/t3.dbf
-rw-r-----   1 oracle   dba        10493952 Jun 25 23:42 /oradata/data/standby/data/t3.dbf

没有加上and datafiles参数时，oracle不会自动删除datafiles。

SQL> create tablespace t3 datafile '/oradata/data/standby/data/t4.dbf' size 10m;

Tablespace created.

SQL> create table t100 (a int) tablespace t3;

Table created.

SQL> drop tablespace t3  including contents and datafiles;

Tablespace dropped.

SQL> !ls -l /oradata/data/standby/data/t4.dbf
ls: 0653-341 The file /oradata/data/standby/data/t4.dbf does not exist.

加上and datafiles参数时，oracle会自动删除datafiles。

SQL> alter system set db_create_file_dest='' scope=both;

System altered.

SQL> create tablespace t3 datafile '/oradata/data/standby/data/t4.dbf' size 10m;

Tablespace created.

SQL> create table t100 (a int) tablespace t3;

Table created.

SQL> drop tablespace t3  including contents and datafiles;

Tablespace dropped.

SQL> !ls -l /oradata/data/standby/data/t4.dbf
ls: 0653-341 The file /oradata/data/standby/data/t4.dbf does not exist.

SQL>