ORA-23515: materialized views and/or their indices exist in the tablespace

ORA-12919: Can not drop the default permanent tablespace

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  634679296 bytes

Fixed Size                                2216024 bytes

Variable Size                        507514792 bytes

Database Buffers               121634816 bytes

Redo Buffers                         3313664 bytes

Database mounted.

SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/users01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

6 rows selected.

SQL> SELECT file_name FROM dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ORCL/users01.dbf

/u01/app/oracle/oradata/ORCL/undotbs01.dbf

/u01/app/oracle/oradata/ORCL/sysaux01.dbf

/u01/app/oracle/oradata/ORCL/system01.dbf

/u01/app/oracle/oradata/ORCL/example01.dbf

• While trying to drop tablespace USERS I'm getting the below ORA-12919 error.

SQL> DROP tablespace USERS including contents;

DROP tablespace USERS including contents

*

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

SQL> alter database default tablespace example;

Database altered.

SQL> DROP  tablespace USERS including contents and datafiles cascade constraints;

DROP  tablespace USERS including contents and datafiles cascade constraints

*

ERROR at line 1:

ORA-23515: materialized views and/or their indices exist in the tablespace

• Query to find out the materialized view

SQL> select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;' from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name ='USERS');

'DROPMATERIALIZEDVIEW'||OWNER||'.'||NAME||'PRESERVETABLE;'

--------------------------------------------------------------------------------

drop materialized view SCOTT.EMP_MV PRESERVE TABLE;

SQL> drop materialized view SCOTT.EMP_MV PRESERVE TABLE;

Materialized view dropped.

• Now, we can easily drop the tablesapce including contents;

SQL> DROP  tablespace USERS including contents;                                 

Tablespace dropped.