Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, 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 and Simple. Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips .
Thread Rating:
  • 33 Vote(s) - 3.27 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Droping a Tablespace
10-20-2010, 04:10 PM,
#1
Droping a Tablespace
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
10-21-2010, 04:45 AM,
#2
RE: Droping a Tablespace
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




Users browsing this thread: 1 Guest(s)