用户TEST为新创建的一个用户。
SQL> CONN TEST/TEST
Connected.
一、创建相应的组合分区表
SQL> SET TIMING ON
SQL> CREATE TABLE TEST
2 PARTITION BY RANGE(OBJECT_ID)
3 SUBPARTITION BY HASH(DATA_OBJECT_ID)
4 SUBPARTITION TEMPLATE
5 (
6 SUBPARTITION P1,
7 SUBPARTITION P2,
8 SUBPARTITION P3,
9 SUBPARTITION P4)
10 (
11 PARTITION PART1 VALUES LESS THAN(10000),
12 PARTITION PART2 VALUES LESS THAN(20000),
13 PARTITION PART3 VALUES LESS THAN(30000),
14 PARTITION PART4 VALUES LESS THAN(40000),
15 PARTITION PART5 VALUES LESS THAN(MAXVALUE)
16 )
17 AS
18 SELECT * FROM ALL_OBJECTS;
Table created.
Elapsed: 00:00:04.68
二、创建相应的约束及其全局索引和本地索引
SQL> ALTER TABLE TEST ADD PRIMARY KEY(OBJECT_ID);
Table altered.
Elapsed: 00:00:00.32
SQL> CREATE INDEX TEST_OBJECT_NAME_IDX ON TEST(OBJECT_NAME) LOCAL;
Index created.
Elapsed: 00:00:00.45
SQL> CREATE INDEX TEST_CREATED_IDX ON TEST(CREATED) GLOBAL
2 PARTITION BY RANGE(CREATED)
3 (
4 PARTITION PP1 VALUES LESS THAN(TO_DATE('1999-01-01','YYYY-MM-DD')),
5 PARTITION PP2 VALUES LESS THAN(TO_DATE('2001-01-01','YYYY-MM-DD')),
6 PARTITION PP3 VALUES LESS THAN(TO_DATE('2009-01-01','YYYY-MM-DD')),
7 PARTITION PP4 VALUES LESS THAN(MAXVALUE)
8 )
9 /
Index created.
Elapsed: 00:00:00.23
SQL> col index_name format a20
SQL> col subpartition_name format a20
SQL> select index_name,subpartition_name from user_ind_subpartitions
2 .
SQL> select index_name,subpartition_name,status from user_ind_subpartitions ;
INDEX_NAME SUBPARTITION_NAME STATUS
-------------------- -------------------- --------
TEST_OBJECT_NAME_IDX PART1_P1 USABLE
TEST_OBJECT_NAME_IDX PART1_P2 USABLE
TEST_OBJECT_NAME_IDX PART1_P3 USABLE
TEST_OBJECT_NAME_IDX PART1_P4 USABLE
TEST_OBJECT_NAME_IDX PART2_P1 USABLE
TEST_OBJECT_NAME_IDX PART2_P2 USABLE
TEST_OBJECT_NAME_IDX PART2_P3 USABLE
TEST_OBJECT_NAME_IDX PART2_P4 USABLE
TEST_OBJECT_NAME_IDX PART3_P1 USABLE
TEST_OBJECT_NAME_IDX PART3_P2 USABLE
TEST_OBJECT_NAME_IDX PART3_P3 USABLE
TEST_OBJECT_NAME_IDX PART3_P4 USABLE
TEST_OBJECT_NAME_IDX PART4_P1 USABLE
TEST_OBJECT_NAME_IDX PART4_P2 USABLE
TEST_OBJECT_NAME_IDX PART4_P3 USABLE
TEST_OBJECT_NAME_IDX PART4_P4 USABLE
TEST_OBJECT_NAME_IDX PART5_P1 USABLE
TEST_OBJECT_NAME_IDX PART5_P2 USABLE
TEST_OBJECT_NAME_IDX PART5_P3 USABLE
TEST_OBJECT_NAME_IDX PART5_P4 USABLE
20 rows selected.
SQL> col partition_name format a20
SQL> select index_name,partition_name,status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
-------------------- -------------------- --------
TEST_CREATED_IDX PP1 USABLE
TEST_CREATED_IDX PP2 USABLE
TEST_CREATED_IDX PP3 USABLE
TEST_CREATED_IDX PP4 USABLE
TEST_OBJECT_NAME_IDX PART1 N/A
TEST_OBJECT_NAME_IDX PART2 N/A
TEST_OBJECT_NAME_IDX PART3 N/A
TEST_OBJECT_NAME_IDX PART4 N/A
TEST_OBJECT_NAME_IDX PART5 N/A
9 rows selected.
SQL> select index_name,status from user_indexes;
INDEX_NAME STATUS
-------------------- --------
SYS_C006104 VALID
TEST_OBJECT_NAME_IDX N/A
TEST_CREATED_IDX N/A
三、验证表是否能在线重定义
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'TEST',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
四、创建中间表
SQL> CREATE TABLE INT_TEST
2 PARTITION BY RANGE(OBJECT_ID)
3 (
4 PARTITION PART1 VALUES LESS THAN(10000),
5 PARTITION PART2 VALUES LESS THAN(20000),
6 PARTITION PART3 VALUES LESS THAN(30000),
7 PARTITION PART4 VALUES LESS THAN(40000),
8 PARTITION PART5 VALUES LESS THAN(MAXVALUE)
9 )
10 AS
11 SELECT * FROM TEST WHERE 1=0
12 /
Table created.
五、开始执行在线重定义
Elapsed: 00:00:00.07
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname =>USER,
4 orig_table =>'TEST',
5 int_table =>'INT_TEST',
6 col_mapping =>NULL,
7 options_flag =>DBMS_REDEFINITION.CONS_USE_PK
8 );
9* END;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.78
六、复制相关的约束及其索引到中间表
SQL> DECLARE
2 NUMERROR NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
5 UNAME =>USER,
6 ORIG_TABLE =>'TEST',
7 INT_TABLE =>'INT_TEST',
8 COPY_INDEXES =>DBMS_REDEFINITION.CONS_ORIG_PARAMS,
9 COPY_TRIGGERS =>TRUE,
10 COPY_CONSTRAINTS=>TRUE,
11 COPY_PRIVILEGES =>TRUE,
12 IGNORE_ERRORS =>TRUE,
13 NUM_ERRORS =>NUMERROR,
14 COPY_STATISTICS =>TRUE);
15 DBMS_OUTPUT.PUT_LINE('ERRORS:'||TO_CHAR(NUMERROR));
16 END;
17/
ERRORS:6
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.31
七、检查相应的错误对象及其DDL语句
SQL> SELECT OBJECT_TYPE,OBJECT_OWNER,OBJECT_NAME,BASE_TABLE_OWNER ,BASE_TABLE_NAME FROM
DBA_REDEFINITION_ERRORS;
OBJECT_TYP OBJECT_OWN OBJECT_NAME BASE_TABLE BASE_TABLE
---------- ---------- -------------------- ---------- ----------
CONSTRAINT TEST SYS_C006100 TEST TEST
CONSTRAINT TEST SYS_C006101 TEST TEST
CONSTRAINT TEST SYS_C006102 TEST TEST
INDEX TEST TEST_OBJECT_NAME_IDX TEST TEST
CONSTRAINT TEST SYS_C006099 TEST TEST
CONSTRAINT TEST SYS_C006103 TEST TEST
6 rows selected.
Elapsed: 00:00:00.31
SQL> SELECT DDL_TXT FROM DBA_REDEFINITION_ERRORS
DDL_TXT
-------------------------------------------------------------------------------------------
-------------------
ALTER TABLE "TEST"."INT_TEST" MODIFY ("OBJECT_NAME" CONSTRAINT "TMP$$_SYS_C0061000" NOT NULL
ENABLE NOVALIDATE)
ALTER TABLE "TEST"."INT_TEST" MODIFY ("OBJECT_ID" CONSTRAINT "TMP$$_SYS_C0061010" NOT NULL
ENABLE NOVALIDATE)
ALTER TABLE "TEST"."INT_TEST" MODIFY ("CREATED" CONSTRAINT "TMP$$_SYS_C0061020" NOT NULL
ENABLE NOVALIDATE)
CREATE INDEX "TEST"."TMP$$_TEST_OBJECT_NAME_IDX0" ON "TEST"."INT_TEST" ("OBJECT_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT) LOCAL
(PARTITION "PART1"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
( SUBPARTITION "PART1_P1"
TABLESPACE "USERS",
SUBPARTITION "PART1_P2"
TABLESPACE "USERS",
SUBPARTITION "PART1_P3"
TABLESPACE "USERS",
SUBPARTITION "PART1_P4"
TABLESPACE "USERS") ,
PARTITION "PART2"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
( SUBPARTITION "PART2_P1"
TABLESPACE "USERS",
SUBPARTITION "PART2_P2"
TABLESPACE "USERS",
SUBPARTITION "PART2_P3"
TABLESPACE "USERS",
SUBPARTITION "PART2_P4"
TABLESPACE "USERS") ,
PARTITION "PART3"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
( SUBPARTITION "PART3_P1"
TABLESPACE "USERS",
SUBPARTITION "PART3_P2"
TABLESPACE "USERS",
SUBPARTITION "PART3_P3"
TABLESPACE "USERS",
SUBPARTITION "PART3_P4"
TABLESPACE "USERS") ,
PARTITION "PART4"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
( SUBPARTITION "PART4_P1"
TABLESPACE "USERS",
SUBPARTITION "PART4_P2"
TABLESPACE "USERS",
SUBPARTITION "PART4_P3"
TABLESPACE "USERS",
SUBPARTITION "PART4_P4"
TABLESPACE "USERS") ,
PARTITION "PART5"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
( SUBPARTITION "PART5_P1"
TABLESPACE "USERS",
SUBPARTITION "PART5_P2"
TABLESPACE "USERS",
SUBPARTITION "PART5_P3"
TABLESPACE "USERS",
SUBPARTITION "PART5_P4"
TABLESPACE "USERS") )
ALTER TABLE "TEST"."INT_TEST" MODIFY ("OWNER" CONSTRAINT "TMP$$_SYS_C0060990" NOT NULL
ENABLE NOVALIDATE)
ALTER TABLE "TEST"."INT_TEST" MODIFY ("LAST_DDL_TIME" CONSTRAINT "TMP$$_SYS_C0061030" NOT
NULL ENABLE NOVALIDATE)
6 rows selected.
Elapsed: 00:00:00.89
八、在中间表上手工创建本地索引并注册为重定义对象
SQL> CREATE INDEX TMP_LOCAL_IDX ON INT_TEST(OBJECT_NAME) LOCAL;
Index created.
Elapsed: 00:00:00.30
SQL> BEGIN
2 DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
3 uname=>USER,
4 orig_table=>'TEST',
5 int_table=>'INT_TEST',
6 dep_type=>DBMS_REDEFINITION.CONS_INDEX,
7 dep_owner=>USER,
8 dep_orig_name=>'TEST_OBJECT_NAME_IDX',
9 dep_int_name=>'TMP_LOCAL_IDX');
10 END;
11 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
SQL> SELECT OBJECT_TYPE,OBJECT_NAME,BASE_TABLE_NAME,INTERIM_OBJECT_NAME
FROM DBA_REDEFINITION_OBJECTS;
OBJECT_TYPE OBJECT_NAME BASE_TABLE INTERIM_OBJECT_NAME
------------ -------------------- ---------- ------------------------------
INDEX TEST_CREATED_IDX TEST TMP$$_TEST_CREATED_IDX0
TABLE TEST TEST INT_TEST
INDEX SYS_C006104 TEST TMP$$_SYS_C0061040
CONSTRAINT SYS_C006104 TEST TMP$$_SYS_C0061040
INDEX TEST_OBJECT_NAME_IDX TEST TMP_LOCAL_IDX
5 rows selected.
Elapsed: 00:00:00.00
九、结束在线重定义
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER,'TEST','INT_TEST');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.61
十、删除中间表
SQL> DROP TABLE INT_TEST;
Table dropped.
Elapsed: 00:00:00.21
十一、验证结果
SQL> SELECT TABLE_NAME,COMPOSITE,PARTITION_NAME ,SUBPARTITION_COUNT FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='TEST';
TABLE_NAME COM PARTITION_ SUBPARTITION_COUNT
---------- --- ---------- ------------------
TEST NO PART1 0
TEST NO PART2 0
TEST NO PART3 0
TEST NO PART4 0
TEST NO PART5 0
Elapsed: 00:00:00.00
SQL> select index_name,subpartition_name,status from user_ind_subpartitions WHERE
INDEX_NAME='TEST_OBJECT_NAME_IDX';
no rows selected
Elapsed: 00:00:00.04
子分区对应的索引已不存在。
SQL> select index_name,partition_name,status from user_ind_partitions WHERE
INDEX_NAME='TEST_OBJECT_NAME_IDX';
INDEX_NAME PARTITION_NAME STATUS
-------------------- -------------------- --------
TEST_OBJECT_NAME_IDX PART1 USABLE
TEST_OBJECT_NAME_IDX PART2 USABLE
TEST_OBJECT_NAME_IDX PART3 USABLE
TEST_OBJECT_NAME_IDX PART4 USABLE
TEST_OBJECT_NAME_IDX PART5 USABLE
5 rows selected.
Elapsed: 00:00:00.08
SQL> select index_name,status from user_indexes;
INDEX_NAME STATUS
-------------------- --------
TEST_OBJECT_NAME_IDX N/A
TEST_CREATED_IDX N/A
SYS_C006104 VALID
3 rows selected.
Elapsed: 00:00:00.07
