Home » RDBMS Server » Server Administration » need to split a partition into multiple partitions (Oracle 11.2.0.1.0 linux 2.6)
need to split a partition into multiple partitions [message #522044] Tue, 06 September 2011 01:40
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear Sir/Madam,
we have a table having 33 partitions. Initial default partition range is less than 2008-02-01 and last partiton rage is less than the period: 2012-10-01. ( i.e as below )

select table_owner,table_name,partition_name,HIGH_VALUE from dba_tab_partitions
where TABLE_OWNER='COX_MEDIA' and TABLE_NAME='CMI_EVENT_FACT';  2

TABLE_OWNER     TABLE_NAME      PARTITION_NAME       HIGH_VALUE
--------------- --------------- -------------------- --------------------------------------------------------------------------------
COX_MEDIA       CMI_EVENT_FACT  CEF_200801           TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P18553           TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P18561           TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P18571           TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23471           TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23485           TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23499           TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35113           TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35114           TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35115           TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35116           TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TABLE_OWNER     TABLE_NAME      PARTITION_NAME       HIGH_VALUE
--------------- --------------- -------------------- --------------------------------------------------------------------------------
COX_MEDIA       CMI_EVENT_FACT  SYS_P35117           TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35118           TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35119           TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35120           TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35121           TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35122           TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35123           TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35124           TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35125           TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35126           TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23548           TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TABLE_OWNER     TABLE_NAME      PARTITION_NAME       HIGH_VALUE
--------------- --------------- -------------------- --------------------------------------------------------------------------------
COX_MEDIA       CMI_EVENT_FACT  SYS_P23552           TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23553           TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23554           TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23555           TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23556           TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23557           TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P23560           TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P26103           TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P28501           TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P35350           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
COX_MEDIA       CMI_EVENT_FACT  SYS_P34995           TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TABLE_OWNER     TABLE_NAME      PARTITION_NAME       HIGH_VALUE
--------------- --------------- -------------------- --------------------------------------------------------------------------------
COX_MEDIA       CMI_EVENT_FACT  SYS_P34996           TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

34 rows selected.

now we have a requirement, we need to split this DEFAULT partition ( i.e. CEF_200801 ) into multiple partitions to load the data from 2006-01-01.

i have just splitted this DEFAULT partition into 2 partition as below:

ALTER TABLE CMI_EVENT_FACT
 SPLIT PARTITION CEF_200801 AT (TO_DATE('2006-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION CEF_200601,PARTITION CEF_200801);
i.e. 2006 JAN month data will be inserted into new partition - CEF_200601. and 2006 FEB onwards till 2008-02-01,will be under partition - CEF_200801.

I need to write a single syntax to split ( 1 partition will have 1 month data )..can you please guide me how to write multiple SPLIT PARTITION in a single statement...

thank you
kesavan

[Updated on: Tue, 06 September 2011 01:48]

Report message to a moderator

Previous Topic: sys user not login and giving error ORA-27101
Next Topic: table analyze issue
Goto Forum:
  


Current Time: Thu Mar 28 19:48:09 CDT 2024