ogg环境在线重定义普通表到分区表实战及生产操作注意事项-orastar

最新推荐文章于 2023-05-18 09:52:09 发布

cuihanbai4510

最新推荐文章于 2023-05-18 09:52:09 发布

阅读量531

点赞数

文章标签：数据库

实验目的

在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)

----------

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.