Ordinary table to partition table (online redefinition)
Confirm whether the table can be partitioned
SQL > BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE ('SCOTT','EMP',DBMS_REDEFINITION.CONS_USE_PK)
3 END
4 /
Create a temporary table
CREATE TABLE "SCOTT". "EMP_TMP"
("EMPNO" NUMBER (4. 0)
"ENAME" VARCHAR2 (10)
"JOB" VARCHAR2 (9)
"MGR" NUMBER (4. 0)
"HIREDATE" DATE
"SAL" NUMBER (7. 2)
"COMM" NUMBER (7. 2)
"DEPTNO" NUMBER (2. 0)
)
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)
)
Start performing data migration
EXEC DBMS_REDEFINITION.START_REDEF_TABLE ('SCOTT',' EMP', 'EMP_TMP')
If it is possible to continue to update writes to the emp table,
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('SCOTT',' EMP', 'EMP_TMP')
END
/ (optional process)
Migration of permission objects
DECLARE
Num_errors PLS_INTEGER
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('SCOTT',' EMP','EMP_TMP'
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors)
END
/
Query related error
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
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE ('scott',' emp', 'emp_TMP')
END
/
If there is an error during execution, you can execute the following statement to terminate
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE (uname = > 'SCOTT'
Orig_table = > 'EMP'
Int_table = > 'EMP_TMP'
);
END
Inspection
SQL > SELECT tablespace_name, PARTITIONED FROM ALL_TABLES WHERE TABLE_NAME = 'EMP_TMP'
TABLESPACE_NAME PAR
-
USERS NO
SQL > SELECT tablespace_name, PARTITIONED FROM ALL_TABLES WHERE TABLE_NAME = 'EMP'
TABLESPACE_NAME PAR
-
YES
View partition information
Select table_name
Partitioning_type type
P.subpartitioning_type
P.partition_count
From user_part_tables p
