ITPub博客

首页 > 数据库 > Oracle > 使用临时表的一个问题

使用临时表的一个问题

原创 Oracle 作者:to_be_dba 时间:2015-12-14 14:27:36 0 删除 编辑

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

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.列出当前所有dblinkdatabase_rolesprimaryinstance_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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    391983