ora-604 and ora-02429 errors

[mkey]

Hi all,
I'm dropped a user in oracle 8i and tried to drop the corresponding tablespace that I created for that user. Now when I dropped the user but I'm not able to drop the tablespace for that user. Can someone tell me why I'm getting these errors?
The reason I need to do this because I need to export some objects into this user.

Thanks.

 

[karluk]

Your errors indicate that you have foreign key violations being caused by dropping the tablespace. That means that the tablespace must still contain tables (or maybe primary or foreign key indexes), even though you've dropped the user the tablespace was designed for. To find out what tables still exist in the tablespace, run the query

select owner, table_name from dba_tables where tablespace_name = 'TABLE_SPACE_BEING_DROPPED';

To tell the truth, I don't see why you are dropping the tablespace. It doesn't seem to have much to do with the export you say you are trying to do.

 

[hariharanmurugan]

Hi,
Check if there are any integrity constraints present. If there are any, try to drop them instead of the indexes. This would get rid of that ora-02429 error. As for the ora-00604 error, this is what the documentation has to say about it:

ORA-00604 error occurred at recursive SQL level string

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Customer Support.

And for the ora-02429 error, the documentation says the following:

ORA-02429 cannot drop index used for enforcement of unique/primary key

Cause: An attempt was made to drop an index that is being used as the enforcement mechanism for a unique or primary key.

Action: Drop the integrity constraint instead of dropping the index.

Hope that helps,
Hariharan

 

[mkey]

hi guys,
What I'm trying to do is export from user1 and import that file to user2. Now if I don't drop the existing user2's tablespace I find some objects for example triggers use the user1 tablespace. Its weird. So that is the reason I wanted to deleted the user2's tablespace.

select owner, table_name from dba_tables where
tablespace_name = 'PODSCT_QUAL';

The above select statement returned no records. So I'm still not sure what's going on here.

Thank you for all your thoughts.

 

[karluk]

Execute the same type of query on dba_indexes:

select owner, index_name from dba_indexes where
tablespace_name = 'PODSCT_QUAL';

You need to track down what object is in the tablespace being dropped.

 

[mkey]

Thank you for all the help! I think I know what the problem is now.

Another question though. I used the export utility like below to create a dump file.

exp user1/user1@host file = user1.dmp

Then,

imp user2/user2@host file = user1.dmp full = y

I don't see any constraints on user2. All the tables and data is their except the constraints. I notised in the dos prompt that the dmp file trying to enable the constraints and I got bunch of errors. How can I create the export file with the create constraints?

Thanks!

 

[karluk]

Please add a log={file name} parameter to you import and rerun. That will write screen output to the log file and enable you to look at the errors being generated by the import, and in particular why the constraint creations are failing.