實驗目的

在ogg環境採用線上重定義的方式將普通錶轉化成分割槽表。
檢視線上重定義後各主鍵索引、普通索引及分割槽索引狀態。
檢視原表附加日誌狀態。

環境說明

Oracle 10.2.0.5、goldengate 11.1.1.1.2,aix5.3,ogg已開DDL
普通表1有主鍵索引A、普通索引B，分割槽表2有主鍵索引C、分割槽索引D

測試結果

採用線上重定義方式在OGG環境進行，普通錶轉化分割槽表操作，測試結果如下：

4個索引均正常，普通索引狀態valid,分割槽索引usable。
原表附加日誌disabled(根據線上重定義原因，很容易理解。如有疑問請參考《Oracle? Database Administrator’s Guide》Redefining Tables Online章節)。

測試過程

一資料準備

–建立普通表1

CREATE TABLE HT.SALESTABLE

(S_PRODUCTID NUMBER PRIMARY KEY,

S_SALEDATE DATE,

S_CUSTID NUMBER,

S_TOTALPRICE NUMBER);

–建立非主鍵普通索引

create index ht.idx_SALESTABLE on HT.SALESTABLE(S_CUSTID);

–產生業務資料

insert into HT.SALESTABLE values(1,TO_DATE('01-APR-2003', 'DD-MON-YYYY'),1,1);

insert into HT.SALESTABLE values(2,TO_DATE('01-JUL-2003', 'DD-MON-YYYY'),1,1);

insert into HT.SALESTABLE values(3,TO_DATE('01-OCT-2003', 'DD-MON-YYYY'),1,1);

insert into HT.SALESTABLE values(4,TO_DATE('01-JAN-2004', 'DD-MON-YYYY'),1,1);

commit;

–檢查資料生產情況

select count(1) from HT.SALESTABLE;

–生產分割槽表2

CREATE TABLE HT.PAR_SALESTABLE

2 (s_productid NUMBER primary key,

s_saledate DATE,

s_custid NUMBER,

s_totalprice NUMBER)

TABLESPACE users

PARTITION BY RANGE(s_saledate)

(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),

PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),

PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),

PARTITION sal03q4 VALUES LESS THAN (TO_DATE('30-JAN-2004', 'DD-MON-YYYY')));

–生成分割槽索引

create index ht.idx_PAR_SALESTABLE on HT.PAR_SALESTABLE(S_CUSTID) local;

二線上重定義

–驗證是否可以重定義

exec dbms_redefinition.can_redef_table('HT','SALESTABLE');

–開始線上重定義

exec dbms_redefinition.start_redef_table('HT', 'SALESTABLE', 'PAR_SALESTABLE');

–結果線上重定義

exec dbms_redefinition.finish_redef_table('HT','SALESTABLE','PAR_SALESTABLE');

–異常終止

exec dbms_redefinition.abort_redef_table('HT','SALESTABLE','PAR_SALESTABLE');

三操作記錄

SQL> CREATE TABLE HT.SALESTABLE

(S_PRODUCTID NUMBER PRIMARY KEY,

S_SALEDATE DATE,

S_CUSTID NUMBER,

S_TOTALPRICE NUMBER);

Table created.

SQL> create index ht.idx_SALESTABLE on HT.SALESTABLE(S_CUSTID);

Index created.

SQL> insert into HT.SALESTABLE values(1,TO_DATE('01-APR-2003', 'DD-MON-YYYY'),1,1);

insert into HT.SALESTABLE values(2,TO_DATE('01-JUL-2003', 'DD-MON-YYYY'),1,1);

insert into HT.SALESTABLE values(3,TO_DATE('01-OCT-2003', 'DD-MON-YYYY'),1,1);

insert into HT.SALESTABLE values(4,TO_DATE('01-JAN-2004', 'DD-MON-YYYY'),1,1);

commit;

1 row created.

SQL>

