drop tablespace problem [message #227900] |
Fri, 30 March 2007 02:04 |
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 |
|
Mahesh Rajendran
Messages: 10707 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.
|
|
|