压缩表转非压缩表（在线重定义）

1.重定义需要源表存在主键,使用dbms包检测 exec dbms_redefinition.can_redef_table('PAR', 'TEST'); ERROR at line 1: ORA-12089: cannot online redefine table "PAR"."TEST" with no primary key ORA-06512: at "SYS.DBMS_REDEFINITION", line 143 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1635 ORA-06512: at line 1 如果没有主键，可以使用rowid的方式（使用rowid方式，会产生名为M_ROW$$的unused列，可以在重定义后删除） EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('PAR','TEST', DBMS_REDEFINITION.CONS_USE_ROWID); 事后删除隐藏列： 查看隐藏列 select table_name,column_name,hidden_column from dba_tab_cols where table_name='T_01'; 删除隐藏列 alter table DEF.T_01 drop unused columns; 2.获取源表ddl和索引ddl 获取源表ddl set linesize 1000 set pagesize 0 set echo off set heading off set feedback off set trims ON set term off set trimout on set long 99999 select dbms_metadata.get_ddl('TABLE','TEST','PAR') from dual; 获取源表的local index的ddl索引 select dbms_metadata.get_ddl('INDEX','LOCAL_ID','PAR') from dual; 3.修改ddl语句，并创建表和索引 表的ddl（删掉压缩）   CREATE TABLE "DEF"."T_01"    (    "A" NUMBER,         "B" NUMBER,         "C" NUMBER    ) SEGMENT CREATION IMMEDIATE   PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255  COMPRESS BASIC 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 "USERS" 索引的ddl Xxxxxxx 4.检查是否开启行迁移（2个表都要看） select row_movement from dba_tables where table_name='TEST' and owner='PAR'; select row_movement from dba_tables where table_name='TEST_NEW' and owner='PAR'; 开启行迁移 alter table test enable row movement; alter table test_new enable row movement; 5.开始在线重定义 开始： exec dbms_redefinition.start_redef_table('PAR','TEST','TEST_NEW',null,dbms_redefinition.cons_use_rowid); 脚本： source ~/.bash_profile sqlplus / as sysdba >/tmp/start.log << EOF conn BUSBIKE/BUSBIKE alter table TERM_TRAN_LOG_TBL parallel 8;        exec dbms_redefinition.start_redef_table('BUSBIKE','TERM_TRAN_LOG_TBL','TERM_TRAN_LOG_TBL_QY',null,dbms_redefinition.cons_use_rowid); alter table TERM_TRAN_LOG_TBL parallel 1; exit EOF 如果第一次执行失败，需要重新执行，记得删除物化视图以及物化视图日志 drop materialized view test_new; SELECT LOG_OWNER,MASTER,LOG_TABLE FROM DBA_MVIEW_LOGS; DROP MATERIALIZED VIEW LOG on TEST; 6.复制表属性（传输触发器，权限，约束等依赖） 开始： declare num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('PAR','TEST','TEST_NEW', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors); END; / 脚本： 创建存储过程 create or replace procedure p_test is num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('BUSBIKE','TERM_TRAN_LOG_TBL','TERM_TRAN_LOG_TBL_QY', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors); END p_test; /  后台脚本执行  source ~/.bash_profile sqlplus / as sysdba >/tmp/copy.log << EOF alter table BUSBIKE.TERM_TRAN_LOG_TBL parallel 8; exec p_test alter table BUSBIKE.TERM_TRAN_LOG_TBL parallel 1; exit EOF 7.同步数据（可以减少结束重定义过程的锁表时间） 开始： exec dbms_redefinition.sync_interim_table('PAR','TEST','TEST_NEW'); 脚本： 增量 source ~/.bash_profile sqlplus / as sysdba >/tmp/add.log << EOF conn BUSBIKE/BUSBIKE alter table TERM_TRAN_LOG_TBL parallel 8;   exec dbms_redefinition.sync_interim_table('PAR','TERM_TRAN_LOG_TBL','TERM_TRAN_LOG_TBL_QY'); alter table TERM_TRAN_LOG_TBL parallel 1; exit EOF 8.完成在线重定义（期间会锁表） 开始： EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('PAR','TEST','TEST_NEW'); 脚本： source ~/.bash_profile sqlplus / as sysdba >/tmp/finsh.log << EOF conn BUSBIKE/BUSBIKE alter table TERM_TRAN_LOG_TBL parallel 8;        EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('BUSBIKE','TERM_TRAN_LOG_TBL','TERM_TRAN_LOG_TBL_QY'); alter table TERM_TRAN_LOG_TBL parallel 1; exit EOF **此时test表与test_new完成替换** 9.收尾 收集统计信息 begin dbms_stats.gather_table_stats ( ownname => 'PAR', tabname => 'TEST', granularity => 'ALL', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all columns size auto', no_invalidate => false, degree => 1, cascade => true); END; 我的： exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);   关闭行迁移 alter table test_new disable row movement; 删隐藏列 查看隐藏列 select table_name,column_name,hidden_column from dba_tab_cols where table_name='T_01'; 删除隐藏列 alter table DEF.T_01 drop unused columns;