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

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

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.