Home » RDBMS Server » Server Administration » Help - Long running alter table (9.2.5,unix)
Help - Long running alter table [message #284363] Thu, 29 November 2007 08:02 Go to next message
nil123
Messages: 6
Registered: November 2007
Junior Member
Hi,
we have long running ( more than 24 hrs ) alter table modify col nvarchar...

This is a partitioned table with 30+ partitions and two local indexes. one of the index uses the same col that is being modified.

There are about 5 million records in each partition.

My question is, is it safe to kill alter table ? will it take long time to rollback what it has done so far ? Meaning another 24 hrs... will it corrupt the underlying table ?

Also, does anybody know how to find out how much work it did so far and how much is pending ? I used v$session_longops, but it shows one working record at a time, so no help.

I know I can use exchange partition to modify col, but right now question is whether to let this run or kill it ?

Thanks for your help...
Re: Help - Long running alter table [message #284367 is a reply to message #284363] Thu, 29 November 2007 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the Oracle version (4 decimals)?
What is the previous table definition?
What is the actual full statement?

Regards
Michel
Re: Help - Long running alter table [message #284412 is a reply to message #284367] Thu, 29 November 2007 10:31 Go to previous messageGo to next message
nil123
Messages: 6
Registered: November 2007
Junior Member
oracle version - 9.2.0.5.0
The exact sql is ...

ALTER TABLE table_name
MODIFY (
col1 NVARCHAR2 (2000));

This col was varchar2(2000), changing it to nvarchar2(2000).



Re: Help - Long running alter table [message #284416 is a reply to message #284412] Thu, 29 November 2007 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing strange that it takes very long time.
It'd take less time to create a new table as select from the old one above all if you have an index on the modified column.

Regards
Michel
Re: Help - Long running alter table [message #284419 is a reply to message #284416] Thu, 29 November 2007 10:53 Go to previous messageGo to next message
nil123
Messages: 6
Registered: November 2007
Junior Member
I understand that...
I know couple of ways to do this faster. But, for now, question is, killing this session would have any bad effect ? corruption or loss of table...anything of that sort.

Never killed long running DDLs. so not sure.
Thanks
Re: Help - Long running alter table [message #284428 is a reply to message #284419] Thu, 29 November 2007 11:42 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it may.
DDL is built with small transactions.
So killing it may lead to some "corruptions".

Regards
Michel
Previous Topic: How to change current working directory in Oracle
Next Topic: ORA-00060: Deadlock detected
Goto Forum:
  


Current Time: Sat Dec 10 07:14:54 CST 2016

Total time taken to generate the page: 0.09290 seconds