Printable View
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
That would be perfect! I'm getting this error when I do that:Quote:
Originally Posted by tamilselvan
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
Yea, got that part. If my memory serves me right, I think the MOVE errors on the column USER_DATA's datatype.Quote:
Originally Posted by sambavan
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.
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 :D Good luck with ur downtime :)
Sam