删除表空间
系统表空间不允许删除,具有活动状态即active状态的表空间也不允许删除
INCLUDING CONTENTS:删除表空间内的所有段
AND DATAFILES:删除关联的操作系统文件
CASCADE CONSTRAINTS:如果要删除的表空间之外的表引用了该表空间内表的主键和唯一键,则删除这种引用完整性约束
实验
删除mytablespace表空间
SQL> drop tablespace mytablespace including contents and datafiles;
Tablespace dropped.
SQL> select file_name,tablespace_name as tname from dba_data_files;
FILE_NAME TNAME
---------------------------------------- ------------------------------
/u01/oradata/houzhh/users01.dbf USERS
/u01/oradata/houzhh/undotbs01.dbf UNDOTBS1
/u01/oradata/houzhh/sysaux01.dbf SYSAUX
/u01/oradata/houzhh/system01.dbf SYSTEM
/u01/oradata/houzhh/example01.dbf EXAMPLE
SQL> !
[oracle@oracle11g ~]$ cd /u01/oradata/houzhh/
[oracle@oracle11g houzhh]$ ls
control01.ctl mytemp01.dbf redo02.log redo04.rdo system01.dbf undotbs01.dbf
example01.dbf redo01.log redo03.log sysaux01.dbf temp01.dbf users01.dbf
已经没有myts01.dbf,myts02.dbf 文件