【转】 基于主键的重定义表，解决delete日志表产生的碎片问题

基于主键的重定义表，解决delete日志表产生的碎片问题

http://bkeep.blog.163.com/blog/static/123414290200911109189588/

 

导读：

1，连接数据库    

2. 创建"原表"      

3. 检查是否可以执行在线重建表     

4. 创建中间表     

5 开始执行在线重建表      

6. 确认更改后的原表的结构     

7 我想查看TRIGGER的信息，可是不知道有那些视图可以用。于是查看DBA_VIEW的信息      

8，如果在线更改表结构过程中出错。请用下面的过程来撤销前面的操作！

正文：

1，连接数据库

[root@kk ~]# su - oracle

[oracle@kk ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 20 20:55:16 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

SQL> conn zbb/zbb

Connected.

 

2. 创建原表

SQL> create table table_log(id int, ctime date);

Table created.

 

SQL> insert into table_log select rownum,created from dba_objects where rownum<=10;

10 rows created.

 

SQL> commit;

Commit complete.

 

2.2 创建触发器

SQL> create or replace trigger tr_table_log

  2  before insert on table_log for each row

  3  begin

  4  null;            //假动作，不做任何事情

  5  end;

  6  /

Trigger created.

 

3. 检查是否可以执行在线重建表

SQL> begin

  2  dbms_redefinition.can_redef_table('zbb','table_log',dbms_redefinition.cons_use_pk);

  3  end;

  4  /

begin

*

ERROR at line 1:

ORA-12089: cannot online redefine table "ZBB"."TABLE_LOG" with no primary key  //报错：因为原表没有主键

ORA-06512: at "SYS.DBMS_REDEFINITION", line 137

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478

ORA-06512: at line 2

 

3.2 给原表添加一个主键

SQL> alter table table_log add constraint pk_table_log primary key (id);

Table altered.

在id 列上创建一个主键约束

3.3 再次执行检查

SQL> begin

  2  dbms_redefinition.can_redef_table('zbb','table_log',dbms_redefinition.cons_use_pk);

  3  end;

  4  /

PL/SQL procedure successfully completed.

 

4. 创建中间表

SQL> create table inter_table_log as select * from table_log where 1=0;

Table created.

 

4.2 给中间表添加主键约束

SQL> alter table inter_table_log add constraint pk_inter_table_log primary key(id);

Table altered.

 

5 开始执行在线重建表

SQL> exec dbms_redefinition.start_redef_table('zbb','table_log','inter_table_log');

PL/SQL procedure successfully completed.

 

5.2 统计inter_table_log总行数

SQL> select count(*) from inter_table_log;

  COUNT(*)

----------

        10

5.3 改变当前session日期格式

SQL> col comments format a20

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

 

5.5 再次给原表里面插入数据，模拟用户DML操作

SQL> insert into table_log

  2  select rownum+10,created+10 from dba_objects where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

 

5.6 执行同步

SQL> begin

  2  dbms_redefinition.sync_interim_table('zbb','table_log','inter_table_log');

  3  end;

  4  /

PL/SQL procedure successfully completed.

 

5.7 验证中间表中的数据量

SQL> select count(*) from inter_table_log;

  COUNT(*)

----------

        19

 

5.8 执行真正的重建工作。

SQL> begin

  2  dbms_redefinition.finish_redef_table('zbb','table_log','inter_table_log');

  3  end;

  4  /

PL/SQL procedure successfully completed.

 

6. 确认更改后的原表的结构

SQL> desc table_log;

 Name                                      Null?    Type

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

 ID                                        NOT NULL NUMBER(38)

 CTIME                                              DATE

 

6.2 查询约束条件

SQL> select table_name,constraint_name,status from user_constraints

  2  where table_name in ('TABLE_LOG','INTER_TABLE_LOG');

TABLE_NAME

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

CONSTRAINT_NAME                                              STATUS

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

INTER_TABLE_LOG

PK_TABLE_LOG                                                 ENABLED

 

TABLE_LOG

PK_INTER_TABLE_LOG                                           ENABLED

 

7 我想查看trigger的信息，可是不知道有那些视图可以用。于是查看dba_view的信息

SQL> desc dba_views;

 Name                                      Null?    Type

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

 OWNER                                     NOT NULL VARCHAR2(30)

 VIEW_NAME                                 NOT NULL VARCHAR2(30)

 TEXT_LENGTH                                        NUMBER

 TEXT                                               LONG

 TYPE_TEXT_LENGTH                                   NUMBER

 TYPE_TEXT                                          VARCHAR2(4000)

 OID_TEXT_LENGTH                                    NUMBER

 OID_TEXT                                           VARCHAR2(4000)

 VIEW_TYPE_OWNER                                    VARCHAR2(30)

 VIEW_TYPE                                          VARCHAR2(30)

 SUPERVIEW_NAME                                     VARCHAR2(30)

 

7.1 查询跟 trigger有关的视图

SQL> select view_name from dba_views where view_name like '%TRIGGER%';

VIEW_NAME

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

LOADER_TRIGGER_INFO

USER_TRIGGERS

ALL_TRIGGERS

DBA_TRIGGERS

USER_INTERNAL_TRIGGERS

ALL_INTERNAL_TRIGGERS

DBA_INTERNAL_TRIGGERS

USER_TRIGGER_COLS

ALL_TRIGGER_COLS

DBA_TRIGGER_COLS

KU$_TRIGGERCOL_VIEW

 

7.2 查询user_trigger的表结构

SQL> DESC USER_TRIGGERS;

 Name                                      Null?    Type

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

 TRIGGER_NAME                                       VARCHAR2(30)

 TRIGGER_TYPE                                       VARCHAR2(16)

 TRIGGERING_EVENT                                   VARCHAR2(227)

 TABLE_OWNER                                        VARCHAR2(30)

 BASE_OBJECT_TYPE                                   VARCHAR2(16)

 TABLE_NAME                                         VARCHAR2(30)

 COLUMN_NAME                                        VARCHAR2(4000)

 REFERENCING_NAMES                                  VARCHAR2(128)

 WHEN_CLAUSE                                        VARCHAR2(4000)

 STATUS                                             VARCHAR2(8)

 DESCRIPTION                                        VARCHAR2(4000)

 ACTION_TYPE                                        VARCHAR2(11)

 TRIGGER_BODY                                       LONG

 

7.3 查询当前用户所拥有的trigger

SQL> select trigger_name,table_name from user_triggers

TRIGGER_NAME                                                 TABLE_NAME

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

TR_TABLE_LOG                                                 INTER_TABLE_LOG

TR_ORGIN_TABLE                                               INTER_TABLE

TR_INTER_TABLE                                               ORGIN_TABLE

 

8，如果在线更改表结构过程中出错。请用下面的过程来撤销前面的操作！

执行DBMS_REDEFINITION.ABORT_REDEF_TABLE( ) 过程。