Simple operations on Columns hanging Database:-10.2.0.3

[nandusb]

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.

[PAVB]

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.

[sunil_kandi]

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.

[PAVB]

...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?

[sunil_kandi]

... 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.

[Zenryou]

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