It's a little bit urgent, I can't drop a user scheme, oracle gives me a set of errors such as:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
And I can't find the solution for this issue.
Step by step what I just did:
1) connected to a database with sysdba privs;
2) drop user eks cascade;
here I got errors;
3) and tried to execute thease two select statements:
select object_name,object_type from dba_objects where owner='eks';
select queue_table from dba_queue_tables where owner = 'eks';
and what oracle gave me back is - no rows sellected
And here I stucked, any chance to fix this problem. Solution maybe?
why are you using dba_queue_tables?
like you can read, it's normal that with user EKS doesn't appear anything.
Edited by: Fran on 16-nov-2012 1:44
Result is the same, there aren't any queue for EKS in all_queue_tables...
And I'm still wondering, why it gives me this kind of error if there aren't anything what could cause this issue.
Any solutions, tips?
I know this way to solve this problem, but there is a problem:
There isn't EKS which contains any queue tables.
Edited by: safazaurs on 2012.16.11 04:03
I just tried your select statement and it didn't give me anything (no rows selected).
But then I tried another one thing what I just wanted to try:
Like I don't have any needs to save that user I tried to drop all objects one by one using this statement:
"select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
And then accidently I found out that one of the errors showed me that I have to drop table using this procedure DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'QTABWSEORIREQUEST' , force => TRUE);
Then I found out the similarities and it was this QT, and then I used this select "select object_name from user_objects where object_name like '%QT%'; " to obtain list of thease **** tables. After that droped all thease tables using:
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'QTABWSEORIREQUEST' , force => TRUE);
So, thank you everyone for helping me.