一次在线重定义引发的血案

  

大家好！

 

今天ANING和大家分享一个2年前的案例，这是一次血淋淋的教训。

 

当时需要是这样的，由于生产环境数据量越来越大，根据业务需求导致物理读、逻辑读十分严重，已经严重影响数据库性能，经过和上级领导讨论，决定把2张关键表改成分区表，为了不影响业务，通过在线重定义实现。

 

具体步骤如下：

 

1.1.  备份原表

create table ANINGTEST_temp as select * from ANINGTEST;

 

1.2.  创建重定义分区表

创建重定义临时表，字段类型、字段名称、索引等都需要和原表一致

/*==============================================================*/

/* Table: ANINGTEST3                                   */

/*==============================================================*/

CREATE TABLE ANINGTEST3

(

   SEQ                  NUMBER(18)           NOT NULL,

   GAMECODE             CHAR(2)              NOT NULL,

   PERIOD               VARCHAR2(50),

   VERSION              VARCHAR2(100),

   MERCHANTID           VARCHAR2(100),

   MESSAGEID            VARCHAR2(100),

   SYSTEMDATE           DATE,

   CONSTRAINT PK_ANINGTEST3 PRIMARY KEY (SEQ)

)

PCTFREE 20

 PARTITION BY RANGE

 (SYSTEMDATE)

    (

        PARTITION

             ANINGTEST_P1

            VALUES LESS THAN (TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

         PARTITION

             ANINGTEST_MAXVAL

            VALUES LESS THAN (MAXVALUE)

    );

 

 

1.3 授权ANING用户权限，sys用户执行

    grant dba to ANING;

 

1.4 检查原表是否能被在线重定义

EXEC Dbms_Redefinition.Can_Redef_Table('ANING', 'ANINGTEST');

 

1.5 启动在线重定义

BEGIN

  DBMS_REDEFINITION.start_redef_table(

    uname      => 'ANING',       

    orig_table => 'ANINGTEST',

    int_table  => 'ANINGTEST3');

END;

/

 

1.6 同步原表和临时表数据，减少完成重定义锁表时间

 

BEGIN

  dbms_redefinition.sync_interim_table(

    uname      => 'ANING',       

    orig_table => 'ANINGTEST',

    int_table  => 'ANINGTEST3');

END;

/

 

1.7 完成在线重定义

临时表变成原表，原表变成临时表

BEGIN

  dbms_redefinition.finish_redef_table(

    uname      => 'ANING',       

    orig_table => 'ANINGTEST',

    int_table  => 'ANINGTEST3');

END;

/

 

1.8 删除原是表，现在的临时表

DROP TABLE ANINGTEST3 cascade constraints;

 

1.9 创建索引前，检查表上索引约束状态

  select index_name,status from user_indexes where table_name=upper('ANINGTEST');

 

  SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS  WHERE INDEX_NAME like 'IDX_ANINGTEST_%';

 

  select table_name,constraint_name,status from user_constraints where table_name=upper('ANINGTEST');

 

1.10 根据原来的索引和约束的名称，重新命名同时建立相关索引

CREATE INDEX IDX_ANINGTEST_1 ON ANINGTEST (

   PERIOD ASC

);

 

CREATE UNIQUE INDEX IDX_ANINGTEST_3 ON ANINGTEST (

   MESSAGEID ASC

);

 

ALTER INDEX PK_ANINGTEST2 RENAME TO PK_ANINGTEST;

 

ALTER INDEX PK_ANINGTEST rebuild online parallel 4  compute statistics;

 

ALTER INDEX PK_ANINGTEST NOPARALLEL;

 

ALTER TABLE ANINGTEST RENAME CONSTRAINT PK_ANINGTEST2 TO PK_ANINGTEST;

 

1.11创建索引后，检查表上索引约束状态

select index_name,status from user_indexes where table_name=upper('ANINGTEST');

 

  SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS  WHERE INDEX_NAME like 'IDX_ANINGTEST_%';

 

  select table_name,constraint_name,status from user_constraints where table_name=upper('ANINGTEST');

 

1.12 查看分区表信息

set lin 200    

set pages 200

select TABLE_NAME,PARTITION_NAME from user_tab_partitions  where   TABLE_NAME='ANINGTEST'  order by PARTITION_NAME ;

 

1.13 回收ANING用户的DBA权限

revoke dba from ANING;

grant UNLIMITED TABLESPACE to ANING;

 

1.14 在线重定义总结

oracle在回收dba权限时会把表空间使用权限同时回收，这导致回收后的用户没有操作任何表空间权限，当执行dml操作时会报表空间配额不足错,导致无法处理业务，而且还是客户发现系统不能使用

 

 

1.15 在线重定义过程异常处理

exec dbms_redefinition.abort_redef_table('ANING','ANINGTEST','ANINGTEST3');

或

BEGIN

  DBMS_REDEFINITION.abort_redef_table (

    uname      => 'ANING',       

    orig_table => 'ANINGTEST',

    int_table  => 'ANINGTEST3');

END;

/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12457158/viewspace-753998/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12457158/viewspace-753998/