-
How to move Advanced Queues
I want to move my 9.2.0.4 AQ tables to a different tablespace. Actually I want to shrink the datafiles
I'm guessing I'll need a script that'll reverse engineer them. I wasn't able to move them because they have a datatype it's not happy with.
I've had troubles re-creating them after exp/imp.
Any ideas or suggestions would be greatly appreciated.
Ken
-
What is the difficulty in moving the table to a different tablespace?
(ALTER TABLE xxxx MOVE TABLESPACE yyyy).
Tamil
-
Originally Posted by tamilselvan
(ALTER TABLE xxxx MOVE TABLESPACE yyyy).
That would be perfect! I'm getting this error when I do that:
SQL> alter table el_scanqueue move tablespace aq_2;
alter table el_scanqueue move tablespace aq_2
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
-
Some session is still using the table.
You have to wait untill all the transactions commit or rollback.
Tamil
-
I believe I did this before and got past the locking problem but it had an issue with the USER_DATA column (see below)
SQL> desc el_scanqueue
Name Null? Type
----------------------------------------- -------- ----------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
CORRID VARCHAR2(128)
PRIORITY NUMBER
.
.
DEQUEUE_MSGID RAW(16)
SENDER_NAME VARCHAR2(30)
SENDER_ADDRESS VARCHAR2(1024)
SENDER_PROTOCOL NUMBER
USER_DATA SYS.AQ$_JMS_TEXT_MESSAGE
-
Stop the queue, move the table and then restart the queue.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Originally Posted by sambavan
Stop the queue, move the table and then restart the queue.
Yea, got that part. If my memory serves me right, I think the MOVE errors on the column USER_DATA's datatype.
-
Check the setup document Note:232301.1. This points me to believe that some JDBC connectivity is locking it. My first thought on glancing the note was to shutdown the any iAS that connects to the database then try to clear all the jdbc connectivities and then try the move.
Hope this would help you.
Thanx
Sam
Life is a journey, not a destination!
-
Thanks Sam, I'll give it a go.
I only get maintenance downtime once per month and tonight is my night.
Thanks again for your posts.
-Ken
-
That does explaing why you were scrabling out Good luck with ur downtime
Sam
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|