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

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>