When you want to / need to move a Partition to a different Tablespace (e.g. as part of a LifeCycle Management Policy), you may need downtime to relocate the Partition. However, version 12cRelease1 allows Online Relocation of a Partition.
Let's say I have a SALES_DATA table and I need to move the Year 2016 data to a tablespace with datafiles on "cheaper" (lesss-performant) storage :
I then want to move the Year 2016 data to the Tablespace ARCHIVE_SALES_DATA :
When I relocated the P_2016 Partition (to the ARCHIVE_SALES_DATA Tablespace), the ROWIDs for rows in that Partition changed. So the Non-Partitioned Index SYS_C0017514 and the corresponding Local Partitions of the two Partitioned Indexes became "UNUSABLE". These had to be rebuilt. Alternatively, I could have added the UPDATE INDEXES clause to to the ALTER TABLE ... MOVE PARTITION .. statement to reset the Indexes to Usable but this would not have relocated the Local Partitions for those two Indexes to the new Tablespace.
Note that for Table Partitions, the MOVE clause relocates the Partition but for Index Partition the REBUILD clause is used to relocate (as well as make Usable) the Partition.
I would encourage you to view documentation and examples of the MOVE ONLINE facility in 12c to relocate a Table Partition without downtime.
Let's say I have a SALES_DATA table and I need to move the Year 2016 data to a tablespace with datafiles on "cheaper" (lesss-performant) storage :
SQL> select partition_name, tablespace_name 2 from user_tab_partitions 3 where table_name = 'SALES_DATA' 4 order by partition_position 5 / PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P_2016 TBS_YEAR_2016 P_2017 TBS_YEAR_2017 P_2018 TBS_YEAR_2018 P_2019 TBS_YEAR_2019 P_2020 TBS_YEAR_2020 P_MAXVALUE USERS 6 rows selected. SQL> SQL> select index_name, tablespace_name, status 2 from user_indexes 3 where table_name = 'SALES_DATA' 4 / INDEX_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ -------- SYS_C0017514 HEMANT VALID SALES_DATA_LCL_NDX_1 N/A SALES_DATA_LCL_NDX_2 N/A SQL> SQL> l 1 select index_name, partition_name, tablespace_name, status 2 from user_ind_partitions 3 where index_name in 4 (select index_name 5 from user_indexes 6 where table_name = 'SALES_DATA') 7* order by index_name, partition_position SQL> / INDEX_NAME PARTITION_NA TABLESPACE_NAM STATUS ------------------------------ ------------ -------------- -------- SALES_DATA_LCL_NDX_1 P_2016 TBS_YEAR_2016 USABLE SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE SALES_DATA_LCL_NDX_2 P_2016 TBS_YEAR_2016 USABLE SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE 12 rows selected. SQL>
I then want to move the Year 2016 data to the Tablespace ARCHIVE_SALES_DATA :
SQL> alter table SALES_DATA 2 move partition P_2016 3 tablespace ARCHIVE_SALES_DATA 4 / Table altered. SQL> SQL> select partition_name, tablespace_name 2 from user_tab_partitions 3 where table_name = 'SALES_DATA' 4 order by partition_position 5 / PARTITION_NA TABLESPACE_NAME ------------ ------------------ P_2016 ARCHIVE_SALES_DATA P_2017 TBS_YEAR_2017 P_2018 TBS_YEAR_2018 P_2019 TBS_YEAR_2019 P_2020 TBS_YEAR_2020 P_MAXVALUE USERS 6 rows selected. SQL> SQL> select index_name, tablespace_name, partitioned, status 2 from user_indexes 3 where table_name = 'SALES_DATA' 4 order by index_name 5 / INDEX_NAME TABLESPACE_NAME PAR STATUS ------------------------------ ------------------ --- -------- SALES_DATA_LCL_NDX_1 YES N/A SALES_DATA_LCL_NDX_2 YES N/A SYS_C0017514 HEMANT NO UNUSABLE SQL> alter index SYS_C0017514 rebuild ; Index altered. SQL> select index_name, tablespace_name, partitioned, status 2 from user_indexes 3 where table_name = 'SALES_DATA' 4 order by index_name 5 / INDEX_NAME TABLESPACE_NAME PAR STATUS ------------------------------ ------------------ --- -------- SALES_DATA_LCL_NDX_1 YES N/A SALES_DATA_LCL_NDX_2 YES N/A SYS_C0017514 HEMANT NO VALID SQL> SQL> l 1 select index_name, partition_name, tablespace_name, status 2 from user_ind_partitions 3 where index_name in 4 (select index_name 5 from user_indexes 6 where table_name = 'SALES_DATA') 7* order by index_name, partition_position SQL> / INDEX_NAME PARTITION_NA TABLESPACE_NAME STATUS ------------------------------ ------------ ------------------ -------- SALES_DATA_LCL_NDX_1 P_2016 TBS_YEAR_2016 UNUSABLE SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE SALES_DATA_LCL_NDX_2 P_2016 TBS_YEAR_2016 UNUSABLE SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE 12 rows selected. SQL> SQL> alter index SALES_DATA_LCL_NDX_1 2 rebuild partition P_2016 3 tablespace ARCHIVE_SALES_DATA 4 / Index altered. SQL> SQL> alter index SALES_DATA_LCL_NDX_2 2 rebuild partition P_2016 3 tablespace ARCHIVE_SALES_DATA 4 / Index altered. SQL> SQL> l 1 select index_name, partition_name, tablespace_name, status 2 from user_ind_partitions 3 where index_name in 4 (select index_name 5 from user_indexes 6 where table_name = 'SALES_DATA') 7* order by index_name, partition_position SQL> / INDEX_NAME PARTITION_NA TABLESPACE_NAME STATUS ------------------------------ ------------ ------------------ -------- SALES_DATA_LCL_NDX_1 P_2016 ARCHIVE_SALES_DATA USABLE SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE SALES_DATA_LCL_NDX_2 P_2016 ARCHIVE_SALES_DATA USABLE SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE 12 rows selected. SQL>
When I relocated the P_2016 Partition (to the ARCHIVE_SALES_DATA Tablespace), the ROWIDs for rows in that Partition changed. So the Non-Partitioned Index SYS_C0017514 and the corresponding Local Partitions of the two Partitioned Indexes became "UNUSABLE". These had to be rebuilt. Alternatively, I could have added the UPDATE INDEXES clause to to the ALTER TABLE ... MOVE PARTITION .. statement to reset the Indexes to Usable but this would not have relocated the Local Partitions for those two Indexes to the new Tablespace.
Note that for Table Partitions, the MOVE clause relocates the Partition but for Index Partition the REBUILD clause is used to relocate (as well as make Usable) the Partition.
I would encourage you to view documentation and examples of the MOVE ONLINE facility in 12c to relocate a Table Partition without downtime.
1 comment:
Interesting post , thank you for the share.
Post a Comment