壓縮錶轉非壓縮表（線上重定義）

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;