OraFAQ Forum: Server Administration » Help

Home » RDBMS Server » Server Administration » Help - Long running alter table (9.2.5,unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help - Long running alter table [message #284363]

Thu, 29 November 2007 08:02

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

Report message to a moderator

Re: Help - Long running alter table [message #284367 is a reply to message #284363]

Thu, 29 November 2007 08:08

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Help - Long running alter table [message #284412 is a reply to message #284367]

Thu, 29 November 2007 10:31

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

Report message to a moderator

Re: Help - Long running alter table [message #284416 is a reply to message #284412]

Thu, 29 November 2007 10:50

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Help - Long running alter table [message #284419 is a reply to message #284416]

Thu, 29 November 2007 10:53

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

Report message to a moderator

Re: Help - Long running alter table [message #284428 is a reply to message #284419]

Thu, 29 November 2007 11:42

Michel Cadot
Messages: 68686
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 18:16:52 CDT 2024