Tuesday 16 August 2016

Convert Partition table from RANGE to INTERVAL With Different Table spaces

Recently I Worked on the task which is  converting  RANGE Partitioning Table to into Interval partitions.  But  Those range partition Table are using different table spaces for each partition for every 6 months and round robin fashion. 


For Example: Converting KUMAR  table under Sunil Schema into Interval Monthly Partitioning which is currently partitioned by Range and using TEST1,TEST2,TEST3 Tablespaces  Every 3 Months.


Step 1) Convert Interval Partition 

alter table SUNIL.KUMAR set interval(numtoyminterval(1,'MONTH'));


Step 2)  Assign the table spaces which is used 


alter table SUNIL.KUMAR set 

         store in (TEST1,TEST2,TEST3);


Step 3) Insert the data into KUMAR and See what are the table spaces are using. 


Execute the below command and make sure results. if you insert the data which will fall under different months it will use above mentioned table spaces in round robin fashion. 

col HIGH_VALUE for a60
set lines 200
select partition_name,high_value,tablespace_name from user_tab_partitions where table_name='KUMAR';


PARTITION_NAME HIGH_VALUE                                                                       TABLESPACE_NAME
-------------  -------------------------------------------------------------------------------  --------------
NOV_2015       TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA BTSUSER_DATA   
SYS_P140       TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST3          
SYS_P141       TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST1          
SYS_P142       TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST2          
SYS_P144       TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST2 




Making sure there is No MAX Partition is defined if you defined max value partition you will get below error. 


alter table SUNIL.KUMAR set interval(numtoyminterval(1,'MONTH'));

Error seen:
ORA-14759: SET INTERVAL is not legal on this table.

 The first step is to ensure that we don’t have MAXVALUE partition to avoid:
ORA-14759: SET INTERVAL is not legal on this table.


if you find any Max Value make sure there is no records in that partition then drop that one and execute above steps to continue. 



No comments:

Post a Comment