1 row created.

SQL>

1 row created.

SQL>

1 row created.

SQL>

Commit complete.

SQL> select count(1) from HT.SALESTABLE;

COUNT(1)

----------

4

SQL> CREATE TABLE HT.PAR_SALESTABLE

2 (s_productid NUMBER primary key,

s_saledate DATE,

s_custid NUMBER,

s_totalprice NUMBER)

TABLESPACE users

PARTITION BY RANGE(s_saledate)

(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),

PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),

PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),

PARTITION sal03q4 VALUES LESS THAN (TO_DATE('30-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL> create index ht.idx_PAR_SALESTABLE on HT.PAR_SALESTABLE(S_CUSTID) local;

Index created.

SQL> exec dbms_redefinition.can_redef_table('HT','SALESTABLE');

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.start_redef_table('HT', 'SALESTABLE', 'PAR_SALESTABLE');

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.finish_redef_table('HT','SALESTABLE','PAR_SALESTABLE');

PL/SQL procedure successfully completed.

四結果驗證

SQL> col owner for a30

SQL> set line 999

SQL> col TABLE_NAME for a50

col index_name for a30

col index_type for a20

select t.OWNER, t.TABLE_NAME, t.PARTITIONED, t.degree from dba_tables t where t.TABLE_NAME in ('SALESTABLE','PAR_SALESTABLE');

select d.owner, d.index_name, d.table_name,d.index_type,d.status, d.degree from dba_indexes d where d.table_name in ('SALESTABLE','PAR_SALESTABLE');

select p.index_owner, p.index_name,p.status from dba_ind_partitions p where p.index_name in ('IDX_PAR_SALESTABLE');SQL> SQL> SQL>

OWNER TABLE_NAME PARTITION DEGREE

------------------------------ -------------------------------------------------- --------- ------------------------------------------------------------

HT PAR_SALESTABLE NO 1

HT SALESTABLE YES 1

//注：透過線上重定義表SALESTABLE(之前非分割槽表)已變更為分割槽表

SQL>

OWNER INDEX_NAME TABLE_NAME INDEX_TYPE STATUS DEGREE

------------------------------ ------------------------------ -------------------------------------------------- -------------------- ------------------------ ------------------------------------

HT SYS_C00379199 PAR_SALESTABLE NORMAL VALID 1

HT IDX_SALESTABLE PAR_SALESTABLE NORMAL VALID 1

HT SYS_C00379202 SALESTABLE NORMAL VALID 1

HT IDX_PAR_SALESTABLE SALESTABLE NORMAL N/A 1

//注：非分割槽索引SYS_C00379199、IDX_SALESTABLE、SYS_C00379202均為VALID可用狀態.

SQL>

INDEX_OWNER INDEX_NAME STATUS

------------------------------------------------------------------------------------------ ------------------------------ ------------------------

HT IDX_PAR_SALESTABLE USABLE

//注：分割槽索引IDX_PAR_SALESTABLE均為可用狀態。

GGSCI (dcods1) 2> dblogin userid xxx password xxx

Successfully logged into database.

GGSCI (dcods1) 3> info trandata ht.SALESTABLE

Logging of supplemental redo log data is disabled for table HT.SALESTABLE.

GGSCI (dcods1) 4> info trandata ht.PAR_SALESTABLE

Logging of supplemental redo log data is enabled for table HT.PAR_SALESTABLE

//注：表SALESTABLE無附加日誌。

友情提示

如果大家生產環境操作請注意以下幾點：

資料量操作5000W儘量不要使用重定義的方式進行拆分。
儘量申請停業務操作
一定做好各表和索引狀態、並行度檢查
一定重新生產附加日誌
一定做好增、刪、改、查及測試資料清理驗證工作

注大家工作一切順利，God bless ‘dba’er!

ogg環境線上重定義普通表到分割槽表實戰及生產操作注意事項-orastar

相關文章