OraFAQ Forum: SQL & PL/SQL » Queue table - error

Home » SQL & PL/SQL » SQL & PL/SQL » Queue table - error (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Queue table - error [message #597588]

Mon, 07 October 2013 01:24

SKSG
Messages: 7
Registered: October 2013
Location: Sg

Junior Member

Hi,
I am had taken over this server as new DBA.

Issue here is DEV server having this Queue table setup which is kind of missing defination or wrong.
In UAT server setup of this Queue table and realted index etc all working fine, in other words it is setup correctly.

By right under schema this queue table should be under "Queue tables".
But in DEV this table is under "Tables". When I try to drop this table could not do that.
Always "Error in <<Table name>>" is the error thrown.

Could not change this in anyway.

Had enough search in fourms, but no clue yet.

Hope to find a solution out here.

[Updated on: Mon, 07 October 2013 01:25]

Report message to a moderator

Re: Queue table - error [message #597592 is a reply to message #597588]

Mon, 07 October 2013 01:40

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

By right under schema this queue table should be under "Queue tables".
But in DEV this table is under "Tables".

What does this mean? "Under"? How "under"?

Quote:

Always "Error in <<Table name>>" is the error thrown.

This is not an Oracle error message.

Report message to a moderator

Re: Queue table - error [message #597593 is a reply to message #597588]

Mon, 07 October 2013 01:42

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Do you have the required privileges granted?

Report message to a moderator

Re: Queue table - error [message #597599 is a reply to message #597588]

Mon, 07 October 2013 02:07

tigsav
Messages: 49
Registered: April 2012

Member

Please Carry out the entire exercise in an sqlplus session and post the output here for us to help you.

Report message to a moderator

Re: Queue table - error [message #597602 is a reply to message #597599]

Mon, 07 October 2013 02:30

SKSG
Messages: 7
Registered: October 2013
Location: Sg

Junior Member

Here is the exact error which I get.
For any drop or alter statments which I tried this is the error spit out.. Smile

Smile

ORA-04063: <schema>.<Table> has errors.
ORA-06512: at "SYS.DBMS_AQADM",line xxx
ORA-06512: at line 1

Report message to a moderator

Re: Queue table - error [message #597603 is a reply to message #597602]

Mon, 07 October 2013 02:50

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First search for all invalid SYS objects (query DBA_OBJECTS).
Then reinstall all invalid objects.

Report message to a moderator

Re: Queue table - error [message #597604 is a reply to message #597602]

Mon, 07 October 2013 02:51

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

See if this helps https://forums.oracle.com/thread/635531

A good demonstartion by D.Morgan http://psoug.org/reference/aq_demo1.html

Regards,
Lalit

Report message to a moderator

Re: Queue table - error [message #597606 is a reply to message #597604]

Mon, 07 October 2013 03:17

SKSG
Messages: 7
Registered: October 2013
Location: Sg

Junior Member

I did tried that linkin oracle forums "635531". It was running for more than 4hours, so I stopped that.

Now again tried running that script with "force" it is still running for more than 30mins.

Report message to a moderator

Re: Queue table - error [message #597608 is a reply to message #597606]

Mon, 07 October 2013 03:33

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste your session.

Report message to a moderator

Re: Queue table - error [message #597628 is a reply to message #597602]

Mon, 07 October 2013 04:20

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

First, the moderator bit:

Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

SKSG wrote on Mon, 07 October 2013 08:30

Here is the exact error which I get.
For any drop or alter statments which I tried this is the error spit out..

ORA-04063: <schema>.<Table> has errors.
ORA-06512: at "SYS.DBMS_AQADM",line xxx
ORA-06512: at line 1

Before any one can assist, you need to show what you are doing. Are you trying to manage queue tables with SQL? You can't do that. For example:

orclz> exec dbms_aqadm.create_queue_table('myqt','sys.anydata')

PL/SQL procedure successfully completed.

orclz> drop table myqt;
drop table myqt
           *
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table SCOTT.MYQT


orclz>  exec dbms_aqadm.drop_queue_table('myqt')

PL/SQL procedure successfully completed.

orclz>

Sp use copy/paste, enclosing the result in [code] tags, to show what you are actually doing.

Report message to a moderator

Re: Queue table - error [message #597719 is a reply to message #597606]

Mon, 07 October 2013 21:03

SKSG
Messages: 7
Registered: October 2013
Location: Sg

Junior Member

Hi, I checked this too. There is no invalid object.

Report message to a moderator

Re: Queue table - error [message #597720 is a reply to message #597628]

Mon, 07 October 2013 21:07

SKSG
Messages: 7
Registered: October 2013
Location: Sg

Junior Member

I had tried like this and also with "force".

exec dbms_aqadm.drop_queue_table('myqt')

without force does return error. But with "force" command is running for ever, in the end I need to kill that process.

Report message to a moderator

Re: Queue table - error [message #597722 is a reply to message #597720]

Mon, 07 October 2013 21:47

SKSG
Messages: 7
Registered: October 2013
Location: Sg

Junior Member

Yes under invalid object this table is listed.

Ofcourse - this should be a queue table. But when compare the table with UAT version, it is not correct.
I am not able to change anything in this table or drop either.

[Updated on: Mon, 07 October 2013 21:58]

Report message to a moderator

Re: Queue table - error [message #597723 is a reply to message #597722]

Mon, 07 October 2013 22:08

SKSG
Messages: 7
Registered: October 2013
Location: Sg

Junior Member

But when I try to create this queue table again , it does throw me error

Error at line 1
ORA-24001: cannot create QUEUE_TABLE, <SCHEMA>.<Queue Tale name> already exists
ORA-06512: at "SYS.DBMS_AQADM", line 81
ORA-06512: at line 2

Report message to a moderator

Re: Queue table - error [message #597725 is a reply to message #597723]

Mon, 07 October 2013 22:33

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

tigsav wrote on Mon, 07 October 2013 12:37

Please Carry out the entire exercise in an sqlplus session and post the output here for us to help you.

Michel Cadot wrote on Mon, 07 October 2013 14:03

Use SQL*Plus and copy and paste your session.

John Watson wrote on Mon, 07 October 2013 14:50

use copy/paste, enclosing the result in [code] tags, to show what you are actually doing.

Please copy paste the SQL*Plus session to show what you are actually doing.

Report message to a moderator

Re: Queue table - error [message #597736 is a reply to message #597723]

Tue, 08 October 2013 00:02

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste all what you do and get as John did it above.

Report message to a moderator

Previous Topic:	duplicate row elimination
Next Topic:	FUNCTION COMPLIATION ERROR

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 23:23:53 CDT 2024