Several Methods to convert a common Oracle table to a partitioned table

Several Methods to convert a common Oracle table to a partitioned table
There are four ways to convert a normal table to a Partition table: 1) Export/import method 2) Insert with a subquery method 3) Partition exchange method (SWAP Partition) 4) DBMS_REDEFINITION (online redefinition) Steps for exchanging partitions: 1) create A partition table, assuming there are two partitions, P1, P2 2) Create Table A to store data of P1 Rules 3) create Table B to store P2 rule data 4) Use table A and P1 partition exchange, put table A's data to P1 partition 5) Use table B and p2 partition exchange, example of storing data in Table B in P2 partition:. create a partition table: SQL> create table p_dba 2 (id number, time date) 3 partition by range (time) 4 (5 partition p1 values less than (to_date ('123 ') -09-1 ', 'yyyy-mm-dd'), 6 partition p2 values less than (to_date ('2017-11-1', 'yyyy-mm-dd ')) 7); B. CREATE two base tables corresponding to the partition: SQL> CREATE TABLE dba_p1 as SELECT id, time_detail FROM dba_old WHERE time_detail <TO_DATE ('2017-09-1 ', 'yyyy-MM-DD '); SQL> CREATE TABLE dba_p2 as SELECT id, time_sequence FROM dba_old WHERE time_sequence <TO_DATE ('2017-11-1 ', 'yyyy-MM-DD') and time_sequence> TO_DATE ('2017-09-1 ', 'yyyy-MM-DD '); c. exchange two base tables with two partitions: SQL> alter table p_dba exchange partition p1 with table dba_p1; the table has been changed. SQL> alter table p_dba exchange partition p2 with table dba_p2; the table has been changed. Online redefinition ensures data consistency. During most of the time, tables can perform DML operations normally. It only locks the table in the instant of switching and has high availability. This method is flexible and can meet various needs. Additionally, you can grant permissions and create various constraints before the switchover, so that no additional management operations are required after the switchover is complete. This feature is available only in Versions later than 9.2.0.4. Online redefinition tables have the following features: (1) modifying the storage parameters of tables (2) Transferring tables to other tablespaces (3) add parallel query option (4) add or delete partitions (5) recreate a table to reduce fragments (6) change the heap table to an index organization table or perform the opposite operation (7) add or remove some restrictions for using online redefinition of a column: (1) There must be enough space to hold two copies of the table. (2) Primary key columns cannot be modified. (3) Tables must have primary keys. (4) Redefinition must be done within the same schema. (5) New columns added cannot be made not null until after t He redefinition operation. (6) Tables cannot contain LONGs, BFILEs or User Defined Types. (7) Clustered tables cannot be redefined. (8) Tables in the SYS or SYSTEM schema cannot be redefined. (9) Tables with materialized view logs or materialized views defined on them cannot be redefined. (10) Horizontal sub setting of data cannot be stored med during the redefinition. the general online redefinition procedure is as follows: (1) create basic table. In, you do not need to perform operations (2) create A temporary partition table B (3) to start redefinition, and import the data of base table A to the temporary partition table B (4) to end the redefinition, at this time, two tables have been exchanged in Name Directory of DB. That is to say, base table A is A partition table, and the temporary partition table B we create is A normal table. In this case, we can delete the temporary table B we created. Example:. create basic table and index SQL> create table unpar_table (2 id number (10) primary key, 3 create_date date 4); B. collect table statistics SQL> exec dbms_stats.gather_table_stats ('icd ', 'unpar _ table', cascade => true); pl/SQL process completed c. create temporary partition table SQL> create table par_table (id number primary key, time date) partition by range (time) 2 (partition p1 values less than (to_date ('2017-7-1 ', 'yyyy-mm-dd'), 3 partition p2 values le Ss than (to_date ('2017-1-1 ', 'yyyy-mm-dd'), 4 partition p3 values less than (to_date ('2017-7-1 ', 'yyyy-mm-dd'), 5 partition p4 values less than (maxvalue); d. perform the redefinition operation d1. check the rationality of the redefinition d2. if d1 is correct, start redefinition. This process may take a while. Note: If the partition table and the original table column name are the same, you can use the following method: SQL> BEGIN DBMS_REDEFINITION.start_redef_table (uname => 'icd ', orig_table => 'unpar _ table', int_table => 'par _ table'); END; /if the name of the partition table is inconsistent with that of the original table, you need to re-define Newly specified ing relationship: SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE ('icd ', 'unpar _ table', 'par _ table', 'Id ID, create_date time ', -- specify the new ing relationship DBMS_REDEFINITION.CONS_USE_PK). After this step is completed, the data has been synchronized to this temporary partition table. D3. synchronize new tables. This is an optional SQL operation> BEGIN 2 dbms_redefinition.sync_interim_table (3 uname => 'icd ', 4 orig_table => 'unpar _ table ', 5 int_table => 'par _ table'); 6 END; 7/PL/SQL process completed successfully. D4. create an index. For online redefinition, only data is redefined. For an index, you also need to create an SQL> create index create_date_ind2 on par_table (time ); d5. collect statistics for new tables SQL> exec dbms_stats.gather_table_stats ('icd ', 'par _ table', cascade => true ); d6. end redefinition SQL> BEGIN 2 dbms_redefinition.finish_redef_table (3 uname => 'icd ', 4 orig_table => 'unpar _ table', 5 int_table => 'par _ table '); 6 END; 7/PL/SQL the process has successfully completed the END of redefinition. Meaning: The base table unpar_table and the temporary partition table par_table are exchanged. In this case, the temporary partition table par_table is a common table, and our base table unpar_table is a partition table. During the redefinition, the base table unpar_table can perform DML operations. There will be a temporary lock table only when two tables are switched.