Shulou(Shulou.com)06/01 Report--

In the previous article, we learned about oracle data and text import and export source code examples, and then let's take a look at how to convert a plain table into a partitioned table in oracle.

Oracle officially recommends that partitioned tables be used for management when the size of the table is larger than 2GB. Partitioned tables have great advantages over small tables in management and performance. This document does not describe the specific advantages for the time being, but mainly introduces several methods for converting ordinary tables into partitioned tables.

[method Overview] oracle officially provides the following four operation methods:

A) Export/import method (Import and Export)

B) Insert with a subquery method (method of inserting subqueries)

C) Partition exchange method (swap partition method)

D) DBMS_REDEFINITION (online redefinition)

The idea of these methods is to create a new partitioned table, then transfer the data of the old table to the new table, then transfer the corresponding dependencies, and finally rename the table and rename the new table and the old table.

Among them, A, B, C these three methods will affect the normal use of the system, this document does not introduce in detail, this document mainly introduces method D, which is a common method to convert ordinary tables into partitioned tables.

[online redefinition for partition table operation] the idea of the whole operation is as follows, taking the EMP table under SCOTT as an example

1. First confirm whether the following table can be partitioned

Confirmation based on primary key

BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE ('SOCTT','EMP',DBMS_REDEFINITION.CONS_USE_PK); END;/PL/SQL procedure successfully completed. There is no problem with the display.

two。 Create temporary tables with DEPTNO as the partition option

CREATE TABLE SCOTT.EMP_1 (EMPNONUMBER (4), ENAMEVARCHAR2 (10 BYTE), JOBVARCHAR2 (9 BYTE), MGRNUMBER (4), HIREDATEDATE, SALNUMBER (7 prizes 2), COMMNUMBER (7 prizes 2), DEPTNONUMBER (2) PARTITION BY RANGE (DEPTNO) (PARTITION EMP_A1 VALUES LESS THAN (20), PARTITION EMP_A2 VALUES LESS THAN (30), PARTITION EMP_A3 VALUES LESS THAN (40), PARTITION EMP_A4 VALUES LESS THAN (50), PARTITION EMP_A5 VALUES LESS THAN (60))

3. Start performing data migration

EXEC DBMS_REDEFINITION.START_REDEF_TABLE ('SCOTT',' EMP', 'EMP_1')

4. If the table has a lot of data and the 3-step period may be long, during which time the system may continue to write or update the data to the table EMP, you can execute the following statement to avoid long locking when performing the last step (the process is optional or not)

BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('SCOTT',' EMP', 'EMP_1'); END;/

5. Migrate permission objects

DECLAREnum_errors PLS_INTEGER;BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('SCOTT',' EMP','EMP_1',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors); END;/

6. Query related errors. Check before operation. Query DBA_REDEFINITION_ERRORS attempted to query errors:

Select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS

7. End the entire redefinition

BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE ('scott',' emp', 'emp_1'); END;/

I have made a table with a size of 2.3GB and a total of 3.6 million rows. The whole process takes about 56 seconds, and the whole process is quite fast. It is suggested that the execution of a specific production environment needs to be carried out after rigorous testing, and the length of execution time of the whole process can be known in the process of testing.

In addition, if an error occurs in the process of re-execution, you can end the entire process with the following statement:

BEGINDBMS_REDEFINITION.ABORT_REDEF_TABLE (uname = > 'SCOTT',orig_table = >' EMP',int_table = > 'EMP_1'); END

Summary

The above is the whole content of this article about the method of converting oracle ordinary tables into partitioned tables. I hope it will be helpful to you. Interested friends can continue to refer to this site: oracle database import and export command analysis, ORACLE SQL statement optimization technical points analysis, etc., if you have any questions, you can leave a message directly, the editor will reply to you in time. Thank you for your support to this site!

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.