Oracle getting started Tutorial: using online redefinition to change a common table to a partition table

You can change a common table to a partition table in the following ways:
1. Create a partition table A_NEW, which is the same as the original table;
Insert A_NEW SELECT * from;
Name Table A A_OLD and name A_NEW;
2. Use the previously redefined method! This is also the method to be introduced next!
The first method is to stop the write access to A by the application. The method of online redefinition can be transparent to the application!
The test example is as follows:
1. Create a test table
Create a common table:
@ Bigtab. SQL -- tom's Big Table creation script!
Create an intermediate partition PART_TAB and replace bigtab with PART_TAB.
Www.bkjia.com @ bkjia> create table PART_TAB
2 (id number,
3 OWNER VARCHAR2 (30) not null enable,
4 OBJECT_NAME VARCHAR2 (30) not null enable,
5 SUBOBJECT_NAME VARCHAR2 (30 ),
6 OBJECT_ID number not null enable,
7 DATA_OBJECT_ID NUMBER,
8 OBJECT_TYPE VARCHAR2 (19 ),
9 created date not null enable,
10 LAST_DDL_TIME date not null enable,
11 TIMESTAMP VARCHAR2 (19 ),
12 STATUS VARCHAR2 (7 ),
13 TEMPORARY VARCHAR2 (1 ),
14 GENERATED VARCHAR2 (1 ),
15 SECONDARY VARCHAR2 (1 ),
16 namespace number not null enable,
17 EDITION_NAME VARCHAR2 (30)
18) partition by range (id)
19 (PARTITION P1 values less than (100000 ),
20 PARTITION P2 values less than (200000 ),
21 PARTITION P3 values less than (300000 ),
22 PARTITION P4 values less than (400000 ),
23 PARTITION P5 values less than (MAXVALUE)
24 );
Table created.
2. Verify whether online redefinition can be performed:
Www.bkjia.com @ bkjia> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE (user, 'bigtab', DBMS_REDEFINITION.CONS_USE_PK );
PL/SQL procedure successfully completed.
3. Online redefinition of the execution table:
Www.bkjia.com @ bkjia> EXEC DBMS_REDEFINITION.START_REDEF_TABLE (USER, 'bigtab', 'Part _ tab ');
PL/SQL procedure successfully completed.
When Step 1 is completed, you can view it in session 2:
Www.bkjia.com @ bkjia> select count (*) from bigtab;
COUNT (*)
----------
500000
Www.bkjia.com @ bkjia> select count (*) FROM PART_TAB PARTITION (P2 );
COUNT (*)
----------
100000
Www.bkjia.com @ bkjia> select count (*) FROM PART_TAB PARTITION (P3 );
COUNT (*)
----------
100000
Www.bkjia.com @ bkjia>
4. Synchronize the content of the intermediate table with the data source table.
Perform dml operations on the original table in session 2 and submit:
Www.bkjia.com @ bkjia> update bigtab set id = id-1 where rownum <10000;
9999 rows updated.
Www.bkjia.com @ bkjia> commit;
Commit complete.
Synchronize the changed data in session 1! Wait for the corresponding time based on the data size!
Www.bkjia.com @ bkjia> execute dbms_redefinition.sync_interim_table (user, 'bigtab', 'Part _ tab ');
PL/SQL procedure successfully completed.
After the synchronization is complete, the intermediate table in the session query again is not the same as the original table:
Www.bkjia.com @ bkjia> select id from bigtab where rownum <10;

ID
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
Www.bkjia.com @ bkjia>
Www.bkjia.com @ bkjia> select id from PART_TAB where rownum <10;
ID
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
It will not be able to be viewed again until the end of the process!
5. Execute the online definition process.
Www.bkjia.com @ bkjia> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (user, 'bigtab', 'Part _ tab ');
PL/SQL procedure successfully completed.
6. view the data dictionary. You can see that the changed table has become a partition table.
Www.bkjia.com @ bkjia> select table_name, partition_name, high_value from user_tab_partitions;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------------------------------------
BIGTAB P1 100000
BIGTAB P2 200000
BIGTAB P3 300000
BIGTAB P4 400000
BIGTAB P5 MAXVALUE
Www.bkjia.com @ bkjia> select count (*) from bigtab;
COUNT (*)
----------
6264
Www.bkjia.com @ bkjia> select count (*) FROM PART_TAB PARTITION (P2 );
COUNT (*)
----------
6246

Now the normal table is partitioned.
If an error occurs when online redefinition is executed, run the following command after dbms_redefinition.start_redef_table to the time before dbms_redefinition.finish_redef_table: partial (user, 't', 'Part _ tab ') to discard the online redefinition.