-
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.
-
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.
-
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.
-
Originally Posted by sunil_kandi
...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.
-
Originally Posted by PAVB
... 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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|