Oracle CTASrename 表重建方法操作总结一、概述使用CREATE TABLE AS SELECT(CTAS) 配合PARALLEL和NOLOGGING进行然后通过rename实现表重建是Oracle 中处理大表结构如转分区表最快速的方法之一但是这种方法不会自动迁移原表的非物理存储属性例如约束、索引、触发器、依赖对象等等都需要手工单独完成处理。注意事项表和列的注释 表上列的默认值 表上列的约束 表上列的索引 表上相关触发器 表上的对象权限 引用该表的外键约束需要重新指向 表上的依赖对象视图、存储过程、函数、包等,需要重新编译 表上的物化视图和物化视图日志 重建表和索引时可能都会使用parallelnologging模式来加快创建速度完成后一定要将这些属性修改回来 备注 关注表字段类型那些LOB、long字段都是我们重建表是需要考虑的 一些同步机制如果同步依赖rowid由于重建表rowid会该表可能造成实时同步失败这些都是我们需要考虑的 工作完成后检查一下所有依赖对象的有效性建议在重建前保存快照重建后与前面的快照比较 如果依赖对象中存在一些私有对象例如dblink等我们用DBA用户重新编译是会出现编译错误对于这种对象必须以对应对象的所属者才能编译成功 重建的只是直接依赖对象必须考虑那些间接依赖的对象例如 view1依赖A表view2依赖view1 确保表空间有足够的空间建议原表的1.5倍空闲空间二、具体CTASrename案例0、准备测试表--创建表 drop table BIG_TABLE; create table BIG_TABLE ( id number(10), created_date date default sysdate, lookup_id number(10), data varchar2(50) ); COMMENT ON TABLE big_table IS ctas tt; --模拟数据 --准备测试数据 --插入 10000 条模拟数据时间分布在 2023~2026 年 INSERT INTO BIG_TABLE (id, created_date, lookup_id, data) SELECT ROWNUM2000, TO_DATE(2023-01-01, YYYY-MM-DD) DBMS_RANDOM.VALUE(0, 1460), ROUND(DBMS_RANDOM.VALUE(1, 100)), DBMS_RANDOM.STRING(X, ROUND(DBMS_RANDOM.VALUE(5, 50))) FROM DUAL CONNECT BY LEVEL 10000; commit; --准备依赖对象用于测试 --授权 grant select,insert,update,delete on big_table to scott; -- 创建索引 create index bita_created_date_i on BIG_TABLE(created_date); create index bita_look_fk_i on BIG_TABLE(lookup_id); -- 约束1主键约束 ALTER TABLE BIG_TABLE ADD CONSTRAINT pk_big_table PRIMARY KEY (id); -- 约束2CHECK 约束限制 lookup_id 范围 ALTER TABLE BIG_TABLE ADD CONSTRAINT chk_lookup_id CHECK (lookup_id BETWEEN 1 AND 100); -- 约束3NOT NULL 约束 ALTER TABLE BIG_TABLE MODIFY data CONSTRAINT nn_data NOT NULL; -- 创建另一张表外键约束引用该表 drop table FK_BIG_TABLE; create table FK_BIG_TABLE ( id number,name varchar2(200), FK_id number, constraint fk_id_big foreign key (FK_id) references BIG_TABLE(id) ); -- 触发器1插入时自动生成 ID序列 触发器 drop sequence seq_big_table; CREATE SEQUENCE seq_big_table START WITH 1001 INCREMENT BY 1; CREATE OR REPLACE TRIGGER trg_big_table_bi BEFORE INSERT ON BIG_TABLE FOR EACH ROW WHEN (NEW.id IS NULL) BEGIN SELECT seq_big_table.NEXTVAL INTO :NEW.id FROM DUAL; END; / -- 触发器2更新时自动记录日志到日志表 drop table big_table_audit purge; CREATE TABLE big_table_audit ( operation VARCHAR2(10), old_id NUMBER(10), new_id NUMBER(10), old_data VARCHAR2(50), new_data VARCHAR2(50), changed_by VARCHAR2(30), changed_at DATE DEFAULT SYSDATE ); CREATE OR REPLACE TRIGGER trg_big_table_audit AFTER UPDATE OR DELETE ON BIG_TABLE FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (UPDATE, :OLD.id, :NEW.id, :OLD.data, :NEW.data, USER); ELSIF DELETING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (DELETE, :OLD.id, NULL, :OLD.data, NULL, USER); END IF; END; / -- 建立 3 个视图 -- 视图1按年份汇总统计 CREATE OR REPLACE VIEW v_big_table_yearly AS SELECT TO_CHAR(created_date, YYYY) AS year, COUNT(*) AS total_rows, MIN(created_date) AS first_date, MAX(created_date) AS last_date, AVG(lookup_id) AS avg_lookup_id FROM BIG_TABLE GROUP BY TO_CHAR(created_date, YYYY); -- 视图2按 lookup_id 分组统计 CREATE OR REPLACE VIEW v_big_table_lookup AS SELECT lookup_id, COUNT(*) AS cnt, MIN(data) AS sample_data, MAX(created_date) AS latest_date FROM BIG_TABLE GROUP BY lookup_id; -- 视图3审计日志视图关联原表 CREATE OR REPLACE VIEW v_big_table_audit AS SELECT a.operation, a.old_id, a.new_id, a.old_data, a.new_data, a.changed_by, a.changed_at FROM big_table_audit a ORDER BY a.changed_at DESC; -- 创建存储过程 CREATE OR REPLACE PROCEDURE sp_insert_big_table( p_count IN NUMBER, p_year_start IN NUMBER DEFAULT 2023, p_year_end IN NUMBER DEFAULT 2026 ) AS v_date_start DATE : TO_DATE(p_year_start || -01-01, YYYY-MM-DD); v_date_end DATE : TO_DATE(p_year_end || -12-31, YYYY-MM-DD); v_days NUMBER : v_date_end - v_date_start; BEGIN FOR i IN 1 .. p_count LOOP INSERT INTO BIG_TABLE (id, created_date, lookup_id, data) VALUES ( NULL, v_date_start DBMS_RANDOM.VALUE(0, v_days), ROUND(DBMS_RANDOM.VALUE(1, 100)), DBMS_RANDOM.STRING(X, ROUND(DBMS_RANDOM.VALUE(5, 50))) ); END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE(成功插入 || p_count); END sp_insert_big_table; / -- 函数根据 ID 查询 data 字段 CREATE OR REPLACE FUNCTION fn_get_big_table_data( p_id IN BIG_TABLE.id%TYPE ) RETURN BIG_TABLE.data%TYPE AS v_data BIG_TABLE.data%TYPE; BEGIN SELECT data INTO v_data FROM BIG_TABLE WHERE id p_id; RETURN v_data; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END fn_get_big_table_data; / --创建package包 -- 包头 CREATE OR REPLACE PACKAGE pkg_big_table AS -- 按年份查询数据 FUNCTION get_by_year(p_year IN NUMBER) RETURN SYS_REFCURSOR; -- 按 ID 删除数据 PROCEDURE delete_by_id(p_id IN NUMBER); -- 统计总行数 FUNCTION get_count RETURN NUMBER; END pkg_big_table; / -- 包体 CREATE OR REPLACE PACKAGE BODY pkg_big_table AS FUNCTION get_by_year(p_year IN NUMBER) RETURN SYS_REFCURSOR AS v_cur SYS_REFCURSOR; BEGIN OPEN v_cur FOR SELECT id, created_date, lookup_id, data FROM BIG_TABLE WHERE TO_CHAR(created_date, YYYY) TO_CHAR(p_year) ORDER BY created_date; RETURN v_cur; END get_by_year; PROCEDURE delete_by_id(p_id IN NUMBER) AS BEGIN DELETE FROM BIG_TABLE WHERE id p_id; IF SQL%ROWCOUNT 0 THEN RAISE_APPLICATION_ERROR(-20001, 未找到 ID || p_id); END IF; COMMIT; END delete_by_id; FUNCTION get_count RETURN NUMBER AS v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM BIG_TABLE; RETURN v_cnt; END get_count; END pkg_big_table; / set linesize 200 pagesize 999 col constraint_name format a20 col constraint_type format a15 col triggering_event format a20 col trigger_name format a20 col view_name format a20 col object_name format a20 col object_type format a20 -- 验证数据量 SELECT COUNT(*) FROM BIG_TABLE; -- 验证约束 SELECT constraint_name, constraint_type, status FROM user_constraints WHERE table_name BIG_TABLE; -- 验证触发器 SELECT trigger_name, triggering_event, status FROM user_triggers WHERE table_name IN (BIG_TABLE, BIG_TABLE_AUDIT); -- 验证视图 SELECT view_name FROM user_views WHERE view_name LIKE V_BIG_TABLE%; SELECT object_name, object_type, status FROM user_objects WHERE object_name LIKE V_BIG_TABLE%; -- 验证存储过程 SELECT object_name, object_type, status FROM user_objects WHERE object_name IN (SP_INSERT_BIG_TABLE, FN_GET_BIG_TABLE_DATA,PKG_BIG_TABLE); -- 验证包 set serveroutput on SELECT pkg_big_table.get_count FROM DUAL; GET_COUNT ---------- 100001、获取表定义和表大小set longc 9999 set long 99999 set linesize 400 pagesize 400 select dbms_metadata.get_ddl(TABLE,upper(BIG_TABLE),upper(user1)) from dual; 或者 select dbms_metadata.get_ddl(TABLE,upper(i_table_name),upper(i_owner)) from dual; 结果 ------------------------------------------------------------------------------------------------------------ CREATE TABLE USER1.BIG_TABLE ( ID NUMBER(10,0), CREATED_DATE DATE DEFAULT sysdate, LOOKUP_ID NUMBER(10,0), DATA VARCHAR2(50) CONSTRAINT NN_DATA NOT NULL ENABLE, CONSTRAINT PK_BIG_TABLE PRIMARY KEY (ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA ENABLE, CONSTRAINT CHK_LOOKUP_ID CHECK (lookup_id BETWEEN 1 AND 100) ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA -- 表大小查询包括索引 with temp_seg as ( select owner,segment_name from dba_lobs where TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1) union select owner,segment_name from dba_segments where segment_name upper(BIG_TABLE) AND OWNER upper(USER1) union select owner,index_name from dba_indexes where TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1) ) select a.tablespace_name,sum(a.bytes)/1024/1024 from dba_segments a,temp_seg b where a.owner b.owner and a.segment_nameb.segment_name group by a.tablespace_name;2、获取表的依赖对象定义最关键部分先将获取的结果保存后续会需要使用这些定义来重新创建1获取表注释 SELECT COMMENT ON TABLE ||table_Name|| IS || COMMENTS || ; FROM DBA_TAB_COMMENTS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 COMMENT ON TABLE BIG_TABLE IS ctastt; -- 获取列注释 SELECT COMMENT ON COLUMN ||table_Name||.|| COLUMN_NAME || IS || COMMENTS || ; FROM DBA_COL_COMMENTS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1) AND COMMENTS IS NOT NULL; 结果 无 2获取源表索引结构 -- 提取原表上所有索引的创建语句 SELECT DBMS_METADATA.GET_DDL(INDEX, INDEX_NAME, upper(USER1)) FROM DBA_INDEXES WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 CREATE INDEX USER1.BITA_CREATED_DATE_I ON USER1.BIG_TABLE (CREATED_DATE) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA CREATE INDEX USER1.BITA_LOOK_FK_I ON USER1.BIG_TABLE (LOOKUP_ID) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA CREATE UNIQUE INDEX USER1.PK_BIG_TABLE ON USER1.BIG_TABLE (ID) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA 3提取原表上所有除NOT NULL外的约束主键、外键、检查等 SELECT DBMS_METADATA.GET_DDL(CONSTRAINT, CONSTRAINT_NAME, upper(USER1)) FROM DBA_CONSTRAINTS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 ALTER TABLE USER1.BIG_TABLE MODIFY (DATA CONSTRAINT NN_DATA NOT NULL ENABLE) ALTER TABLE USER1.BIG_TABLE ADD CONSTRAINT CHK_LOOKUP_ID CHECK (lookup_id BETWEEN 1 AND 100) ENABLE ALTER TABLE USER1.BIG_TABLE ADD CONSTRAINT PK_BIG_TABLE PRIMARY KEY (ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA ENABLE 4获取表的授权 select grant ||privilege|| on ||owner||.||table_name|| to ||grantee||; from dba_tab_privs where TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 grant DELETE on USER1.BIG_TABLE to SCOTT; grant INSERT on USER1.BIG_TABLE to SCOTT; grant SELECT on USER1.BIG_TABLE to SCOTT; grant UPDATE on USER1.BIG_TABLE to SCOTT; 5提取触发器源码 SELECT DBMS_METADATA.GET_DDL(TRIGGER, TRIGGER_NAME, upper(USER1)) FROM DBA_TRIGGERS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 CREATE OR REPLACE TRIGGER USER1.TRG_BIG_TABLE_BI BEFORE INSERT ON BIG_TABLE FOR EACH ROW WHEN (NEW.id IS NULL) BEGIN SELECT seq_big_table.NEXTVAL INTO :NEW.id FROM DUAL; END; ALTER TRIGGER USER1.TRG_BIG_TABLE_BI ENABLE CREATE OR REPLACE TRIGGER USER1.TRG_BIG_TABLE_AUDIT AFTER UPDATE OR DELETE ON BIG_TABLE FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (UPDATE, :OLD.id, :NEW.id, :OLD.data, :NEW.data, USER); ELSIF DELETING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (DELETE, :OLD.id, NULL, :OLD.data, NULL, USER); END IF; END; ALTER TRIGGER USER1.TRG_BIG_TABLE_AUDIT ENABLE 6重建触发器需要先删除删除触发器语句 SELECT DROP TRIGGER ||OWNER||.||TRIGGER_NAME FROM DBA_TRIGGERS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 DROP TRIGGER USER1.TRG_BIG_TABLE_BI; DROP TRIGGER USER1.TRG_BIG_TABLE_AUDIT; 7依赖对象重建一般可以使用如下方式完成 select alter ||decode(type,PACKAGE BODY,PACKAGE,type)|| ||owner||.||name|| compile; from dba_dependencies a where a.referenced_name upper(BIG_TABLE) and a.referenced_owner upper(USER1); 结果 alter TRIGGER USER1.TRG_BIG_TABLE_AUDIT compile; alter FUNCTION USER1.FN_GET_BIG_TABLE_DATA compile; alter PACKAGE USER1.PKG_BIG_TABLE compile; alter VIEW USER1.V_BIG_TABLE_YEARLY compile; alter TRIGGER USER1.TRG_BIG_TABLE_BI compile; alter VIEW USER1.V_BIG_TABLE_LOOKUP compile; alter PROCEDURE USER1.SP_INSERT_BIG_TABLE compile; 8获取依赖对象的状态记录下来用于前后比较 select a.owner,a.name,a.type,b.status from dba_DEPENDENCIES a,dba_objects b where a.REFERENCED_OWNERupper(USER1) and a.REFERENCED_NAMEupper(BIG_TABLE) and a.NAMEb.object_name; 结果 OWNER NAME TYPE STATUS ---------- ------------------------------ ------------------ ------- USER1 V_BIG_TABLE_LOOKUP VIEW VALID USER1 SP_INSERT_BIG_TABLE PROCEDURE VALID USER1 PKG_BIG_TABLE PACKAGE BODY VALID USER1 PKG_BIG_TABLE PACKAGE BODY VALID USER1 V_BIG_TABLE_YEARLY VIEW VALID USER1 TRG_BIG_TABLE_BI TRIGGER VALID USER1 TRG_BIG_TABLE_AUDIT TRIGGER VALID USER1 FN_GET_BIG_TABLE_DATA FUNCTION VALID 9查询外键约束引用该表的 set linesize 200 pagesize 999 col table_name format a15 col owner format a10 col constraint_name format a20 col r_owner format a10 col constraint_type format a20 col r_constraint_name format a20 col table_name format a20 select a.table_name, a.owner, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name, b.table_name from dba_constraints a, dba_constraints b where a.constraint_type R and a.r_constraint_name b.constraint_name and a.r_owner b.owner and b.table_name upper(BIG_TABLE) and b.ownerupper(USER1); a.table_name /* 外键引用待重命名的表 * a.r_constraint_name, /*被外键引用的约束名*/ b.table_name /*被外键引用的表名*/ 结果 TABLE_NAME OWNER CONSTRAINT_NAME CONSTRAINT_TYPE R_OWNER R_CONSTRAINT_NAME TABLE_NAME -------------------- ---------- -------------------- ---------------- ---------- -------------------- FK_BIG_TABLE USER1 FK_ID_BIG; R USER1 PK_BIG_TABLE BIG_TABLE 删除外键约束和重建外键约束语句 alter table FK_BIG_TABLE drop constraint FK_ID_BIG; alter table FK_BIG_TABLE add constraint fk_id_big foreign key (FK_id) references BIG_TABLE_NEW(id); 10物化视图日志物化视图日志是为了快速刷新准备的而且从dba_dependencies 这张依赖表中无法查找出来的但是对于这个对象我们一定要保持谨慎和敬畏因为如果表上存在物化视图日志对象的话那么这张表无法完成rename在一个变更的晚上其它什么都OK了突然遇到一个这样的问题还得找开发确认是非常被动的整个变更很有可能因为这个无法确认而取消会直接报错查找表上的物化视图日志对象方法如下 select master,log_table from user_mview_logs a where master in (BIG_TABLE);3、正式开始CTAS1设置表为只读模式禁止增删改确保数据无变化业务会受到影响alter table user1.big_table read only;2开始CTASdrop table big_table_new; create table big_table_new ( id , created_date default sysdate, lookup_id , data ) nologging parallel 4 as select /* parallel(a 4) */ * from user1.big_table a; 备注 可以通过指定tablespace将表存放至新的表空间 建议通过上面的方式将默认值加入建表语句以防止遗漏掉 表的结构通过前面获取表定义得到4、CTAS完成后依赖对象处理重要部分1可选项、修改原表的索引名称方法如下 重建后索引的名字是否必须和以前的一样如果需要一样则必须将当前使用的索引名字先rename否则创建的时候会出现索引名字已经存在的错误 select alter index || owner || . || index_name || rename to ||substr(index_name, 1, 26) || _old; from dba_indexes a where a.table_owner upper(USER1) AND A.table_name upper(BIG_TABLE); alter index USER1.BITA_CREATED_DATE_I rename to BITA_CREATED_DATE_I_old; alter index USER1.BITA_LOOK_FK_I rename to BITA_LOOK_FK_I_old; alter index USER1.PK_BIG_TABLE rename to PK_BIG_TABLE_old; 2注释、约束、索引的提前创建 COMMENT ON TABLE BIG_TABLE_NEW IS ctastt; CREATE INDEX USER1.BITA_CREATED_DATE_I ON USER1.BIG_TABLE_NEW (CREATED_DATE) NOLOGGING parallel 4; CREATE INDEX USER1.BITA_LOOK_FK_I ON USER1.BIG_TABLE_NEW (LOOKUP_ID) NOLOGGING parallel 4; CREATE UNIQUE INDEX USER1.PK_BIG_TABLE_NEW ON USER1.BIG_TABLE_NEW (ID) NOLOGGING parallel 4; ALTER TABLE USER1.BIG_TABLE_NEW MODIFY (DATA CONSTRAINT NN_DATA NOT NULL ENABLE); ALTER TABLE USER1.BIG_TABLE_NEW ADD CONSTRAINT CHK_LOOKUP_ID1 CHECK (lookup_id BETWEEN 1 AND 100) ENABLE; ALTER TABLE USER1.BIG_TABLE_NEW ADD CONSTRAINT PK_BIG_TABLE_NEW PRIMARY KEY (ID) USING INDEX ENABLE ; 特别注意约束名称需要修改否则会冲突。索引名称也一样。 3引用原表的外键约束处理如下 alter table FK_BIG_TABLE drop constraint FK_ID_BIG; alter table FK_BIG_TABLE add constraint fk_id_big foreign key (FK_id) references BIG_TABLE_NEW(id); 4修改属性(改为logging noparallel属性) alter table big_table_new logging noparallel; alter index USER1.BITA_CREATED_DATE_I logging noparallel; alter index USER1.BITA_LOOK_FK_I logging noparallel; alter index USER1.PK_BIG_TABLE_NEW logging noparallel; 备注索引批量修改的语句查询 select alter index || owner || . || index_name || logging noparallel; from dba_indexes a where a.table_owner upper(USER1) AND A.table_name upper(BIG_TABLE);5、rename重命名表ALTER TABLE BIG_TABLE RENAME TO BIG_TABLE_OLD; ALTER TABLE BIG_TABLE_NEW RENAME TO BIG_TABLE;6、依赖对象继续处理重要部分1权限重新授予 grant DELETE on USER1.BIG_TABLE to SCOTT; grant INSERT on USER1.BIG_TABLE to SCOTT; grant SELECT on USER1.BIG_TABLE to SCOTT; grant UPDATE on USER1.BIG_TABLE to SCOTT; 2触发器重建 --先删除 DROP TRIGGER USER1.TRG_BIG_TABLE_BI; DROP TRIGGER USER1.TRG_BIG_TABLE_AUDIT; --再重建 CREATE OR REPLACE TRIGGER USER1.TRG_BIG_TABLE_BI BEFORE INSERT ON BIG_TABLE FOR EACH ROW WHEN (NEW.id IS NULL) BEGIN SELECT seq_big_table.NEXTVAL INTO :NEW.id FROM DUAL; END; / CREATE OR REPLACE TRIGGER USER1.TRG_BIG_TABLE_AUDIT AFTER UPDATE OR DELETE ON BIG_TABLE FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (UPDATE, :OLD.id, :NEW.id, :OLD.data, :NEW.data, USER); ELSIF DELETING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (DELETE, :OLD.id, NULL, :OLD.data, NULL, USER); END IF; END; / 3依赖对象重新编译 alter TRIGGER USER1.TRG_BIG_TABLE_AUDIT compile; alter FUNCTION USER1.FN_GET_BIG_TABLE_DATA compile; alter PACKAGE USER1.PKG_BIG_TABLE compile; alter VIEW USER1.V_BIG_TABLE_YEARLY compile; alter TRIGGER USER1.TRG_BIG_TABLE_BI compile; alter VIEW USER1.V_BIG_TABLE_LOOKUP compile; alter PROCEDURE USER1.SP_INSERT_BIG_TABLE compile;7、验证set linesize 200 pagesize 999 col constraint_name format a20 col constraint_type format a15 col triggering_event format a20 col trigger_name format a20 col view_name format a20 col object_name format a20 col object_type format a20 -- 验证数据量 SELECT COUNT(*) FROM BIG_TABLE; -- 验证约束 SELECT constraint_name, constraint_type, status FROM user_constraints WHERE table_name BIG_TABLE; -- 验证触发器 SELECT trigger_name, triggering_event, status FROM user_triggers WHERE table_name IN (BIG_TABLE, BIG_TABLE_AUDIT); -- 验证视图 SELECT view_name FROM user_views WHERE view_name LIKE V_BIG_TABLE%; SELECT object_name, object_type, status FROM user_objects WHERE object_name LIKE V_BIG_TABLE%; -- 验证存储过程 SELECT object_name, object_type, status FROM user_objects WHERE object_name IN (SP_INSERT_BIG_TABLE, FN_GET_BIG_TABLE_DATA,PKG_BIG_TABLE); --依赖对象的前后比较 select a.owner,a.name,a.type,b.status from dba_DEPENDENCIES a,dba_objects b where a.REFERENCED_OWNERupper(USER1) and a.REFERENCED_NAMEupper(BIG_TABLE) and a.NAMEb.object_name; 结果应该如下 OWNER NAME TYPE STATUS ---------- ------------------------------ ------------------ ------- USER1 V_BIG_TABLE_LOOKUP VIEW VALID USER1 SP_INSERT_BIG_TABLE PROCEDURE VALID USER1 PKG_BIG_TABLE PACKAGE BODY VALID USER1 PKG_BIG_TABLE PACKAGE BODY VALID USER1 V_BIG_TABLE_YEARLY VIEW VALID USER1 TRG_BIG_TABLE_BI TRIGGER VALID USER1 TRG_BIG_TABLE_AUDIT TRIGGER VALID USER1 FN_GET_BIG_TABLE_DATA FUNCTION VALID Oracle CTAS+rename 表重建方法操作总结
Oracle CTASrename 表重建方法操作总结一、概述使用CREATE TABLE AS SELECT(CTAS) 配合PARALLEL和NOLOGGING进行然后通过rename实现表重建是Oracle 中处理大表结构如转分区表最快速的方法之一但是这种方法不会自动迁移原表的非物理存储属性例如约束、索引、触发器、依赖对象等等都需要手工单独完成处理。注意事项表和列的注释 表上列的默认值 表上列的约束 表上列的索引 表上相关触发器 表上的对象权限 引用该表的外键约束需要重新指向 表上的依赖对象视图、存储过程、函数、包等,需要重新编译 表上的物化视图和物化视图日志 重建表和索引时可能都会使用parallelnologging模式来加快创建速度完成后一定要将这些属性修改回来 备注 关注表字段类型那些LOB、long字段都是我们重建表是需要考虑的 一些同步机制如果同步依赖rowid由于重建表rowid会该表可能造成实时同步失败这些都是我们需要考虑的 工作完成后检查一下所有依赖对象的有效性建议在重建前保存快照重建后与前面的快照比较 如果依赖对象中存在一些私有对象例如dblink等我们用DBA用户重新编译是会出现编译错误对于这种对象必须以对应对象的所属者才能编译成功 重建的只是直接依赖对象必须考虑那些间接依赖的对象例如 view1依赖A表view2依赖view1 确保表空间有足够的空间建议原表的1.5倍空闲空间二、具体CTASrename案例0、准备测试表--创建表 drop table BIG_TABLE; create table BIG_TABLE ( id number(10), created_date date default sysdate, lookup_id number(10), data varchar2(50) ); COMMENT ON TABLE big_table IS ctas tt; --模拟数据 --准备测试数据 --插入 10000 条模拟数据时间分布在 2023~2026 年 INSERT INTO BIG_TABLE (id, created_date, lookup_id, data) SELECT ROWNUM2000, TO_DATE(2023-01-01, YYYY-MM-DD) DBMS_RANDOM.VALUE(0, 1460), ROUND(DBMS_RANDOM.VALUE(1, 100)), DBMS_RANDOM.STRING(X, ROUND(DBMS_RANDOM.VALUE(5, 50))) FROM DUAL CONNECT BY LEVEL 10000; commit; --准备依赖对象用于测试 --授权 grant select,insert,update,delete on big_table to scott; -- 创建索引 create index bita_created_date_i on BIG_TABLE(created_date); create index bita_look_fk_i on BIG_TABLE(lookup_id); -- 约束1主键约束 ALTER TABLE BIG_TABLE ADD CONSTRAINT pk_big_table PRIMARY KEY (id); -- 约束2CHECK 约束限制 lookup_id 范围 ALTER TABLE BIG_TABLE ADD CONSTRAINT chk_lookup_id CHECK (lookup_id BETWEEN 1 AND 100); -- 约束3NOT NULL 约束 ALTER TABLE BIG_TABLE MODIFY data CONSTRAINT nn_data NOT NULL; -- 创建另一张表外键约束引用该表 drop table FK_BIG_TABLE; create table FK_BIG_TABLE ( id number,name varchar2(200), FK_id number, constraint fk_id_big foreign key (FK_id) references BIG_TABLE(id) ); -- 触发器1插入时自动生成 ID序列 触发器 drop sequence seq_big_table; CREATE SEQUENCE seq_big_table START WITH 1001 INCREMENT BY 1; CREATE OR REPLACE TRIGGER trg_big_table_bi BEFORE INSERT ON BIG_TABLE FOR EACH ROW WHEN (NEW.id IS NULL) BEGIN SELECT seq_big_table.NEXTVAL INTO :NEW.id FROM DUAL; END; / -- 触发器2更新时自动记录日志到日志表 drop table big_table_audit purge; CREATE TABLE big_table_audit ( operation VARCHAR2(10), old_id NUMBER(10), new_id NUMBER(10), old_data VARCHAR2(50), new_data VARCHAR2(50), changed_by VARCHAR2(30), changed_at DATE DEFAULT SYSDATE ); CREATE OR REPLACE TRIGGER trg_big_table_audit AFTER UPDATE OR DELETE ON BIG_TABLE FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (UPDATE, :OLD.id, :NEW.id, :OLD.data, :NEW.data, USER); ELSIF DELETING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (DELETE, :OLD.id, NULL, :OLD.data, NULL, USER); END IF; END; / -- 建立 3 个视图 -- 视图1按年份汇总统计 CREATE OR REPLACE VIEW v_big_table_yearly AS SELECT TO_CHAR(created_date, YYYY) AS year, COUNT(*) AS total_rows, MIN(created_date) AS first_date, MAX(created_date) AS last_date, AVG(lookup_id) AS avg_lookup_id FROM BIG_TABLE GROUP BY TO_CHAR(created_date, YYYY); -- 视图2按 lookup_id 分组统计 CREATE OR REPLACE VIEW v_big_table_lookup AS SELECT lookup_id, COUNT(*) AS cnt, MIN(data) AS sample_data, MAX(created_date) AS latest_date FROM BIG_TABLE GROUP BY lookup_id; -- 视图3审计日志视图关联原表 CREATE OR REPLACE VIEW v_big_table_audit AS SELECT a.operation, a.old_id, a.new_id, a.old_data, a.new_data, a.changed_by, a.changed_at FROM big_table_audit a ORDER BY a.changed_at DESC; -- 创建存储过程 CREATE OR REPLACE PROCEDURE sp_insert_big_table( p_count IN NUMBER, p_year_start IN NUMBER DEFAULT 2023, p_year_end IN NUMBER DEFAULT 2026 ) AS v_date_start DATE : TO_DATE(p_year_start || -01-01, YYYY-MM-DD); v_date_end DATE : TO_DATE(p_year_end || -12-31, YYYY-MM-DD); v_days NUMBER : v_date_end - v_date_start; BEGIN FOR i IN 1 .. p_count LOOP INSERT INTO BIG_TABLE (id, created_date, lookup_id, data) VALUES ( NULL, v_date_start DBMS_RANDOM.VALUE(0, v_days), ROUND(DBMS_RANDOM.VALUE(1, 100)), DBMS_RANDOM.STRING(X, ROUND(DBMS_RANDOM.VALUE(5, 50))) ); END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE(成功插入 || p_count); END sp_insert_big_table; / -- 函数根据 ID 查询 data 字段 CREATE OR REPLACE FUNCTION fn_get_big_table_data( p_id IN BIG_TABLE.id%TYPE ) RETURN BIG_TABLE.data%TYPE AS v_data BIG_TABLE.data%TYPE; BEGIN SELECT data INTO v_data FROM BIG_TABLE WHERE id p_id; RETURN v_data; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END fn_get_big_table_data; / --创建package包 -- 包头 CREATE OR REPLACE PACKAGE pkg_big_table AS -- 按年份查询数据 FUNCTION get_by_year(p_year IN NUMBER) RETURN SYS_REFCURSOR; -- 按 ID 删除数据 PROCEDURE delete_by_id(p_id IN NUMBER); -- 统计总行数 FUNCTION get_count RETURN NUMBER; END pkg_big_table; / -- 包体 CREATE OR REPLACE PACKAGE BODY pkg_big_table AS FUNCTION get_by_year(p_year IN NUMBER) RETURN SYS_REFCURSOR AS v_cur SYS_REFCURSOR; BEGIN OPEN v_cur FOR SELECT id, created_date, lookup_id, data FROM BIG_TABLE WHERE TO_CHAR(created_date, YYYY) TO_CHAR(p_year) ORDER BY created_date; RETURN v_cur; END get_by_year; PROCEDURE delete_by_id(p_id IN NUMBER) AS BEGIN DELETE FROM BIG_TABLE WHERE id p_id; IF SQL%ROWCOUNT 0 THEN RAISE_APPLICATION_ERROR(-20001, 未找到 ID || p_id); END IF; COMMIT; END delete_by_id; FUNCTION get_count RETURN NUMBER AS v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM BIG_TABLE; RETURN v_cnt; END get_count; END pkg_big_table; / set linesize 200 pagesize 999 col constraint_name format a20 col constraint_type format a15 col triggering_event format a20 col trigger_name format a20 col view_name format a20 col object_name format a20 col object_type format a20 -- 验证数据量 SELECT COUNT(*) FROM BIG_TABLE; -- 验证约束 SELECT constraint_name, constraint_type, status FROM user_constraints WHERE table_name BIG_TABLE; -- 验证触发器 SELECT trigger_name, triggering_event, status FROM user_triggers WHERE table_name IN (BIG_TABLE, BIG_TABLE_AUDIT); -- 验证视图 SELECT view_name FROM user_views WHERE view_name LIKE V_BIG_TABLE%; SELECT object_name, object_type, status FROM user_objects WHERE object_name LIKE V_BIG_TABLE%; -- 验证存储过程 SELECT object_name, object_type, status FROM user_objects WHERE object_name IN (SP_INSERT_BIG_TABLE, FN_GET_BIG_TABLE_DATA,PKG_BIG_TABLE); -- 验证包 set serveroutput on SELECT pkg_big_table.get_count FROM DUAL; GET_COUNT ---------- 100001、获取表定义和表大小set longc 9999 set long 99999 set linesize 400 pagesize 400 select dbms_metadata.get_ddl(TABLE,upper(BIG_TABLE),upper(user1)) from dual; 或者 select dbms_metadata.get_ddl(TABLE,upper(i_table_name),upper(i_owner)) from dual; 结果 ------------------------------------------------------------------------------------------------------------ CREATE TABLE USER1.BIG_TABLE ( ID NUMBER(10,0), CREATED_DATE DATE DEFAULT sysdate, LOOKUP_ID NUMBER(10,0), DATA VARCHAR2(50) CONSTRAINT NN_DATA NOT NULL ENABLE, CONSTRAINT PK_BIG_TABLE PRIMARY KEY (ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA ENABLE, CONSTRAINT CHK_LOOKUP_ID CHECK (lookup_id BETWEEN 1 AND 100) ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA -- 表大小查询包括索引 with temp_seg as ( select owner,segment_name from dba_lobs where TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1) union select owner,segment_name from dba_segments where segment_name upper(BIG_TABLE) AND OWNER upper(USER1) union select owner,index_name from dba_indexes where TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1) ) select a.tablespace_name,sum(a.bytes)/1024/1024 from dba_segments a,temp_seg b where a.owner b.owner and a.segment_nameb.segment_name group by a.tablespace_name;2、获取表的依赖对象定义最关键部分先将获取的结果保存后续会需要使用这些定义来重新创建1获取表注释 SELECT COMMENT ON TABLE ||table_Name|| IS || COMMENTS || ; FROM DBA_TAB_COMMENTS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 COMMENT ON TABLE BIG_TABLE IS ctastt; -- 获取列注释 SELECT COMMENT ON COLUMN ||table_Name||.|| COLUMN_NAME || IS || COMMENTS || ; FROM DBA_COL_COMMENTS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1) AND COMMENTS IS NOT NULL; 结果 无 2获取源表索引结构 -- 提取原表上所有索引的创建语句 SELECT DBMS_METADATA.GET_DDL(INDEX, INDEX_NAME, upper(USER1)) FROM DBA_INDEXES WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 CREATE INDEX USER1.BITA_CREATED_DATE_I ON USER1.BIG_TABLE (CREATED_DATE) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA CREATE INDEX USER1.BITA_LOOK_FK_I ON USER1.BIG_TABLE (LOOKUP_ID) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA CREATE UNIQUE INDEX USER1.PK_BIG_TABLE ON USER1.BIG_TABLE (ID) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA 3提取原表上所有除NOT NULL外的约束主键、外键、检查等 SELECT DBMS_METADATA.GET_DDL(CONSTRAINT, CONSTRAINT_NAME, upper(USER1)) FROM DBA_CONSTRAINTS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 ALTER TABLE USER1.BIG_TABLE MODIFY (DATA CONSTRAINT NN_DATA NOT NULL ENABLE) ALTER TABLE USER1.BIG_TABLE ADD CONSTRAINT CHK_LOOKUP_ID CHECK (lookup_id BETWEEN 1 AND 100) ENABLE ALTER TABLE USER1.BIG_TABLE ADD CONSTRAINT PK_BIG_TABLE PRIMARY KEY (ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE APP_DATA ENABLE 4获取表的授权 select grant ||privilege|| on ||owner||.||table_name|| to ||grantee||; from dba_tab_privs where TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 grant DELETE on USER1.BIG_TABLE to SCOTT; grant INSERT on USER1.BIG_TABLE to SCOTT; grant SELECT on USER1.BIG_TABLE to SCOTT; grant UPDATE on USER1.BIG_TABLE to SCOTT; 5提取触发器源码 SELECT DBMS_METADATA.GET_DDL(TRIGGER, TRIGGER_NAME, upper(USER1)) FROM DBA_TRIGGERS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 CREATE OR REPLACE TRIGGER USER1.TRG_BIG_TABLE_BI BEFORE INSERT ON BIG_TABLE FOR EACH ROW WHEN (NEW.id IS NULL) BEGIN SELECT seq_big_table.NEXTVAL INTO :NEW.id FROM DUAL; END; ALTER TRIGGER USER1.TRG_BIG_TABLE_BI ENABLE CREATE OR REPLACE TRIGGER USER1.TRG_BIG_TABLE_AUDIT AFTER UPDATE OR DELETE ON BIG_TABLE FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (UPDATE, :OLD.id, :NEW.id, :OLD.data, :NEW.data, USER); ELSIF DELETING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (DELETE, :OLD.id, NULL, :OLD.data, NULL, USER); END IF; END; ALTER TRIGGER USER1.TRG_BIG_TABLE_AUDIT ENABLE 6重建触发器需要先删除删除触发器语句 SELECT DROP TRIGGER ||OWNER||.||TRIGGER_NAME FROM DBA_TRIGGERS WHERE TABLE_NAME upper(BIG_TABLE) AND OWNER upper(USER1); 结果 DROP TRIGGER USER1.TRG_BIG_TABLE_BI; DROP TRIGGER USER1.TRG_BIG_TABLE_AUDIT; 7依赖对象重建一般可以使用如下方式完成 select alter ||decode(type,PACKAGE BODY,PACKAGE,type)|| ||owner||.||name|| compile; from dba_dependencies a where a.referenced_name upper(BIG_TABLE) and a.referenced_owner upper(USER1); 结果 alter TRIGGER USER1.TRG_BIG_TABLE_AUDIT compile; alter FUNCTION USER1.FN_GET_BIG_TABLE_DATA compile; alter PACKAGE USER1.PKG_BIG_TABLE compile; alter VIEW USER1.V_BIG_TABLE_YEARLY compile; alter TRIGGER USER1.TRG_BIG_TABLE_BI compile; alter VIEW USER1.V_BIG_TABLE_LOOKUP compile; alter PROCEDURE USER1.SP_INSERT_BIG_TABLE compile; 8获取依赖对象的状态记录下来用于前后比较 select a.owner,a.name,a.type,b.status from dba_DEPENDENCIES a,dba_objects b where a.REFERENCED_OWNERupper(USER1) and a.REFERENCED_NAMEupper(BIG_TABLE) and a.NAMEb.object_name; 结果 OWNER NAME TYPE STATUS ---------- ------------------------------ ------------------ ------- USER1 V_BIG_TABLE_LOOKUP VIEW VALID USER1 SP_INSERT_BIG_TABLE PROCEDURE VALID USER1 PKG_BIG_TABLE PACKAGE BODY VALID USER1 PKG_BIG_TABLE PACKAGE BODY VALID USER1 V_BIG_TABLE_YEARLY VIEW VALID USER1 TRG_BIG_TABLE_BI TRIGGER VALID USER1 TRG_BIG_TABLE_AUDIT TRIGGER VALID USER1 FN_GET_BIG_TABLE_DATA FUNCTION VALID 9查询外键约束引用该表的 set linesize 200 pagesize 999 col table_name format a15 col owner format a10 col constraint_name format a20 col r_owner format a10 col constraint_type format a20 col r_constraint_name format a20 col table_name format a20 select a.table_name, a.owner, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name, b.table_name from dba_constraints a, dba_constraints b where a.constraint_type R and a.r_constraint_name b.constraint_name and a.r_owner b.owner and b.table_name upper(BIG_TABLE) and b.ownerupper(USER1); a.table_name /* 外键引用待重命名的表 * a.r_constraint_name, /*被外键引用的约束名*/ b.table_name /*被外键引用的表名*/ 结果 TABLE_NAME OWNER CONSTRAINT_NAME CONSTRAINT_TYPE R_OWNER R_CONSTRAINT_NAME TABLE_NAME -------------------- ---------- -------------------- ---------------- ---------- -------------------- FK_BIG_TABLE USER1 FK_ID_BIG; R USER1 PK_BIG_TABLE BIG_TABLE 删除外键约束和重建外键约束语句 alter table FK_BIG_TABLE drop constraint FK_ID_BIG; alter table FK_BIG_TABLE add constraint fk_id_big foreign key (FK_id) references BIG_TABLE_NEW(id); 10物化视图日志物化视图日志是为了快速刷新准备的而且从dba_dependencies 这张依赖表中无法查找出来的但是对于这个对象我们一定要保持谨慎和敬畏因为如果表上存在物化视图日志对象的话那么这张表无法完成rename在一个变更的晚上其它什么都OK了突然遇到一个这样的问题还得找开发确认是非常被动的整个变更很有可能因为这个无法确认而取消会直接报错查找表上的物化视图日志对象方法如下 select master,log_table from user_mview_logs a where master in (BIG_TABLE);3、正式开始CTAS1设置表为只读模式禁止增删改确保数据无变化业务会受到影响alter table user1.big_table read only;2开始CTASdrop table big_table_new; create table big_table_new ( id , created_date default sysdate, lookup_id , data ) nologging parallel 4 as select /* parallel(a 4) */ * from user1.big_table a; 备注 可以通过指定tablespace将表存放至新的表空间 建议通过上面的方式将默认值加入建表语句以防止遗漏掉 表的结构通过前面获取表定义得到4、CTAS完成后依赖对象处理重要部分1可选项、修改原表的索引名称方法如下 重建后索引的名字是否必须和以前的一样如果需要一样则必须将当前使用的索引名字先rename否则创建的时候会出现索引名字已经存在的错误 select alter index || owner || . || index_name || rename to ||substr(index_name, 1, 26) || _old; from dba_indexes a where a.table_owner upper(USER1) AND A.table_name upper(BIG_TABLE); alter index USER1.BITA_CREATED_DATE_I rename to BITA_CREATED_DATE_I_old; alter index USER1.BITA_LOOK_FK_I rename to BITA_LOOK_FK_I_old; alter index USER1.PK_BIG_TABLE rename to PK_BIG_TABLE_old; 2注释、约束、索引的提前创建 COMMENT ON TABLE BIG_TABLE_NEW IS ctastt; CREATE INDEX USER1.BITA_CREATED_DATE_I ON USER1.BIG_TABLE_NEW (CREATED_DATE) NOLOGGING parallel 4; CREATE INDEX USER1.BITA_LOOK_FK_I ON USER1.BIG_TABLE_NEW (LOOKUP_ID) NOLOGGING parallel 4; CREATE UNIQUE INDEX USER1.PK_BIG_TABLE_NEW ON USER1.BIG_TABLE_NEW (ID) NOLOGGING parallel 4; ALTER TABLE USER1.BIG_TABLE_NEW MODIFY (DATA CONSTRAINT NN_DATA NOT NULL ENABLE); ALTER TABLE USER1.BIG_TABLE_NEW ADD CONSTRAINT CHK_LOOKUP_ID1 CHECK (lookup_id BETWEEN 1 AND 100) ENABLE; ALTER TABLE USER1.BIG_TABLE_NEW ADD CONSTRAINT PK_BIG_TABLE_NEW PRIMARY KEY (ID) USING INDEX ENABLE ; 特别注意约束名称需要修改否则会冲突。索引名称也一样。 3引用原表的外键约束处理如下 alter table FK_BIG_TABLE drop constraint FK_ID_BIG; alter table FK_BIG_TABLE add constraint fk_id_big foreign key (FK_id) references BIG_TABLE_NEW(id); 4修改属性(改为logging noparallel属性) alter table big_table_new logging noparallel; alter index USER1.BITA_CREATED_DATE_I logging noparallel; alter index USER1.BITA_LOOK_FK_I logging noparallel; alter index USER1.PK_BIG_TABLE_NEW logging noparallel; 备注索引批量修改的语句查询 select alter index || owner || . || index_name || logging noparallel; from dba_indexes a where a.table_owner upper(USER1) AND A.table_name upper(BIG_TABLE);5、rename重命名表ALTER TABLE BIG_TABLE RENAME TO BIG_TABLE_OLD; ALTER TABLE BIG_TABLE_NEW RENAME TO BIG_TABLE;6、依赖对象继续处理重要部分1权限重新授予 grant DELETE on USER1.BIG_TABLE to SCOTT; grant INSERT on USER1.BIG_TABLE to SCOTT; grant SELECT on USER1.BIG_TABLE to SCOTT; grant UPDATE on USER1.BIG_TABLE to SCOTT; 2触发器重建 --先删除 DROP TRIGGER USER1.TRG_BIG_TABLE_BI; DROP TRIGGER USER1.TRG_BIG_TABLE_AUDIT; --再重建 CREATE OR REPLACE TRIGGER USER1.TRG_BIG_TABLE_BI BEFORE INSERT ON BIG_TABLE FOR EACH ROW WHEN (NEW.id IS NULL) BEGIN SELECT seq_big_table.NEXTVAL INTO :NEW.id FROM DUAL; END; / CREATE OR REPLACE TRIGGER USER1.TRG_BIG_TABLE_AUDIT AFTER UPDATE OR DELETE ON BIG_TABLE FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (UPDATE, :OLD.id, :NEW.id, :OLD.data, :NEW.data, USER); ELSIF DELETING THEN INSERT INTO big_table_audit (operation, old_id, new_id, old_data, new_data, changed_by) VALUES (DELETE, :OLD.id, NULL, :OLD.data, NULL, USER); END IF; END; / 3依赖对象重新编译 alter TRIGGER USER1.TRG_BIG_TABLE_AUDIT compile; alter FUNCTION USER1.FN_GET_BIG_TABLE_DATA compile; alter PACKAGE USER1.PKG_BIG_TABLE compile; alter VIEW USER1.V_BIG_TABLE_YEARLY compile; alter TRIGGER USER1.TRG_BIG_TABLE_BI compile; alter VIEW USER1.V_BIG_TABLE_LOOKUP compile; alter PROCEDURE USER1.SP_INSERT_BIG_TABLE compile;7、验证set linesize 200 pagesize 999 col constraint_name format a20 col constraint_type format a15 col triggering_event format a20 col trigger_name format a20 col view_name format a20 col object_name format a20 col object_type format a20 -- 验证数据量 SELECT COUNT(*) FROM BIG_TABLE; -- 验证约束 SELECT constraint_name, constraint_type, status FROM user_constraints WHERE table_name BIG_TABLE; -- 验证触发器 SELECT trigger_name, triggering_event, status FROM user_triggers WHERE table_name IN (BIG_TABLE, BIG_TABLE_AUDIT); -- 验证视图 SELECT view_name FROM user_views WHERE view_name LIKE V_BIG_TABLE%; SELECT object_name, object_type, status FROM user_objects WHERE object_name LIKE V_BIG_TABLE%; -- 验证存储过程 SELECT object_name, object_type, status FROM user_objects WHERE object_name IN (SP_INSERT_BIG_TABLE, FN_GET_BIG_TABLE_DATA,PKG_BIG_TABLE); --依赖对象的前后比较 select a.owner,a.name,a.type,b.status from dba_DEPENDENCIES a,dba_objects b where a.REFERENCED_OWNERupper(USER1) and a.REFERENCED_NAMEupper(BIG_TABLE) and a.NAMEb.object_name; 结果应该如下 OWNER NAME TYPE STATUS ---------- ------------------------------ ------------------ ------- USER1 V_BIG_TABLE_LOOKUP VIEW VALID USER1 SP_INSERT_BIG_TABLE PROCEDURE VALID USER1 PKG_BIG_TABLE PACKAGE BODY VALID USER1 PKG_BIG_TABLE PACKAGE BODY VALID USER1 V_BIG_TABLE_YEARLY VIEW VALID USER1 TRG_BIG_TABLE_BI TRIGGER VALID USER1 TRG_BIG_TABLE_AUDIT TRIGGER VALID USER1 FN_GET_BIG_TABLE_DATA FUNCTION VALID