| drop tablespace before import [message #70166] |
Tue, 23 April 2002 18:46  |
Owen
Messages: 18
Registered: September 1999
|
Junior Member |
|
|
Dear all,
I am going to drop all the related tablespace by one owner first and then import a .dmp file from another database. 5 tablespaces I would like to drop. But one of them got the following error message:
drop tablespace ts_A including contents cascade constraints
*
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
Could anyone have comments? thanks.
Regards,
Owen
|
|
|
|
| Re: drop tablespace before import [message #70171 is a reply to message #70166] |
Wed, 24 April 2002 07:57  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
You have a primary key constraint in that tablespace which creates an index by default. The table or index appears to be in another tablespace. Run this query to see what objects exist and in which tablespace they reside in.
select owner,segment_name,segment_type,tablespace_name from dba_segments where owner not in ('SYS','
SYSTEM') order by owner,segment_type;
Then do:
select owner,constraint_name,constraint_type,table_name from dba_constraints where owner='SCHEMA_NAME';
Then disable the constraint to drop the ts. If you need it or it belongs to a schema you do not want to drop then try 'alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME to move it. Or if a table alter table TABLE_NAME move tablespace TABLESPACE_NAME to move the table. You should be able to drop the ts after doing one or more of the above.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
