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)
icon9.gif  How to add new column in partition table [message #431918] Fri, 20 November 2009 01:23 Go to next message
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


Re: How to add new column in partition table [message #431924 is a reply to message #431918] Fri, 20 November 2009 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use ALTER TABLE to add a column.
Do the same for the table you want to exchange.

Regards
Michel
Re: How to add new column in partition table [message #431942 is a reply to message #431924] Fri, 20 November 2009 02:59 Go to previous messageGo to next message
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 ?
Re: How to add new column in partition table [message #431949 is a reply to message #431942] Fri, 20 November 2009 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference with or without partition.

Regards
Michel
Re: How to add new column in partition table [message #432104 is a reply to message #431918] Sat, 21 November 2009 04:26 Go to previous message
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.
Previous Topic: Why order by clause is not working in subquery
Next Topic: Parallel Degree on a table
Goto Forum:
  


Current Time: Sat Oct 01 11:03:29 CDT 2016

Total time taken to generate the page: 0.09283 seconds