清理臨時表規範以及指令碼

第一章 目的

 

為了最佳化資料庫的設計，提高資料庫設計的合理性和資料訪問高效性，同時便於資料共享的質量和效率，促進資料庫的統一標準化管理，特制定一套建立臨時表存放臨時資料使用的設計規範。

 

第二章 設計規範

本次設計需要將所有的臨時表存放在一個單獨的資料庫使用者中，我們採用 temp 使用者，temp使用者只可以檢視資料庫中所有使用者下面的表許可權。

本次設計臨時表規範主要按照資料庫建立臨時表資料存放的時間要求進行規範，該建立的臨時表並不要求做為業務提供資料的表進行使用，該規範要求主要分為五部分，如下：

 

Ø   臨時資料存放30天的，需要建立臨時表的名稱規則要已RES_1MON開頭.

Ø   臨時資料存放90天的，需要建立臨時表的名稱規則要已RES_3MON開頭.

Ø   臨時資料存放180天的，需要建立臨時表的名稱規則要已RES_6MON開頭.

Ø   臨時資料永久存放的，需要建立臨時表的名稱規則要已FOREVER開頭.

Ø   不按要求建立的臨時資料表存放每天將定時清理刪除.

   

按照以上的規則進行建立臨時表，將建立永久存放的的FOREVER開頭的臨時表，進行每一年為一個時間節點進行離線備份整理，並且會將所有的定時清理的臨時表都會記錄在DROP_TABLE_TEMP_LOG日誌表中進行記錄.

 

 

例如：

1、 建立臨時存放一個月（30天）資料的臨時表

create table RES_1MON _temp1 as select * from scott.emp ;

2、 建立臨時存放三個月（90天）資料的臨時表

create table RES_3MON _temp3 as select * from scott.emp ;

3、 建立臨時存放六個月（180天）資料的臨時表

create table RES_6MON _temp5 as select * from scott.emp ;

4、 建立臨時永久存放資料的臨時表

 

create table forever __temp3 as select * from scott.emp ;

   建立臨時表成功後，當時間超過30天，資料庫系統會在tmep使用者下刪除已 RES_1MON 開頭的所有表，當時間超過90天，資料庫系統會在tmep使用者下刪除已 RES_3MON 開頭的所有表，當時間超過180天，資料庫系統會在tmep使用者下刪除已 RES_6MON 開頭的所有表，當已 forever 開頭的表將不會刪除，不已該規範建立的臨時表將每天定期刪除。

   檢視臨時表刪除的記錄：

   select * from DROP_TABLE_TEMP_LOG ;

第三章 實現規範指令碼

以下是實現本次規範定時任務指令碼。

 

0、 建立temp使用者

Create user temp identified by oracle ;

grant create session to temp ;

grant select any table to temp ;

 

1、需要建立存放記錄刪除的日誌表

create table drop_table_temp_log

( table_name varchar2 ( 200 ), drop_time date , state varchar2 ( 10 ));

 

2、按照時間方式刪除臨時表

create or replace procedure drop_table_tmep is

  v_sql varchar2 ( 3000 );

  cursor object_mingzi is

    select *

      from user_objects

     where object_type = 'TABLE' ;

   cursor object_other is

    select *

      from user_tables

     where   table_name not like 'RES_1MON_%' and

     table_name not like 'RES_3MON_%' and   table_name not like 'RES_6MON_%' and table_name <> 'DROP_TABLE_TEMP_LOG'

     and table_name not like 'FOREVER_%' ;

begin

  for i in object_mingzi loop

    if i.object_name like 'RES_1MON_%' and

       to_date ( to_char ( sysdate , 'yyyy-dd-mm' ), 'yyyy-dd-mm' ) -

       to_date ( to_char ( i.created , 'yyyy-dd-mm' ), 'yyyy-dd-mm' ) >= 30 then

     dbms_output.put_line ( i.object_name || ' ---30 day--' );

     insert into drop_table_temp_log ( table_name , drop_time , state ) values ( i.object_name , sysdate , 'RES_1MON' );

 

     v_sql := 'drop table ' || i.object_name ;

     execute immediate v_sql ;

     commit ;

    ELSIF i.object_name like 'RES_3MON_%' and

          to_date ( to_char ( sysdate , 'yyyy-dd-mm' ), 'yyyy-dd-mm' ) -

          to_date ( to_char ( i.created , 'yyyy-dd-mm' ), 'yyyy-dd-mm' ) >= 60 then

       dbms_output.put_line ( i.object_name || ' ---90 day--' );

       insert into drop_table_temp_log ( table_name , drop_time , state ) values ( i.object_name , sysdate , 'RES_3MON' );

       v_sql := 'drop table ' || i.object_name ;

       execute immediate v_sql ;

       commit ;

    ELSIF i.object_name like 'RES_6MON_%' and

          to_date ( to_char ( sysdate , 'yyyy-dd-mm' ), 'yyyy-dd-mm' ) -

          to_date ( to_char ( i.created , 'yyyy-dd-mm' ), 'yyyy-dd-mm' ) >= 180 then

      dbms_output.put_line ( i.object_name || ' ----180 day--' );

      insert into drop_table_temp_log ( table_name , drop_time , state ) values ( i.object_name , sysdate , 'RES_6MON' );

      v_sql := 'drop table ' || i.object_name ;

      execute immediate v_sql ;

      commit ;

    end if ;

  end loop ;

    for j in object_other loop

       dbms_output.put_line ( j.table_name || '' );

       insert into drop_table_temp_log ( table_name , drop_time , state ) values ( j.table_name , sysdate , 'OTHER' );

       v_sql := 'drop table ' || j.table_name ;

       execute immediate v_sql ;

       commit ;

      end loop ;

end ;

 

附件：