Oracle 表在线重定义为分区表(示例)
SQL> CREATE TABLE TT AS SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','INDEX','FUNCTION');--创建原表
表已创建。
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ADMIN', 'TT', DBMS_REDEFINITION.CONS_USE_PK);--检测是否可以进行重定义
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('ADMIN', 'TT', DBMS_REDEFINITION.CONS_USE_PK); END;
*
第 1 行出现错误:
ORA-12089: 不能联机重新定义无主键的表 "ADMIN"."TT"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 1
SQL> ALTER TABLE TT ADD PRIMARY KEY (OBJECT_ID);--创建主键索引
表已更改。
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ADMIN', 'TT', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL 过程已成功完成。
SQL> CREATE TABLE MID_TT--创建中间表
? 2? (
? 3??? OWNER????????? VARCHAR2(30),
? 4??? OBJECT_NAME??? VARCHAR2(128),
? 5??? SUBOBJECT_NAME VARCHAR2(30),
? 6??? OBJECT_ID????? NUMBER NOT NULL,
? 7??? DATA_OBJECT_ID NUMBER,
? 8??? OBJECT_TYPE??? VARCHAR2(19),
? 9??? CREATED??????? DATE,
?10??? LAST_DDL_TIME? DATE,
?11??? TIMESTAMP????? VARCHAR2(19),
?12??? STATUS???????? VARCHAR2(7),
?13??? TEMPORARY????? VARCHAR2(1),
?14??? GENERATED????? VARCHAR2(1),
?15??? SECONDARY????? VARCHAR2(1)
?16? )TABLESPACE TEST
?17?? PARTITION BY LIST(OBJECT_TYPE) (
?18?? PARTITION TAB VALUES('TABLE'),
?19?? PARTITION IDX VALUES('INDEX'),
?20?? PARTITION FUNC VALUES('FUNCTION')
?21? );
表已创建。
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('ADMIN', 'TT', 'MID_TT');--初始化 表的重定义
PL/SQL 过程已成功完成。
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('ADMIN','TT', 'MID_TT');--完成 表的重定义
PL/SQL 过程已成功完成。
SQL> SELECT SEGMENT_NAME,PARTITION_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('TT','MID_TT') AND OWNER = 'ADMIN';--原表的结构与中间表的结构互换
SEGMENT_NAME????????????????????????????????????????????????????????????????????? PARTITION_NAME
--------------------------------------------- ------------------------------
MID_TT
TT??????????????????????????????????????????????????????????????????????????????? TAB
TT??????????????????????????????????????????????????????????????????????????????? IDX
TT??????????????????????????????????????????????????????????????????????????????? FUNC
SQL> SELECT TABLE_NAME,INDEX_NAME FROM DBA_INDEXES? WHERE TABLE_NAME IN ('TT','MID_TT') AND OWNER = 'ADMIN';--原表的主键索引也创建到中间表中
TABLE_NAME???????????????????? INDEX_NAME
------------------------------ ------------------------------
MID_TT???????????????????????? SYS_C007791
