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 TABLE
OE ORDERS TABLE
OE PROMOTIONS TABLE
PM ONLINE_MEDIA TABLE
……
PM SYS_LOB0000051988C00006$$ LOBSEGMENT
SH 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 INDEX
SH CUSTOMERS TABLE
HR DEPARTMENTS TABLE
HR DEPT_LOCATION_IX INDEX
HR JHIST_JOB_IX INDEX
HR JHIST_EMPLOYEE_IX INDEX
HR JHIST_DEPARTMENT_IX INDEX
OE SYS_LOB0000051924C00023$$ LOBSEGMENT
OE ORDERS TABLE
……
SH SYS_IL0000052332C00002$$ LOBINDEX
SH 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.
