在线重定义为分区表 参考: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html#GUID-6054142E-207A-4DF0-A62A-4C1A94DD36C4 https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_REDEFINITION.html#GUID-09FE5412-519C-412F-B1E0-77F75B1E8CA7
一、 DBMS_REDEFINITION.START_REDEF_TABLE 是 Oracle 数据库中的一个过程，用于启动表的在线重定义（Online Table Redefinition）过程。在线重定义是一种技术， 允许你在不中断正常数据库操作的情况下修改表的结构（例如添加、删除列，修改数据类型等）。
使用 DBMS_REDEFINITION.START_REDEF_TABLE 过程，可以在不停机的情况下进行以下操作： 表结构的修改： 可以添加、删除、修改表的列，修改数据类型等。 表的分区和分区键的修改： 可以对分区表的分区键进行修改。 索引的修改： 可以添加、删除、修改索引。 触发器、约束等的修改： 可以添加、删除、修改触发器、约束等。
在线重定义的过程可以分为以下几个步骤：
初始化： 创建一个用于在线重定义的临时表，将原始表的数据复制到临时表中。 同步： 在在线重定义过程中，继续收集在原始表上的DML（Data Manipulation Language）操作，以确保数据的一致性。 完成： 当所有的DML操作都同步到了临时表后，将DML操作应用到临时表，并将临时表的数据同步回原始表。
使用 DBMS_REDEFINITION.START_REDEF_TABLE 过程，可以启动在线重定义的过程，然后使用其他相关的 DBMS_REDEFINITION 过程来完成重定义过程。
二、 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS 是 Oracle 数据库中的一个过程，用于在执行在线表重定义（Online Table Redefinition）过程时， 将源表的相关依赖对象（例如索引、触发器、外键等）复制到重新定义后的目标表上。
在进行在线表重定义时，不仅需要考虑源表的数据迁移，还需要处理与表相关的依赖对象。COPY_TABLE_DEPENDENTS 过程的作用就是帮助你处理这些依赖对象， 确保它们在表重定义完成后的新表上存在并且是有效的。
使用 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS 过程，你可以在在线表重定义过程中执行以下操作： 复制索引： 将源表上的索引复制到新表上。 复制触发器： 将源表上的触发器复制到新表上。 复制外键： 将源表上的外键关系复制到新表上。 复制其他依赖对象： 复制其他与表相关的依赖对象，例如检查约束等。
三、 DBMS_REDEFINITION.FINISH_REDEF_TABLE 过程用于完成在线表重定义的最后一步，即将临时表的数据同步回原始表，然后清理和完成在线重定义过程。
################################################环境准备################################################ 1.使用sysdba权限登陆数据库 CONNECT / AS SYSDBA
2.创建eqnt测试用户,并授权 DROP USER eqnt CASCADE; CREATE USER eqnt IDENTIFIED BY eqnt; GRANT CONNECT, RESOURCE TO eqnt;
— Grant privleges required for online redefinition. GRANT EXECUTE ON DBMS_REDEFINITION TO eqnt; GRANT ALTER ANY TABLE TO eqnt; GRANT DROP ANY TABLE TO eqnt; GRANT LOCK ANY TABLE TO eqnt; GRANT CREATE ANY TABLE TO eqnt; GRANT SELECT ANY TABLE TO eqnt;
— Privileges required to perform cloning of dependent objects. GRANT CREATE ANY TRIGGER TO eqnt; GRANT CREATE ANY INDEX TO eqnt;
—表空间授权 ALTER USER eqnt QUOTA UNLIMITED ON “USERS”;
3.切换到eqnt连接,创建测试数据 CONNECT eqnt/eqnt
— (old) non partitioned nested table CREATE TABLE print_media ( product_id NUMBER(6), ad_textdocs_ntab VARCHAR2(22) );
INSERT INTO print_media VALUES (1,‘xx’); INSERT INTO print_media VALUES (11,‘aa’); COMMIT;
—建主键索引 CREATE UNIQUE INDEX pk_print_media ON print_media(product_id) PARALLEL 4 INITRANS 16; alter table print_media add constraint pk_print_media primary key (product_id) using INDEX;
—检查user_objects对象 SELECT * FROM user_objects
—共两个对象 PK_PRINT_MEDIA INDEX —PRINT_MEDIA表的索引 PRINT_MEDIA TABLE —第一次建的表
— Creating partitioned Interim Table CREATE TABLE print_media2 ( product_id NUMBER(6) , ad_textdocs_ntab VARCHAR2(22) ) PARTITION BY RANGE (product_id) ( PARTITION P1 VALUES LESS THAN (10), PARTITION P2 VALUES LESS THAN (20) );
—检查user_objects对象 SELECT * FROM user_objects
PK_PRINT_MEDIA INDEX —PRINT_MEDIA表的索引 PRINT_MEDIA2 P2 TABLE PARTITION —PRINT_MEDIA2的分区 PRINT_MEDIA2 P1 TABLE PARTITION —PRINT_MEDIA2的分区 PRINT_MEDIA2 TABLE —第二次建的表 PRINT_MEDIA TABLE —第一次建的表
################################################在线重定义################################################
1.开始在线表重定义
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => ‘eqnt’, —源表所属的用户（schema）名称
orig_table => ‘print_media’, —原始表的名称
int_table => ‘print_media2’ —,中间表（interim table）的名称，即在线重定义过程中使用的临时表
—col_mapping => ,—字段映射规则，用于将源表的列映射到中间表的列,表结构相同时不需要指定
—规则格式为 source_column
—options_flag => ,—控制在线表重定义的选项。常用的取值是 DBMS_REDEFINITION.CONS_USE_ROWID， —表示使用ROWID进行映射…理解为 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID
—orderby_cols => ,—用于指定在从源表复制数据到中间表时的排序规则,column1 ASC, column2 DESC,默认值为 NULL —p****** => ,—用于指定分区表中的特定分区，如果原始表是分区表理解为只针对某一个分区进行重定义 part_name,默认值为 NULL —continue_after_errors => ,—指定是否在遇到错误时继续执行,TRUE 表示继续执行，FALSE 表示遇到错误就停止,默认值为 TRUE —copy_vpd_opt => ,—是否复制源表上的 Virtual Private Database (VPD) 选项,TRUE 表示复制，FALSE 表示不复制,默认值为 TRUE —refresh_dep_mviews => ,—是否刷新依赖于源表的物化视图,TRUE 表示刷新，FALSE 表示不刷新,默认值为 TRUE —enable_rollback => —是否启用回滚,TRUE 表示启用，FALSE 表示不启用,默认值为 TRUE ); END;
—检查user_objects对象 SELECT * FROM user_objects
PK_PRINT_MEDIA INDEX —PRINT_MEDIA表的索引 PRINT_MEDIA2 P2 TABLE PARTITION —PRINT_MEDIA2的分区 PRINT_MEDIA2 P1 TABLE PARTITION —PRINT_MEDIA2的分区 PRINT_MEDIA2 TABLE —第二次建的表 MLOG_PRINT_MEDIA TABLE —多出来的表b I_MLOG$_PRINT_MEDIA INDEX —多出来的索引
2.查看四张表变化过程 SELECT T1.*,ROWID FROM PRINT_MEDIA T1;
SELECT T2.*,ROWID FROM PRINT_MEDIA2 T2;
SELECT T3.*,ROWID FROM MLOG$_PRINT_MEDIA T3;
SELECT T4.*,ROWID FROM RUPD$_PRINT_MEDIA T4;
其中 PRINT_MEDIA 表没有变化 其中 PRINT_MEDIA2 表同步了PRINT_MEDIA的数据 其中 MLOG_PRINT_MEDIA 无值
PRINT_MEDIA ROWID AAATL7AAMAAAACtAAA AAATL7AAMAAAACtAAB
PRINT_MEDIA2 ROWID AAATL+AAMAAAA4SAAA AAATL/AAMAAABISAAA
使用plsql查看表的建立,发现已经变成了物化视图 /* CREATE MATERIALIZED VIEW PRINT_MEDIA2 ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT “PRINT_MEDIA”.”PRODUCT_ID” “PRODUCT_ID”,“PRINT_MEDIA”.”AD_TEXTDOCS_NTAB” “AD_TEXTDOCS_NTAB” FROM “EQNT”.”PRINT_MEDIA” “PRINT_MEDIA”; */
尝试删除 print_media2 表 DROP TABLE print_media2 PURGE; 报错: ORA-12083:必须使用 DROP MATERIALIZED VIEW 来删除“EQNT”.”PRINT MEDIA2
这里删表必须删除物化视图才能做删表操作 例: DROP MATERIALIZED VIEW print_media2 ; DROP TABLE print_media2 PURGE;
3.对源表插入数据
INSERT INTO print_media VALUES (3,‘cc’); UPDATE print_media t SET t.product_id=2 WHERE t.product_id=11; DELETE FROM print_media t WHERE t.product_id=1; COMMIT;
4.查看四张表变化过程 SELECT T1.*,ROWID FROM PRINT_MEDIA T1;
SELECT T2.*,ROWID FROM PRINT_MEDIA2 T2;
SELECT T3.*,ROWID FROM MLOG$_PRINT_MEDIA T3;
SELECT T4.*,ROWID FROM RUPD$_PRINT_MEDIA T4;
其中 PRINT_MEDIA 表同修改语句变化 其中 PRINT_MEDIA2 表没有变化 其中 MLOG_PRINT_MEDIA 无值
PRINT_MEDIA ROWID AAATL7AAMAAAACtAAB AAATL7AAMAAAACtAAC
PRINT_MEDIA2 ROWID AAATL+AAMAAAA4SAAA AAATL/AAMAAABISAAA
MLOG$_PRINT_MEDIA 表记录内容(全是PRINT_MEDIA的变化) 3 I N FE 1.12595574141939E15 AAATMAAAMAAAA1dAAA —insert引起 11 D O 00 2.81486143626754E15 AAATMAAAMAAAA1dAAB —update引起,先删除 2 I N FF 2.81486143626754E15 AAATMAAAMAAAA1dAAC —update引起,再插入 1 D O 00 2.81487432116942E15 AAATMAAAMAAAA1dAAD —delete引起
5.复制依赖对象，包括主键约束 —所有的参数都没有默认值。每个参数都需要明确提供值，否则过程无法执行。
DECLARE error_count pls_integer := 0; BEGIN dbms_redefinition.copy_table_dependents( uname => ‘eqnt’,—源表所属的用户（schema）名称 orig_table => ‘print_media’,—原始表的名称 int_table => ‘print_media2’,—中间表（interim table）的名称，即在线重定义过程中使用的临时表 copy_indexes => 1,—是否复制源表上的索引,1 表示复制索引，0 表示不复制索引 copy_triggers => TRUE,—是否复制源表上的触发器,TRUE 表示复制触发器，FALSE 表示不复制触发器 copy_constraints => TRUE,—是否复制源表上的约束。可以使用常量,DBMS_REDEFINITION.CONS_ORIG_PARAMS’ 表示复制约束 copy_privileges => TRUE,—是否复制源表上的权限信息,TRUE 表示复制权限，FALSE 表示不复制权限 ignore_errors => FALSE,—指定是否忽略在复制依赖对象过程中发生的错误,TRUE 表示忽略错误，FALSE 表示不忽略错误 num_errors => error_count,—用于返回在复制依赖对象过程中发生的错误数量 copy_statistics => TRUE,—是否复制源表上的统计信息,TRUE 表示复制统计信息，FALSE 表示不复制统计信息 copy_mvlog => FALSE —是否复制源表上的物化视图日志,TRUE 表示复制物化视图日志，FALSE 表示不复制物化视图日志 );
dbms_output.put_line(‘errors := ’ || to_char(error_count)); END;
检查数据结果为 errors := 0
—检查user_objects对象 SELECT * FROM user_objects
检查结果: 多了一个索引 TMP$$_PK_PRINT_MEDIA0
6.结束在线表重定义
BEGIN dbms_redefinition.finish_redef_table( uname => ‘eqnt’,—源表所属的用户（schema）名称 orig_table => ‘print_media’,—原始表的名称 int_table => ‘print_media2’—,中间表（interim table）的名称，即在线重定义过程中使用的临时表 —p****** => ,—用于指定分区表中的特定分区，如果原始表是分区表 —dml_lock_timeout => ,— DML 操作的锁定超时时间，单位是秒。如果不指定，默认值是 NULL —continue_after_errors => ,—指定是否在遇到错误时继续执行,TRUE 表示继续执行，FALSE 表示遇到错误就停止 —disable_rollback => —是否禁用回滚操作,TRUE 表示禁用回滚，FALSE 表示允许回滚 ); END;
—检查user_objects对象 SELECT * FROM user_objects
以下表和索引消失 MLOG_PRINT_MEDIA TABLE —多出来的表b I_MLOG$_PRINT_MEDIA INDEX —多出来的索引
7.验证新表 SELECT * FROM print_media
— Drop the interim table DROP TABLE print_media2; 成功删除
—清回收站 PURGE RECYCLEBIN;
—检查user_objects对象 SELECT * FROM user_objects
名字及索引名已成功修改