Skip to content
Advertisements

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

When I am trying to drop the tablespace, I am getting below error

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||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name =’XXXXXXX’);

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 XXXXXXX including contents and datafiles;

Tablespace dropped.

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: