第一章 目的
为了优化数据库的设计，提高数据库设计的合理性和数据访问高效性，同时便于数据共享的质量和效率，促进数据库的统一标准化管理，特制定一套创建临时表存放临时数据使用的设计规范。
第二章 设计规范
本次设计需要将所有的临时表存放在一个单独的数据库用户中，我们采用 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 ;
附件：
