This content has been marked as final. Show 8 replies
Is this a dev/test environment or production with important data?
If the later close this forum thread and open an SR with Oracle Support immediately.
You can potentially do a lot more damage ... and we don't even know your version number ... not to mention you should never have executed that ALTER SESSION with being instructed to do so by Oracle.
Firstly - as someone has already mentioned, never use events that haven't been recommended by support or are well documented for use (e.g. 10046 for sql trace), for AQ always use the given API's (DBMS_AQ, DBMS_AQADM), you should not interact directly with Queue objects unless it is documented in either the standard Oracle documentation or a support note or you have been instructed to do so by a support engineer from Oracle.
To resolve your current issue I suspect you should be able to work through (some bits may already be complete due to your manual table drops) the process in the following MOS note:
How to Manually Cleanup Advanced Queuing Tables (Doc ID 203225.1)
Yeah, I know now that I couldn't do that... I learned my lesson :(
I did the query you asked me to do and it returned no rows ... So it's not in the recycle bin [when you said about that, I really tought that it should be there but its not :( damn]
I couldn't find these notes ... Where can I find it?
I was looking at the Oracle Database Documentation Library, but nothing ...
Thanks for all the help!!
It doesnt exists anymore at metalink.oracle.com
but its ok ... I fixed the problem!
looking the query that DBA_QUEUES and DBA_QUEUE_TABLES does, I found the following tables:
the DBA_QUEUES does a join between those tables, and the problem was that the PK was different in both. So, it couldnt show the queues in DBA_QUEUES because of that ...
but, as I said, the queue didn't exists, and I just wanted to clean it from the system ... to be able to drop the schema!
so, I doing this query:
SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE UPPER(OBJECT_NAME) LIKE 'AQ%' AND OBJECT_TYPE='TABLE' AND OWNER IN ('SYS','SYSTEM','OWNER_OF_QUEUE');
I found the candidates tables that could have registers about this queue ...
then I did a search in every table looking for a table_name [or some column like that] that could have a register about that queue ...
So I deleted those registers from there, and from obj$
Finally, all the register about that queue was gone, and I was able to drop the schema
I know that what I did was risk (TOO TOO risk), but was the only way to be able to drop the schema ...
but dropping the schema, my problem was over and I was able to recreate it without problems...
Not the best solution, but ... what can I do?
but thanks for everyone that helped me !! I really appreciated that!