Error while importing the schema 'SOAINFRA'

YBSolutions
YBSolutions used Ask the Experts™
on
Situation:

I need to drop the existing users created by RCU for OSB( oracle service bus) and Weblogic.
Therefore, need to take the backup and drop them.

Reason to drop is: We want to create another set of users with same name and details.

After taking the backup I tried to test it in my laptop but
I am getting error while importing the schema SOAINFRA.
Even I tried to restore in the original database itself with another username.

==========
I am sending the logs of exp and imp both.
----------------
Dev original objects count:
------
 select count(object_name), object_type from dba_objects where owner='I3OV_SOAINFRA' group by object_type ;

COUNT(OBJECT_NAME) OBJECT_TYPE
------------------ -------------------
                 7 QUEUE
                10 SEQUENCE
                45 PROCEDURE
                 9 PACKAGE
                 8 PACKAGE BODY
                 8 RULE SET
                75 LOB
                 1 TYPE BODY
                37 VIEW
               505 INDEX
               240 TABLE

COUNT(OBJECT_NAME) OBJECT_TYPE
------------------ -------------------
                 9 FUNCTION
                 3 EVALUATION CONTEXT
                 5 TYPE

Command used to export
exp file=I3OV_SOAINFRA.dmp log=I3OV_SOAINFRA.log owner=I3OV_SOAINFRA


==========
After import count:
--------
SQL>   select count(object_name), object_type from dba_objects where owner='TEST_I3OV_SOAINFRA' group by object_type ;

COUNT(OBJECT_NAME) OBJECT_TYPE
------------------ -------------------
                10 SEQUENCE
                45 PROCEDURE
                 9 PACKAGE
                 8 PACKAGE BODY
                68 LOB
                37 VIEW
               500 INDEX
               237 TABLE
                 9 FUNCTION

9 rows selected.

Command used to import:
---------
imp  file=I3OV_SOAINFRA.dmp log=I3OV_SOAINFRA_imp.log fromuser=I3OV_SOAINFRA touser=TEST_I3OV_SOAINFRA


More over I am getting below error while dropping the user.
========


SQL> drop user TEST_I3OV_SOAINFRA cascade ;
drop user TEST_I3OV_SOAINFRA cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
4users-4-OSB-n-WL.log
4users-4-OSB-n-WL-imp.log
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Hi,

Can someone look into it, please?

Author

Commented:
Hi,

What if I do tablespace export. Reason this user's object should be in tablespace 'I3OV_SOAINFRA'.

----------
SQL> select username, default_tablespace, temporary_tablespace from dba_users where username ='I3OV_SOAINFRA' ;

I3OV_SOAINFRA                  I3OV_SOAINFRA                  I3OV_IAS_TEMP

Author

Commented:
To drop the user, I checked there are no queue tables.

==========
SQL>select queue_table from dba_queue_tables where owner in ('TEST2_I3OV_SOAINFRA','I3OV_SOAINFRA','TEST3_I3OV_SOAINFRA') ;

no rows selected



 SQL>drop user TEST2_I3OV_SOAINFRA cascade ;
drop user TEST2_I3OV_SOAINFRA cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables


 SQL>drop user I3OV_SOAINFRA cascade ;
drop user I3OV_SOAINFRA cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables


 SQL>drop user  TEST3_I3OV_SOAINFRA cascade ;
drop user  TEST3_I3OV_SOAINFRA cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables


 SQL>

Author

Commented:
I saw metalink note:

Implications Of Export/Import On Advanced Queuing [ID 233105.1]

Do you think it might have hit the bug as mentioned in this doc?

Author

Commented:
May I know if someone is working on this issue or not?
Mark GeerlingsDatabase Administrator

Commented:
For "normal" schemas, the approach you tried of doing a schema-level export, then a "drop user [username] cascade" should.  I've never tried that though with this Oracle-created schema.

Which version of Oracle do you have?  If it is Oracle10.2 or higher, you should try expdp instead of exp.

If your biggest problem now is the error with the "drop user ...cascade" command, you could manually drop all of the user's objects first, then a simple "drop user [username]" command should work.  You can use SQL commands to create the "drop object..." commands for you.  First run this, and save the results into a *.sql file:
select 'drop '||object_type||' '||object_name||';' "Command"
from user_objects where object_type not in ('PACKAGE BODY', 'TABLE')
order by object_type, object_name;

Then run that *.SQL file.  Next run this, and save those results to another *.sql file
select 'drop table '||table_name||';' "Command"
from user_tables
order by table_name;

Then run that file.  Now, use this query to see if there are any user objects remaining (there shouldn't be):

select object_type, object_name from user_objects
order by object_type, object_name;

If any remain, you will need to remove them.  If that query returns no rows, you should be able to drop the user without an error.
Perhaps you should check the fine Oracle® Streams Advanced Queuing User's Guide and Reference, specially the section "Queue Table Export-Import"
:p
After waiting over 12 hours. I thought it is not worth to wait more.
I found the solution in metalink note id 1359656.1

OIM 11g Schema Backup and Restoration using Data Pump Client Utility [ID 1359656.1]

Author

Commented:
I found the solution before provided by expert exchange.<br />Therefore, I didn't even check what they have said.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial