Grokbase › Groups › Oracle › oracle-l › May 2006
FAQ
Hi All,


How to track the table reorg:


alter table xyz move;


A 2 GB table is taking long time to compelte the operation (almost we waited for 2 hrs, but no use).


Other tables more than 2 GB also taking little time ( ~ 15-20 mins).


can someone tell me how to track this and what could be the reason for this specific tbale taking long time for reorg ?



I checked for the wait events. No locks, no wait events.


is there a way to check the progress of this process ?


Thanks,
Raj



Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min with Yahoo! Messenger with Voice.

Search Discussions

  • Stefan Knecht at May 6, 2006 at 2:51 pm ⇧
    did you check wait events at instance level ? or using sql trace:

    alter session set events '10046 trace name context forever, level 12';

    alter table xyz move;

    alter session set events '10046 trace name context off';

    then check the resulting trace file, gives you the most detailed view of
    what's going on

    or, while the move is already running, query v$session to get the
    sid/serial#, then use
    dbms_support.start_trace_in_session(sid,serial#,true,true); to start trace
    from your current session (must be dba) - if dbms_support isn't available
    in your database, and you're in 9i or above, @?/dbms/admin/dbmssupp.sql will
    create it.

    Stefan
    On 5/6/06, raja rao wrote:

    Hi All,

    How to track the table reorg:

    alter table xyz move;

    A 2 GB table is taking long time to compelte the operation (almost we
    waited for 2 hrs, but no use).

    Other tables more than 2 GB also taking little time ( ~ 15-20 mins).

    can someone tell me how to track this and what could be the reason for
    this specific tbale taking long time for reorg ?


    I checked for the wait events. No locks, no wait events.

    is there a way to check the progress of this process ?

    Thanks,
    Raj

    ------------------------------
    Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries<http://us.rd.yahoo.com/mail_us/taglines/postman9/*http://us.rd.yahoo.com/evt=39666/*http://messenger.yahoo.com/>for just 2¢/min with Yahoo! Messenger with Voice.
    --
    http://www.freelists.org/webpage/oracle-l
  • Hemant K Chitale at May 6, 2006 at 3:05 pm ⇧
    You could query DBA_SEGMENTS to see if a new Segment is being built.
    With Index rebuilds, I have seen that the new Segment is a TEMPORARY segment
    till the rebuild is completed. A Table MOVE should also be creating a like
    segment.

    Hemant
    At 10:46 PM Saturday, you wrote:
    Hi All,

    How to track the table reorg:

    alter table xyz move;
    Hemant K Chitale
    http://web.singnet.com.sg/~hkchital
  • Hallas, John, Tech Dev at May 8, 2006 at 10:56 am ⇧
    Does v$session_longops not show anything?



    From: oracle-l-bounce_at_freelists.org On Behalf Of raja rao
    Sent: 06 May 2006 15:47
    To: oracle list
    Subject: table reorg



    Hi All,



    How to track the table reorg:



    alter table xyz move;



    A 2 GB table is taking long time to compelte the operation (almost we waited for 2 hrs, but no use).



    Other tables more than 2 GB also taking little time ( ~ 15-20 mins).



    can someone tell me how to track this and what could be the reason for this specific tbale taking long time for reorg ?





    I checked for the wait events. No locks, no wait events.



    is there a way to check the progress of this process ?



    Thanks,

    Raj

    Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries <http://us.rd.yahoo.com/mail_us/taglines/postman9/*http:/us.rd.yahoo.com/evt=39666/*http:/messenger.yahoo.com/> for just 2¢/min with Yahoo! Messenger with Voice.
  • JayMiller_at_TDWaterhouse.com at May 8, 2006 at 3:54 pm ⇧
    Also, are you doing an online move? In that case activity on the table could be an issue.

    Thanks,

    Jay Miller

    Sr. Oracle DBA

    x68355

    -----Original Message-----
    From: [email protected]
    Sent: Saturday, May 06, 2006 10:47 AM
    To: oracle list
    Subject: table reorg

    Hi All,

    How to track the table reorg:

    alter table xyz move;

    A 2 GB table is taking long time to compelte the operation (almost we waited for 2 hrs, but no use).

    Other tables more than 2 GB also taking little time ( ~ 15-20 mins).

    can someone tell me how to track this and what could be the reason for this specific tbale taking long time for reorg ?

    I checked for the wait events. No locks, no wait events.

    is there a way to check the progress of this process ?

    Thanks,

    Raj

    _____

    Love cheap thrills? Enjoy PC-to-Phone calls <http://us.rd.yahoo.com/mail_us/taglines/postman9/*http:/us.rd.yahoo.com/evt=39666/*http:/messenger.yahoo.com/> to 30+ countries for just 2¢/min with Yahoo! Messenger with Voice.

    -----------------------------------------
    This message is confidential and sent by TD Waterhouse solely for
    use by the intended recipient. If you are not the intended
    recipient, you are hereby notified that any use, distribution or
    copying of this communication is strictly prohibited. This should
    not be deemed as an offer or solicitation, to buy or sell any
    product. Any 3rd party information contained herein was prepared by
    sources deemed reliable, but is not guaranteed. TD Waterhouse does
    not accept electronic instructions that would require an original
    signature. Information received by or sent from TD Waterhouse is
    stored, subject to review, and may be produced to regulatory
    authorities or others with a legal right to such.

    --
    http://www.freelists.org/webpage/oracle-l
  • Juan Carlos Reyes Pacheco at May 8, 2006 at 4:35 pm ⇧
    Hi raja,
    Did you tried v$session_wait?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 6, '06 at 2:46p
activeMay 8, '06 at 4:35p
posts6
users6
websiteoracle.com

6 users in discussion

Hallas, John, Tech Dev: 1 post Juan Carlos Reyes Pacheco: 1 post Stefan Knecht: 1 post Raja rao: 1 post JayMiller_at_TDWaterhouse.com: 1 post Hemant K Chitale: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase