Home » RDBMS Server » Backup & Recovery » Oracle Queue tables gets corrupted (standard edition,10.2.0.4 Windows 2003)
Oracle Queue tables gets corrupted [message #602240] Mon, 02 December 2013 06:08 Go to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Hi,

Please note in my database oracle queue table gets corrupted SYS"."SYS_IOT_TOP_8864 please advice can we drop this index if yes,Kindly advice how to drop.

Structure of index for reference:

CREATE UNIQUE INDEX "SYS"."SYS_IOT_TOP_8864" ON "SYS"."AQ$_ALERT_QT_T" ("NEXT_
DATE", "TXN_ID", "MSGID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSAUX"

Regards,
Anil
Re: Oracle Queue tables gets corrupted [message #602241 is a reply to message #602240] Mon, 02 December 2013 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is an IOT, so the index IS the table.

Re: Oracle Queue tables gets corrupted [message #602242 is a reply to message #602241] Mon, 02 December 2013 06:15 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Please advice can we drop this?if so how
Re: Oracle Queue tables gets corrupted [message #602243 is a reply to message #602242] Mon, 02 December 2013 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try using "drop table SYS.AQ$_ALERT_QT_T".

Re: Oracle Queue tables gets corrupted [message #602247 is a reply to message #602243] Mon, 02 December 2013 06:56 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Hi, Please find below

SQL> drop table AQ$_ALERT_QT_T;
drop table AQ$_ALERT_QT_T
*
ERROR at line 1:
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

SQL> EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'AQ$_ALERT_QT_T');
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'AQ$_ALERT_QT_T'); END;

*
ERROR at line 1:
ORA-24002: QUEUE_TABLE SYS.AQ$_ALERT_QT_T does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4103
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1

Please advce.

Regards,
Anil

Re: Oracle Queue tables gets corrupted [message #602256 is a reply to message #602247] Mon, 02 December 2013 07:24 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
That is the queue table used by the server alert system. If you have corrupted that, you may well have damaged other parts of your data dictionary too. If I were you, I would do a full export immediately, and import into a new database.
Re: Oracle Queue tables gets corrupted [message #602257 is a reply to message #602256] Mon, 02 December 2013 07:26 Go to previous message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Dear John,Michel.

Thanks for advice we are planning to recreate the database.

Regards,
Anil

Previous Topic: Hot dmp backup + recovery
Next Topic: Recovery of Incremental backup with archieve log files
Goto Forum:
  


Current Time: Thu Mar 28 03:18:57 CDT 2024