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 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 |
|
|
|
Goto Forum:
Current Time: Fri Nov 29 04:22:43 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
