为监控数据库中的无效对象，创建以下存储过程：
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博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-1871926/ ，如需转载，请注明出处，否则将追究法律责任。
