Home » SQL & PL/SQL » SQL & PL/SQL » Simple operations on Columns hanging (Database 10.2.0.3)
icon4.gif  Simple operations on Columns hanging [message #396124] Sat, 04 April 2009 01:39 Go to next message
nandusb
Messages: 4
Registered: March 2009
Location: INDIA
Junior Member
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.
Re: Simple operations on Columns hanging [message #396125 is a reply to message #396124] Sat, 04 April 2009 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But as it is a production I can not bounce it so frequently.

And do you use these statements frequently in production?

Query v$session_wait and/or activate a trace on the session to know on what they are waiting or what they are doing.

ADD COLUMN could be very long if there is a default value as each and every block has to be updated. Same thing for DROP COLUMN.

Regards
Michel
Re: Simple operations on Columns hanging [message #396168 is a reply to message #396124] Sat, 04 April 2009 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I also observed that there are no locked objects.
>Please Help me......

I suspect that DML against the table exists without any COMMIT or ROLLBACK.

In other words, there is a pending transaction against table & to ensure data integrity, Oracle prevent DDL from completing.
Re: Simple operations on Columns hanging [message #396169 is a reply to message #396124] Sat, 04 April 2009 09:39 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I also observed that there are no locked objects.

Show us what you did to observe this.

Regards
Michel
Previous Topic: want output in excel automatically
Next Topic: Need Help Posting date
Goto Forum:
  


Current Time: Sat Dec 10 22:40:54 CST 2016

Total time taken to generate the page: 0.04120 seconds