SYS@ PROD1>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

SYS@ PROD1>select index_name, table_owner, table_name, tablespace_name from dba_indexes where index_type='DOMAIN';

INDEX_NAME TABLE_OWNE TABLE_NAME TABLESPACE_NAME
------------------------------ ---------- ------------------------------ ------------------------------
SUP_TEXT_IDX SH SUPPLEMENTARY_DEMOGRAPHICS

SYS@ PROD1>drop index sh.SUP_TEXT_IDX ;
Index dropped.

################################################
SYS@ PROD1>drop tablespace example including contents and datafiles;
drop tablespace example including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

SYS@ PROD1>select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;'
2 from dba_registered_snapshots
3 where name in (select table_name from dba_tables where tablespace_name ='EXAMPLE');
'DROPMATERIALIZEDVIEW'||OWNER||'.'||NAME||'PRESERVETABLE;'
--------------------------------------------------------------------------------
drop materialized view SH.CAL_MONTH_SALES_MV PRESERVE TABLE;
drop materialized view SH.FWEEK_PSCAT_SALES_MV PRESERVE TABLE;
SYS@ PROD1>drop materialized view SH.CAL_MONTH_SALES_MV PRESERVE TABLE;
Materialized view dropped.
SYS@ PROD1>drop materialized view SH.FWEEK_PSCAT_SALES_MV PRESERVE TABLE;
Materialized view dropped.

################################################

SYS@ PROD1>drop tablespace example including contents and datafiles;
drop tablespace example including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SYS@ PROD1>drop tablespace example including contents and datafiles cascade constraints;
Tablespace dropped.

ORA-29857 ORA-23515 ORA-02449