ORACLE大数据量数据删除

  最近生产上报了一个数据连接错误，排查了原因后发现是oracle数据库中的两张表的数据量太大表空间不足引起；查了下数据，发现是交易的一些签名数据，数据并没有什么用，故需定期的清理这些数据，但是要保存一个月内的数据；通过统计，每张表每天的数据量大概是5W；接下来要做的事：

  1、清理掉历史数据，保存一个月的数据；

    数据量特别大的删除方法：可以通过临时表来删除，如要保存的数据小于被删除的数据 ，则可以先将有用的数据插入到临时表，并将原表drop掉，在重新建一个原表，并将数据从临时表中导入，再为表建立相关的key ，indexs 等，并删除临时表；第一次清理这些数据采用临时表的方法清理：

------------------------------------------------第一次清理个人、企业签名数据start------

 --===========================清理企业签名数据==============

    ----创建 CB_SIGN_DATA_TEMP 临时表，并将要保存的数据插入到临时表中

    CREATE TABLE  CB_SIGN_DATA_TEMP  AS  SELECT * FROM  CB_SIGN_DATA   WHERE substr(csd_time, 0, 8) <=  to_char(sysdate - 30 , 'yyyyMMdd');

    ----drop原表（删除效率最高）

    DROP TABLE CB_SIGN_DATA;

    ----重新创建表，并将数据从临时表中恢复

    CREATE TABLE  CB_SIGN_DATA  AS  SELECT * FROM  CB_SIGN_DATA_TEMP;

   

    ---创建key

    alter table CB_SIGN_DATA

      add primary key (CSD_LOGNO)

      using index

      tablespace EBANK_INDEX

      pctfree 10

      initrans 2

      maxtrans 255

      storage

      (

        initial 128K

        next 1M

        minextents 1

        maxextents unlimited

      );

   

   --===========================清理个人签名数据==============

    CREATE TABLE  PB_SIGN_DATA_TEMP  AS  SELECT * FROM  PB_SIGN_DATA   where substr(sda_time, 0, 8) <=  to_char(sysdate - 30, 'yyyyMMdd');

    DROP TABLE PB_SIGN_DATA;

    CREATE TABLE  PB_SIGN_DATA  AS  SELECT * FROM  PB_SIGN_DATA_TEMP;

    ---创建key

    alter table PB_SIGN_DATA

      add primary key (SDA_LOGNO)

      using index

      tablespace EBANK_DATA

      pctfree 10

      initrans 2

      maxtrans 255

      storage

      (

        initial 64K

        next 1M

        minextents 1

        maxextents unlimited

      );

      ---创建索引

      create index PB_SIGN_DATA_IDX1 on PB_SIGN_DATA (SDA_CSTNO)

        tablespace EBANK_DATA

        pctfree 10

        initrans 2

        maxtrans 255

        storage

        (

          initial 64K

          next 1M

          minextents 1

          maxextents unlimited

        );

     ---drop 临时表

     DROP TABLE PB_SIGN_DATA_TEMP;

     DROP TABLE CB_SIGN_DATA_TEMP; 

----------------------------------------------------第一次清理个人、企业签名数据end------

 2、每天定时清理掉30天后的数据：采用oracle自带的定时任务来处理，创建一个清理数据的存储过程，将这个存储过程配置到定时任务中即可；创建oracle job定时任务见博文：http://blog.youkuaiyun.com/supersanya/article/details/50454287

数据量不是特别大，可以通过循环删除，每删除多少条数据  commit一次   删除存储过程如下：

隐式游标的属性有：
SQL%FOUND – SQL 语句影响了一行或多行时为 TRUE
SQL%NOTFOUND – SQL 语句没有影响任何行时为TRUE
SQL%ROWCOUNT – SQL 语句影响的行数
SQL%ISOPEN - 游标是否打开，始终为FALSE

CREATE OR REPLACE PROCEDURE PUB_CLEAR_DATA_PROC IS

  v_date varchar2(8);

  p_Count varchar2(4);

  n_delete number:=0;

----清理30天前个人、企业签名数据

begin

  --dbms_output.put_line('=================================================');

  v_date := to_char(sysdate - 30, 'yyyyMMdd');

  p_Count:='5000';--- 每5000条提交一次

  --v_date :='20140605';

  

  while 1=1 loop

       EXECUTE IMMEDIATE 

              'delete from CB_SIGN_DATA t where substr(t.csd_time, 0, 8) <= '||v_date||' and rownum <=:rn' USING p_Count;

       EXECUTE IMMEDIATE 

              'delete from PB_SIGN_DATA t where substr(t.sda_time, 0, 8) <= '||v_date||' and rownum <=:rn' USING p_Count;

       if SQL%NOTFOUND then --当前语句没有发现数据，退出

          exit;

/*       else

          n_delete:=n_delete + SQL%ROWCOUNT;*/

       end if;

       commit;

    end loop;

    commit;

  --dbms_output.put_line('=================================================');

end;