How to move Advanced Queues
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to move Advanced Queues

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    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
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    What is the difficulty in moving the table to a different tablespace?

    (ALTER TABLE xxxx MOVE TABLESPACE yyyy).

    Tamil

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Quote 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
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Some session is still using the table.
    You have to wait untill all the transactions commit or rollback.

    Tamil

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Stop the queue, move the table and then restart the queue.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Quote 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.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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!


  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  10. #10
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
  •  



Click Here to Expand Forum to Full Width