DROP TABLESPACE tablespace_name
[ including contents [ and datafiles ] [ CASCADE CONSTRAINT ] ];
无选项 -- 当表空间为空才能删除;
including contents — 删除表空间及对象;
including contents and datafiles — 删除表空间、对象及数据文件;
includingcontents CASCADE CONSTRAINT — 删除关联;
including contents and datafiles cascade constraint -- 含前两项。
--查询表空间对应的数据文件
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
--用户和表空间对应关系
select username,default_tablespace from dba_users;
drop tablespace SETTLE_BASE including contents and datafiles cascade constraint;
drop user SETTLEMENT cascade;
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
TABLESPACE_NAME FILE_ID BYTES/1024/1024 FILE_NAME
------------------------------ ---------- --------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTEM 9 430 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
SYSAUX 10 1300 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
UNDOTBS1 11 7005 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
USERS 12 703.75 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
MANAGE 16 7300 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/manage.dbf
SETTLE_BASE 27 100 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/settle.dbf
SETTLE_CFG_GF_DATA 28 4650 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/settle_gf.dbf
SETTLE_GF_TEMP_DATA 29 100 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/settle_gf_temp_data.dbf
SETTLE_CFG_DATA 30 1700 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/SETTLE_CFG_DATA.dbf
MANAGE_MOCK_DATA 31 100 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/manage_mock.dbf
10 rows selected.
SQL> drop tablespace SETTLE_BASE including contents and datafiles cascade constraint;
Tablespace dropped.
SQL> drop user SETTLEMENT cascade;
User dropped.
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
TABLESPACE_NAME FILE_ID BYTES/1024/1024 FILE_NAME
------------------------------ ---------- --------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTEM 9 430 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
SYSAUX 10 1300 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
UNDOTBS1 11 7005 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
USERS 12 703.75 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
MANAGE 16 7300 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/manage.dbf
SETTLE_CFG_GF_DATA 28 4650 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/settle_gf.dbf
SETTLE_GF_TEMP_DATA 29 100 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/settle_gf_temp_data.dbf
SETTLE_CFG_DATA 30 1700 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/SETTLE_CFG_DATA.dbf
MANAGE_MOCK_DATA 31 100 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/manage_mock.dbf
9 rows selected.
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
XS$NULL SYSTEM
LBACSYS SYSTEM
OUTLN SYSTEM
DBSNMP SYSAUX
APPQOSSYS SYSAUX
DBSFWUSER SYSAUX
GGSYS SYSAUX
ANONYMOUS SYSAUX
CTXSYS SYSAUX
DVSYS SYSAUX
DVF SYSAUX
GSMADMIN_INTERNAL SYSAUX
MDSYS SYSAUX
OLAPSYS SYSAUX
XDB SYSAUX
WMSYS SYSAUX
GSMCATUSER USERS
MDDATA USERS
SYSBACKUP USERS
REMOTE_SCHEDULER_AGENT USERS
PDBADMIN USERS
GSMUSER USERS
C##TEST_USER1 USERS
SYSRAC USERS
OJVMSYS USERS
SI_INFORMTN_SCHEMA USERS
AUDSYS USERS
DIP USERS
ORDPLUGINS USERS
SYSKM USERS
ORDDATA USERS
ORACLE_OCM USERS
SYS$UMF USERS
SYSDG USERS
ORDSYS USERS
MANAGE MANAGE
SETTLE_CFG_GF_DEV SETTLE_CFG_GF_DATA
SETTLE_CFG_GF SETTLE_CFG_GF_DATA
SETTLE_GF_TEMP SETTLE_GF_TEMP_DATA
SETTLE_CFG SETTLE_CFG_DATA
MANAGE_MOCK MANAGE_MOCK_DATA
43 rows selected.
SQL>