OraFAQ Forum: Server Administration » how to drop a column in a huge table

Home » RDBMS Server » Server Administration » how to drop a column in a huge table (11.2.0.1.0 Windos XP)

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

how to drop a column in a huge table [message #519007]

Mon, 08 August 2011 20:34

andy huang
Messages: 498
Registered: July 2011

Senior Member

Dear all,
I want to drop a column in a huge table which contain about 420,000,000 rows,i use the alter table drop coumn command to execute,and found it takes a long time and generate huge redo.
Is there any quickly way to drop a column in a huge table?

Report message to a moderator

Re: how to drop a column in a huge table [message #519008 is a reply to message #519007]

Mon, 08 August 2011 20:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there any quickly way to drop a column in a huge table?
DML takes time.
1) CREATE VIEW without the column.
2) alter column UNUSED
3) DBMS_REDEFINITION

why is it deemed necessary to DROP COLUMN?

Report message to a moderator

Re: how to drop a column in a huge table [message #519009 is a reply to message #519008]

Mon, 08 August 2011 20:49

andy huang
Messages: 498
Registered: July 2011

Senior Member

Hi,
Because the column is clob type and not to use. i want to drop and save space.

Report message to a moderator

Re: how to drop a column in a huge table [message #519010 is a reply to message #519009]

Mon, 08 August 2011 20:53

andy huang
Messages: 498
Registered: July 2011

Senior Member

Hi,
1) CREATE VIEW without the column. The method is not drop the column really.
2) alter column UNUSED The method is not drop the column really also.
3) DBMS_REDEFINITION The method can drop the column,but i think it will take a long time to create mv for base table .

Report message to a moderator

Re: how to drop a column in a huge table [message #519011 is a reply to message #519009]

Mon, 08 August 2011 20:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Because the column is clob type and not to use. i want to drop and save space.
Amount of disk space will be the same before the DROP & afterwards.
I can buy 1TB disk for about $50 US Dollar or $0.05 per GB.
What is your time costing to complete this task?

Report message to a moderator

Re: how to drop a column in a huge table [message #519030 is a reply to message #519010]

Tue, 09 August 2011 01:09

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I'd set the column as UNUSED and then really drop it during a maintenance window.

If you have none, then use there is no other way then to use dbms_redifinition.

Regards
Michel

Report message to a moderator

Previous Topic:	reduce the system tablespace size
Next Topic:	change tablespace of a table using dbms_redefinition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 27 02:54:26 CST 2025