Home » RDBMS Server » Server Administration » drop tablespace problem
drop tablespace problem [message #227900] Fri, 30 March 2007 02:04 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
I have a test schema (NCL) with two tablespaces WIZDATA containing tables of test schema and WIZINDX for indexes
Now these tablespaces are no longer required

SQL> drop tablespace wizdata including contents and datafiles;
drop tablespace wizdata including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop tablespace wizdata including contents;
drop tablespace wizdata including contents
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


SQL> drop tablespace wizindx including contents and datafiles;
drop tablespace wizindx including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> drop user ncl cascade;

User dropped.

SQL> drop tablespace wizdata including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace wizindx including contents and datafiles;

Tablespace dropped.

Now both of tablespaces have been droped after droping the schema NCL

This is quite confusing for me
Can anybody had encountered this problem?
Re: drop tablespace problem [message #227919 is a reply to message #227900] Fri, 30 March 2007 03:19 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This is not a problem and is a documented behaviour.
You need to take care of dependencies before dropping.
Seems you have dependencies across both tablespaces.
Since you dropped the user with cascade option, all dependencies are taken care of and it works.
Previous Topic: How to Find out,.what install i have in my system and how to clean the installation
Next Topic: changing one datatype to another
Goto Forum:
  


Current Time: Wed Dec 07 04:34:46 CST 2016

Total time taken to generate the page: 0.12836 seconds