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

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;