OraFAQ Forum: SQL & PL/SQL » How to add new column in partition table

Home » SQL & PL/SQL » SQL & PL/SQL » How to add new column in partition table (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 OS Solaris 5.10)

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

How to add new column in partition table [message #431918]

Fri, 20 November 2009 01:23

sourajit_seth
Messages: 38
Registered: November 2005
Location: India

Member

Hi,
In Oracle 10g Database, I have one table (X) with list partition . I have added one new column to "X" by "Alter Table" command. Please advise whether any other command needs to be executed since it is a partition table .

The "X" table is used for partition swapping with another table (Y). I have added the same column also in table "Y". Will there be any issue while swapping the partion with the following command

alter table X exchange partition partition_name with table Y

Thanks in advance

Report message to a moderator

Re: How to add new column in partition table [message #431924 is a reply to message #431918]

Fri, 20 November 2009 02:04

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

Senior Member
Account Moderator

Use ALTER TABLE to add a column.
Do the same for the table you want to exchange.

Regards
Michel

Report message to a moderator

Re: How to add new column in partition table [message #431942 is a reply to message #431924]

Fri, 20 November 2009 02:59

sourajit_seth
Messages: 38
Registered: November 2005
Location: India

Member

Thanks for your response.
But my question is, is it required to execute any other command
( like "Re create Partition" etc.) after adding the column ?
Is there any impact on the partition after adding the column ?
Will we face any issue while performing any DML/DDL operations on the table ?

Report message to a moderator

Re: How to add new column in partition table [message #431949 is a reply to message #431942]

Fri, 20 November 2009 03:34

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

Senior Member
Account Moderator

There is no difference with or without partition.

Regards
Michel

Report message to a moderator

Re: How to add new column in partition table [message #432104 is a reply to message #431918]

Sat, 21 November 2009 04:26

wakula
Messages: 150
Registered: February 2008
Location: Poland

Senior Member

You use ALTER TABLE to add a new column.
You use ALTER TABLE to add a new partition.
You use ALTER (...) COMPILE (...) to compile objects that depend on what you just altered.
You use ALTER INDEX (...) REBUILD (...) to rebuild an index if it got invalidated by your DDL operations.

Example of situations when index might be invalidated: you have updated a tablespace where your partitioned table is using a unpartitioned (global) index.
Read about ONLINE clause from the Oracle documentation.
Always check if the objects (view USER_OBJECTS), partitions (USER_TAB_PARTITIONS) and indexes (USER_INDEXES, USER_IND_PARTITIONS) are valid/usable.
When adding a new column to a table which is huge (ex. >100GiB) you should eat something while waiting - because it might take long time.

Report message to a moderator

Previous Topic:	Why order by clause is not working in subquery
Next Topic:	Parallel Degree on a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 24 03:47:37 CDT 2025