实录：oracle下大表清理整改

操作背景：

某业务局点，oracle9208数据库+AIX5306，由于业务增长迅速，相对应的几个表数据增长很快，影响系统运行速度。需要对其进行清理。由于该表有大量数据如果直接执行delete操作不能很快释放所占用的表空间，需做：

1． TABLEA表进行truncate操作来释放表空间；

2． TABLEB表没有本地索引，需要新建本地索引。

一：处理思路：

1． 备份需要留下的数据

create table TABLEA0529 as

 select *

 from tableA b

 WHERE b.SendTime <= SYSDATE

   AND b.InvalidTime > SYSDATE

   AND b.invalidflag = 0

   and b.sendtime>sysdate-90;

注：此种方式建立的表没有索引和主键，需要另外用脚本建。

2． 清空原表数据

     truncate table TABLEA;

3． 导入备份的数据，删除原备份表

 insert into   TABLEA

 select * from TABLEA0529;

 commit;

  drop table TABLEA0529;

4． 重新编译存储过程。

二．详细处理步骤：

由于TABLEB表为分区表，数据量超大，并且新建索引时不允许有进程访问该表，为不影响现网业务，需要先建立一个和现网表完全一致的备份表，但是数据为空，在该表建立好本地索引，然后将现网表和索引均更名，然后将备份表的表名和索引修改为现网一致。最后导入需要保留的数据。以下为具体步骤

如何查找未采用本地索引的数据：

SELECT distinct table_name FROM USER_INDEXES A

WHERE A.table_name IN(SELECT B.table_name FROM USER_TABLES B WHERE B.partitioned = 'YES') AND A.partitioned = 'NO'

1． 建立新的分区表

-- Create table

create table TABLEB0529

(

 BULLETINID   VARCHAR2(20),

 STAFFNO      VARCHAR2(10),

 READFLAG     NUMBER(1) default 0,

 PARTID       VARCHAR2(1),

 READTIME     DATE,

 SENDTIME     DATE,

 INVALIDTIME DATE,

 BULLETINTYPE NUMBER(4),

 TYPENAME     VARCHAR2(16),

 INVALIDFLAG NUMBER(1),

 TITLE        VARCHAR2(100),

 SENDSTAFFNO VARCHAR2(20),

 GRADE        NUMBER(2),

 AFFIXPATH    VARCHAR2(2000)

)

partition by range (PARTID)

(

 partition P0 values less than ('1')

    tablespace SERVICE_MAIN_DAT

    pctfree 10

    pctused 75

    initrans 4

    maxtrans 255

    storage

    (

      initial 1M

      next 1M

      minextents 1

      maxextents unlimited

      pctincrease 0

    ),

 partition P9 values less than (MAXVALUE)

    tablespace SERVICE_MAIN_DAT

    pctfree 10

    pctused 75

    initrans 4

    maxtrans 255

    storage

    (

      initial 1M

      next 1M

      minextents 1

      maxextents unlimited

      pctincrease 0

    )

)

;

2、建立本地索引

-- Create/Recreate indexes

create index IX_BULLETINREAD_BULLETINID1 on TABLEB0529 (BULLETINID);

create index IX_BULLETINREAD_STAFFNO1 on TABLEB0529 (STAFFNO)

LOCAL

 tablespace SERVICE_MAIN_IDX

 storage

 (

    initial 1M

    next 1M

    minextents 1

    maxextents unlimited

    pctincrease 0

 );

3、 插入所需要的数据

insert into tableB0529

 select *

 from tableB t

 where t.bulletinid >='2007011200002015'

   and t.bulletinid in (select distinct (b.bulletinid)

                          from tableA b

                         WHERE b.SendTime <= SYSDATE

                           AND b.InvalidTime > SYSDATE

                           AND b.invalidflag = 0

                           and b.sendtime > sysdate - 90);

commit;

4、 表名修改

 ALTER TABLE tableB RENAME TO tableBOLD;

 ALTER TABLE tableB0529 RENAME TO tableB;

5、索引修改：

 alter index IX_BULLETINREAD_BULLETINID rename to IX_BULLETINREAD_BULLETINID2;

 alter index IX_BULLETINREAD_BULLETINID1 rename to IX_BULLETINREAD_BULLETINID;

 alter index IX_BULLETINREAD_STAFFNO rename to IX_BULLETINREAD_STAFFNO2;

 alter index IX_BULLETINREAD_STAFFNO1 rename to IX_BULLETINREAD_STAFFNO;

6、 重新编译存储过程；

该步骤很重要一定要查看执行完毕后是否存在失效的存储过程。

©著作权归作者所有：来自51CTO博客作者hengdao_guo的原创作品，如需转载，请与作者联系，否则将追究法律责任

oracle职场休闲学习&工作