Shulou(Shulou.com)05/31 Report--

This article focuses on "how Oracle12.2 moves partitions to different tablespaces". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how Oracle12.2 moves partitions to different table spaces.

The following example shows how to redefine multiple partitions online and move two partitions of the range-partitioned table salestable to the new tablespace. The original table jy.salestable is created as follows:

SQL > create table jy.salestable 2 (s_productid number, 3 s_saledate date, 4 s_custid number, 5 s_totalprice number) 6 tablespace users 7 partition by range (s_saledate) 8 (partition sal10q1 values less than (to_date ('01 dd-mon-yyyy' 2010, dd-mon-yyyy')), 9 partition sal10q2 values less than (to_date ('01 to_date, dd-mon-yyyy')), 10 partition sal10q3 values less than (to_date ('01 dd-mon-yyyy' 2010)) 'dd-mon-yyyy')), 11 partition sal10q4 values less than (to_date (' 01MujanMel 2011 dd-mon-yyyy') Table created.

This example moves the partitions sal10q1 and sal10q2 to the example tablespace. Sal10q3 and sal10q4 partitions are not moved. In order to move the partitioned tablespace example must exist. The tablespace example has been created here. Create a local partitioned index on the original table jy.salestable as follows:

SQL > create index jy.sales_index on jy.salestable (s_saledate, s_productid, s_custid) local;Index created.

Note that alter table can also be executed in 12.2. Move partition... Online statement to move the partition to another tablespace.

The online redefinition operation is as follows:

1. Log in to the database with the user to perform the online redefinition operation

SQL > conn jy/jy@jypdbConnected.

two。 Verify that the original table jy.salestable can perform online redefinition

SQL > begin 2 dbms_redefinition.can_redef_table (3 uname = > 'jy', 4 tname = >' salestable', 5 options_flag = > DBMS_REDEFINITION.CONS_USE_ROWID, 6 part_name = > 'sal10q1, sal10q2'), 7 end; 8 / PL/SQL procedure successfully completed.

3. Create an intermediate table in the new tablespace example. Because this is an online redefinition of the partition, the intermediate table cannot be a partition table.

SQL > create table jy.int_salestb1 2 (s_productid number, 3 s_saledate date, 4 s_custid number, 5 s_totalprice number) 6 tablespace example;Table created.SQL > create table jy.int_salestb2 2 (s_productid number, 3 s_saledate date, 4 s_custid number, 5 s_totalprice number) 6 tablespace example;Table created.

4. Use the rowid method to perform a redefinition operation

SQL > begin 2 dbms_redefinition.start_redef_table (3 uname = > 'jy', 4 orig_table = >' salestable', 5 int_table = > 'int_salestb1, int_salestb2', 6 col_mapping = > NULL, 7 options_flag = > DBMS_REDEFINITION.CONS_USE_ROWID, 8 part_name = >' sal10q1, sal10q2', 9 continue_after_errors = > TRUE), 10 end; 11 / PL/SQL procedure successfully completed.

Note that the part_name parameter is used to specify all partitions to be redefined, the int_table parameter is used to specify the intermediate table for each partition, and the continue_after_errors parameter is set to true, so the redefinition operation continues even when a particular partition encounters an error.

5. Create any local indexes on the intermediate table

SQL > create index jy.int_sales1_index on jy.int_salestb1 2 (s_saledate, s_productid, s_custid) 3 tablespace example;Index created.SQL > create index jy.int_sales2_index on jy.int_salestb2 2 (s_saledate, s_productid, s_custid) 3 tablespace example;Index created.

6. Optional operation to synchronize intermediate tables

SQL > begin 2 dbms_redefinition.sync_interim_table (3 uname = > 'jy', 4 orig_table = >' salestable', 5 int_table = > 'int_salestb1, int_salestb2', 6 part_name = >' sal10q1, sal10q2', 7 continue_after_errors = > TRUE), 8 end; 9 / PL/SQL procedure successfully completed.

7. Complete the redefinition operation

SQL > begin 2 dbms_redefinition.finish_redef_table (3 uname = > 'jy', 4 orig_table = >' salestable', 5 int_table = > 'int_salestb1, int_salestb2', 6 part_name = >' sal10q1, sal10q2', 7 continue_after_errors = > TRUE), 8 end; 9 / PL/SQL procedure successfully completed.

8. Optionally, query the dba_redefinition_status view to ensure that the redefinition operation for each partition is successful

SQL > select base_table_owner, base_table_name, operation, status from dba_redefinition_status;no rows selected

If any partition redefinition fails, the view dba_redefinition_errors will show the cause of the error and correct the failure to perform the online redefinition operation.

The following query shows that two partitions of the table jy.salestable have been moved to the new tablespace example

SQL > select partition_name, tablespace_name from dba_tab_partitions where table_name = 'SALESTABLE' and table_owner='JY' PARTITION_NAME TABLESPACE_NAME -- SAL10Q1 EXAMPLESAL10Q2 EXAMPLESAL10Q3 USERSSAL10Q4 USERS

To this online redefinition operation is complete

At this point, I believe you have a deeper understanding of "how Oracle12.2 moves partitions to different tablespaces". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.