Simple operations on Columns hanging Database:-10.2.0.3
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Simple operations on Columns hanging Database:-10.2.0.3

  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Exclamation Simple operations on Columns hanging Database:-10.2.0.3

    Hi All,



    While I am performing simple operations like ,


    ALTER TABLE table_name ADD column_name datatype;

    ALTER TABLE table_name DROP COLUMN column_name;

    ALTER TABLE tablename SET UNUSED COLUMN column_name;


    Such operations are hanging for hours......


    But if I bounce the database this operations are completing quite easily.

    But as it is a production I can not bounce it so frequently.

    I also observed that there are no locked objects.

    Please Help me......

    Thanks.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    An operation like alter table drop column cannot be completed until Oracle acquires an exclusive table level lock meaning nobody else would be able to touch the table while the operation is in progress.

    Oracle wouldn't be able to initiate the operation until acquiring such a lock.

    The operation itself is very time consuming so for a large table you can expect a large timeframe.

    You may want to schedule these kind of operations during scheduled maintenance window.

    By the way, alter table drop column is not a "simple operation" -take a look at Oracle documentation and find out all the work Oracle has to perform on the background to complete your command.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2005
    Location
    USA
    Posts
    32
    Dropping column is time consuming depending upon the table size but other two should finish fast and you might want to look what session is waiting on(event column in v$session) and also try tracing.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by sunil_kandi View Post
    ...but other two should finish fast and you might want to look what session is waiting on(event column in v$session) and also try tracing.
    ... providing Oracle can get an exclusive lock on the affected table, isn't it?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2005
    Location
    USA
    Posts
    32
    Quote Originally Posted by PAVB View Post
    ... providing Oracle can get an exclusive lock on the affected table, isn't it?
    Oracle will error out immediately(ORA-00054: resource busy and acquire with NOWAIT specified) if it could nt get exclusive lock while adding/logically dropping column using set unused so if the operation is taking time then it is nothing to do with locks.

  6. #6
    Join Date
    Feb 2009
    Posts
    17
    Check from v$lock and v$locked_object views whether those tables have locks. If you dont find it there, then it might be something else

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