使用临时表的一个问题-CSDN博客

为监控数据库中的无效对象，创建以下存储过程：

create or replace procedure db_mon_invalid_ojb as

v_sql_role varchar2(2000);

v_sql_ins_num varchar2(2000);

v_stat_inval varchar2(200);

v_stat_inval2 varchar2(200);

v_db_role varchar2(20);

n_ins_num number;

v_invalid_count number;

begin

--查询数据库角色

v_sql_role:='select database_role from v$database@';

--查询实例节点

v_sql_ins_num:='select instance_number from v$instance@';

--查询无效对象

v_stat_inval:='select count(1) from dba_objects@';

v_stat_inval2:='';

--1.列出当前所有dblink中database_roles是primary且instance_number=1的名称

for arr_dblink in (select db_link from dba_db_links) loop

begin

execute immediate v_sql_role ||arr_dblink.db_link into v_db_role;

execute immediate v_sql_ins_num||arr_dblink.db_link into n_ins_num;

if v_db_role='PRIMARY' and n_ins_num=1 then

--2.查出这些数据库中的无效对象，放入临时表

execute immediate v_stat_inval||arr_dblink.db_link||v_stat_inval2 into v_invalid_count;

insert into tmp_log_invalid_obj_count(db_name,invalid_count,update_date)

values (arr_dblink.db_link,v_invalid_count,sysdate);

commit;

end if;

exception

when others then

dbms_output.put_line(arr_dblink.db_link||':'||SQLCODE || '_' || SQLERRM);

end;

end loop;

--3.将查出结果和日志表中历史结果表中最近日期的无效对象数做比较，

--如果不一致，则将新产生的结果记录到表中，并发送邮件

for diff_inval_count in (

select b.db_name, b.invalid_count

from tmp_log_invalid_obj_count b

minus

select db_name, invalid_count

from (select l.db_name,

l.invalid_count,

rank() over(partition by db_name order by l.update_date) ranks

from log_invalid_obj_count l) a

where ranks = 1

) loop

insert into log_invalid_obj_count (db_name,invalid_count,update_date)

values (diff_inval_count.db_name,diff_inval_count.invalid_count,sysdate);

commit;

end loop;

end;

对应的表：

create global temporary table TMP_LOG_INVALID_OBJ_COUNT

(

DB_NAME VARCHAR2(30),

INVALID_COUNT NUMBER,

UPDATE_DATE DATE

)

on commit preserve rows;

create table LOG_INVALID_OBJ_COUNT

(

DB_NAME VARCHAR2(30),

INVALID_COUNT NUMBER,

UPDATE_DATE DATE

);

接着，在pl/sql的命令行窗口中进行测试：

SQL> exec db_mon_invalid_ojb;

PL/SQL procedure successfully completed

SQL> select * from LOG_INVALID_OBJ_COUNT where db_name like 'C1%';

DB_NAME INVALID_COUNT UPDATE_DATE

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

MYDB1402 88597 2015-12-14

MYDB1403 87297 2015-12-14

MYDB1405 89576 2015-12-14

MYDB0003 50472 2015-12-14

MYDB1310 87220 2015-12-14

MYDB1312 88863 2015-12-14

6 rows selected

这时，发现上面的存储过程统计的是所有对象的数量，修改：

将v_stat_inval2:='';

替换为v_stat_inval2:=' where status=''INVALID''';

清空结果表，再次执行存储过程：

SQL> delete from log_invalid_obj_count;

34 rows deleted

SQL> commit;

Commit complete

SQL> exec db_mon_invalid_ojb;

PL/SQL procedure successfully completed

SQL> select * from LOG_INVALID_OBJ_COUNT where db_name like 'C1%';

DB_NAME INVALID_COUNT UPDATE_DATE

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

MYDB1312 0 2015-12-14

MYDB1312 88863 2015-12-14

MYDB1402 0 2015-12-14

MYDB1402 88597 2015-12-14

MYDB1403 3 2015-12-14

MYDB1403 87297 2015-12-14

MYDB1405 1 2015-12-14

MYDB1405 89576 2015-12-14

MYDB0003 1 2015-12-14

MYDB0003 50472 2015-12-14

MYDB1310 0 2015-12-14

MYDB1310 87220 2015-12-14

12 rows selected

以前的结果还在，肯定是临时表的问题。

这时试图修改临时表的结构或者直接删除，都报错了：

SQL> alter table TMP_LOG_INVALID_OBJ_COUNT modify DB_NAME varchar2(20);

alter table TMP_LOG_INVALID_OBJ_COUNT modify DB_NAME varchar2(20)

ORA-14450: attempt to access a transactional temp table already in use

SQL> drop table tmp_log_invalid_obj_count;

drop table tmp_log_invalid_obj_count

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

可以看到，有一个会话正在使用该临时表：

SQL> select * from v$locked_object;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE

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

7 29 1533677 54112 525 SYSMAN oracle 1234 3

以上操作均在同一个窗口中执行。

回想以上异常结果，联系临时表的特点，发现原因：

由于存储过程中间有提交操作，不适合基于事务的临时表，因此使用的是会话级的临时表，也就是说在同一个会话中该表不会自动清空。

查看该临时表，确认了上面的分析：

SQL> select * from TMP_LOG_INVALID_OBJ_COUNT where db_name like 'C1%';

DB_NAME INVALID_COUNT UPDATE_DATE

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

MYDB1402 88597 2015-12-14

MYDB1312 88863 2015-12-14

MYDB0003 50472 2015-12-14

MYDB1405 89576 2015-12-14

MYDB1403 87297 2015-12-14

MYDB1310 87220 2015-12-14

MYDB1402 0 2015-12-14

MYDB1312 0 2015-12-14

MYDB0003 1 2015-12-14

MYDB1405 1 2015-12-14

MYDB1403 3 2015-12-14

MYDB1310 0 2015-12-14

MYDB1402 0 2015-12-14

MYDB1312 0 2015-12-14

MYDB0003 1 2015-12-14

MYDB1405 1 2015-12-14

MYDB1403 3 2015-12-14

MYDB1310 0 2015-12-14

18 rows selected

以上存储过程中使用会话级临时表是没问题的，但测试过程中一般会在同一个会话中进行大量操作，就不适合使用了。

这大概就是在BI库中用于报表的存储过程均不使用临时表的原因吧，虽然使用普通表前需要每次删除旧数据，但总比产生上面这种歧义强。

另外，当对临时表进行DDL操作报错时，关闭所有调用的会话，特别注意当前会话是否在使用该临时表。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1871926/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1871926/