SQL> drop tablespace EXAMPLE including contents and datafiles;
drop tablespace EXAMPLE including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
---提示:ORA-29857:表空间存在域索引和/次级对象
SQL> col owner format a15;SQL> col segment_name format a30;SQL> col segment_type format a20;SQL> set pagesize 300;SQL> select distinct owner,segment_name,segment_type from dba_extents where tablespace_name=‘EXAMPLE‘;
OWNER SEGMENT_NAME SEGMENT_TYPE--------------- ------------------------------ --------------------HR DEPARTMENTS TABLEOE ORDERS TABLEOE PROMOTIONS TABLEPM ONLINE_MEDIA TABLE……PM SYS_LOB0000051988C00006$$ LOBSEGMENTSH SYS_LOB0000052332C00002$$ LOBSEGMENT
175 rows selected.
SQL> col owner format a15;SQL> col segment_name format a30;SQL> col segment_type format a20;SQL> select distinct owner,segment_name,segment_type from dba_segments where tablespace_name=‘EXAMPLE‘;
OWNER SEGMENT_NAME SEGMENT_TYPE--------------- ---------------------------------------- --------------------SH PRODUCTS_PROD_CAT_IX INDEXSH CUSTOMERS TABLEHR DEPARTMENTS TABLEHR DEPT_LOCATION_IX INDEXHR JHIST_JOB_IX INDEXHR JHIST_EMPLOYEE_IX INDEXHR JHIST_DEPARTMENT_IX INDEXOE SYS_LOB0000051924C00023$$ LOBSEGMENTOE ORDERS TABLE……SH SYS_IL0000052332C00002$$ LOBINDEXSH SYS_LOB0000052332C00002$$ LOBSEGMENT
已选择175行。
SQL> drop user SH cascade;User dropped.
SQL> drop user PM cascade;User dropped.
SQL> drop user OE cascade;User dropped.
SQL> drop user IX cascade;User dropped.
SQL> drop user HR cascade;User dropped.
SQL> drop tablespace example including contents and datafiles;Tablespace dropped.
原文:http://pengai.blog.51cto.com/6326789/1899530