Tablespace drop
ORA-23515: materialized views and/or their indices exist in the tablespace
Solution :
Step 1: Find the materialized views and/or their indices
SQL> set head off
SQL> set newpage none
SQL> set pagesize 9999
SQL> spool drop_materialized_view.sql
SQL>select ‘drop materialized view ‘||owner||’.’||name||’ TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name =’xxxxxx’);
SQL>spool off
Step 2: Drop the materialized views and/or their indices
SQL>@drop_materialized_view.sql
Step 3: Drop the tablespace
SQL>drop tablespace xxxxxx including contents and datafiles;
Tablespace dropped.
