Home » SQL & PL/SQL » SQL & PL/SQL » Add partition and Move Data (oracle 9.2.0.5)
Add partition and Move Data [message #307989] Thu, 20 March 2008 12:57 Go to next message
azeem87
Messages: 95
Registered: September 2005
Location: dallas
Member
Hi,

This is the Create table Statement, It's a partitioned table,
We forgot to add 2008 monthly partitions, so for all these 3 months of 2008 data was going in THE_REST partition,
We need to add 2008 12 partitions and also move the data from THE_REST partition to respective partitions,
This table also has a local partitioned Index on (BAT_DT,LBX_ID,SITE_ID).

How can i move the data and add partitions.

CREATE TABLE BAT_HEAD
(
  BAT_DT              DATE                      NOT NULL,
  BAT_HEAD_ID         NUMBER(10)                NOT NULL,
  SITE_ID             NUMBER(10)                NOT NULL,
  LBX_ID              NUMBER(10)                NOT NULL,
  BAT_NB              NUMBER(10)                NOT NULL,
  CRE_DT              DATE                      NOT NULL,

)
TABLESPACE APP_DATA01

PARTITION BY RANGE (BAT_DT) 
(  
  PARTITION NOV_04 VALUES LESS THAN (TO_DATE(' 2004-12-01 00:00:00')
  ........
  PARTITION DEC_07 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00')
  PARTITION THE_REST
               )
)

[Updated on: Thu, 20 March 2008 13:03]

Report message to a moderator

Re: Add partition and Move Data [message #307991 is a reply to message #307989] Thu, 20 March 2008 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SPLIT PARTITION <partition_name> AT <split location> INTO ...
Re: Add partition and Move Data [message #308000 is a reply to message #307991] Thu, 20 March 2008 14:10 Go to previous messageGo to next message
azeem87
Messages: 95
Registered: September 2005
Location: dallas
Member
thanks for Quick Reposnse,
I am able to use SPLIT option and then remove the first 3 months data which was inserted in THE_REST partition and after that i am trying to add new remaining partitions for 2008 it gives me error
This is what i used
alter table bat_head split partition THE_REST at (TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
into (partition P_200803,partition THE_REST)

 > Splitted till March 2003 for coming months i wanted to add remaining partitions by alter Table add partition, it gives this error 

SQL> alter table bat_head add partition P_200804 values less than (TO_DATE(' 2008-05-01 00:00:00', '
SYYYY-MM-DD HH24:MI:SS')) 
  2  /
alter table bat_head add partition P_200804 values less than (TO_DATE(' 2008-05-01 00:00:00', 'SYYYY
                                   *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition


Thanks

[Updated on: Thu, 20 March 2008 14:12]

Report message to a moderator

Re: Add partition and Move Data [message #308001 is a reply to message #307989] Thu, 20 March 2008 14:14 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
At this point the REST partition should be empty.
So, drop it, create the new ones, & then create REST again.
icon14.gif  Re: Add partition and Move Data [message #308003 is a reply to message #308001] Thu, 20 March 2008 14:24 Go to previous message
azeem87
Messages: 95
Registered: September 2005
Location: dallas
Member
Thank You Very Much, It worked.


Thanks

[Updated on: Thu, 20 March 2008 14:32]

Report message to a moderator

Previous Topic: get_all records + error PLS-00306 (merged)
Next Topic: Sequential GUID's??
Goto Forum:
  


Current Time: Sun Dec 04 02:37:12 CST 2016

Total time taken to generate the page: 0.08263 seconds