xxd@ETMCDB> alter tablespace ETMCDB offline;
Tablespace altered
xxd@ETMCDB> drop tablespace ETMCDB including contents and datafiles;
drop tablespace ETMCDB including contents
ORA-23515: materialized views and/or their indices exist in the tablespace
xxd@ETMCDB> select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;'
from dba_registered_snapshots where name in
from dba_registered_snapshots where name in
(select table_name from dba_tables where tablespace_name ='ETMCDB');
'DROPMATERIALIZEDVIEW'||OWNER||'.'||NAME||'PRESERVETABLE;'
--------------------------------------------------------------------------------
drop materialized view XXD.MV PRESERVE TABLE;
'DROPMATERIALIZEDVIEW'||OWNER||'.'||NAME||'PRESERVETABLE;'
--------------------------------------------------------------------------------
drop materialized view XXD.MV PRESERVE TABLE;
xxd@ETMCDB> drop materialized view XXD.MV PRESERVE TABLE;
Materialized view dropped.
xxd@ETMCDB> drop tablespace ETMCDB including contents and datafiles;
ORA-02449: unique/primary keys in table referenced by foreign keys
xxd@ETMCDB> drop tablespace ETMCDB including contents and datafiles CASCADE CONSTRAINTS;
Tablespace dropped