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