Oracle Forums Community of Oracle Professionals including Fusion/Cloud Application Consultants, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick, Simple and Absolutely Free.
Oracle Training

Thread Rating:
  • 29 Vote(s) - 3.28 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Droping a Tablespace
#1
Dear all,

can you please tell me how to drop a tablespace which has materialized views.I tried the following
drop tablespace portal including contents and datafiles;

but i get the following error message

SQL> drop tablespace portal including contents and datafiles;
drop tablespace portal including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace


there are about 200 indices in the tablespace dropping the all one by one is very cumbersome.Is there any other way i can do this and drop the tablespace.
Reply
Thanks given by:
#2
SQL> alter tablespace <tbname> offline;

Tablespace altered

SQL> drop tablespace <tbname> including contents;

drop tablespace BLUH including contents

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

Try the below script...

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 = ‘tbname’);

drop materialized view ABC.CB_BLUH_DM_DATE_VW PRESERVE TABLE;
drop materialized view ABC.CB_BLUH_ROLE_VW PRESERVE TABLE;
drop materialized view ABC.CB_BLUH_DM_INTER_VW PRESERVE TABLE;


[After MVs were dropped]

SQL> drop tablespace <tbname> including contents

ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop tablespace tbname including contents and datafiles CASCADE CONSTRAINTS;

Tablespace dropped

Reply
Thanks given by:




Users browsing this thread: 1 Guest(s)
Get Oracle Tutorials, Tips! Post a Question!