left join 临时表 让临时表走索引

问题描述

有一个将审计日志每日建立dblink导入日志库的shell作业执行失败了，但是手动执行时发生报错。

问题解决

1.ORA-00955

• 执行创建临时表语句

SQL> create global temporary table temp_aud on commit preserve rows as select * from sys.dba_audit_trail;

• 报错如下
  ORA-00955：名称已由现有对象使用

原因是有一个视图和该表重名了，解决方法是修改名称，或删掉重复的对象

• 查看重名的对象

SQL> select a.OBJECT_type,a.* from all_objects a where upper(a.OBJECT_NAME) ='TEMP_AUD';

注： 如果查询对象时没有找到，则在dba账户下执行下面的语句刷新一下共享池

SQL> alter system flush shared_pool;

未进行到此步，无法验证准确性。参考名称已由现有对象使用

• 删除掉这个对象

但是在drop该表时报错：

2.ORA-14452

ORA-14452: 试图创建, 变更或删除正在使用的临时表中的索引原因是temp_aud为临时表，而且有其它session正在使用，这张表被锁了。解决办法是kill掉正在使用该表的session。

• 查找还在使用临时表的会话：

SQL> select sid,serial# from v$session
where sid = (select sid from v$lock where id1 =
    (select object_id from user_objects where object_name=upper('temp_aud')));

但是可能因为有多个，所以无法直接查询

• 分步查询：

SQL> select object_id from user_objects where object_name=upper('temp_aud');
OBJECT_ID
----------
   4660432

SQL> select sid from v$lock where id1 =4660432;

       SID
----------
      2375
      2375

SQL> select sid,serial# from v$session
where sid = 2375;

       SID    SERIAL#
---------- ----------
      2375     4469

• 杀掉进程：
  alter system kill session 'sid,serial#'; 大概等了几十s后报错了：



• 
  

3.ORA-00031

ORA-00031: 标记要终止的会话

Oracle 官网对ORA 31解释如下

ORA-00031: session marked for kill
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot bekilled immediately (because it is rolling back or blocked on a networkoperation), but it has been marked for kill. This means it will be killed as soonas possible after its current uninterruptible operation is done.
Action: No action is required for the session to be killed, but further executionsof the ALTER SYSTEM KILL SESSION command on this session may cause the sessionto be killed sooner.

如果出现这个错误，则说明在数据库级不能杀掉该死锁进程，需要到操作系统级来处理了。
利用kill session命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么只能在OS级再杀死相应的进程（线程）

• 查出这个session的spid

SQL> select spid, osuser, s.program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = 2375; 

SPID             OSUSER			PROGRAM
------------------- ---------------- ------------------------------------------------
5041             oracle			sqlplus@rac1 (TNS V1-V3)

SQL> select a.spid,b.sid,b.serial#,b.username
from v$process a,v$session b
where a.addr=b.paddr and b.status='KILLED';  

SPID                SID    SERIAL# USERNAME
------------------------ ---------- ---------- ------------------------------
5041                   2375      4469 SYS

• 在数据库服务器上kill掉查出的系统进程,并用root用户下kill掉这个进程

[root@rac1 ~]# ps -ef |grep 5041
oracle     5041      1 10 13:47 ?        00:03:10 oracleracdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      82195  80832  0 14:19 pts/1    00:00:00 grep 5041
[root@rac1 ~]# kill -9 5041
[root@rac1 ~]# ps -ef |grep 5041
root      82936  80832  0 14:19 pts/1    00:00:00 grep 5041

此时在数据库中可以看到，v$session视图下状态为killed的进程已经没有了:

SQL> select b.owner,b.object_name,a.session_id,a.locked_mode from gv$locked_object a,dba_objects b where b.object_id = a.object_id;

未选定行

如果再次kill sission，则没有这个进程

SQL> alter system kill session '2375,4469';
alter system kill session '2375,4469'
*
第 1 行出现错误:
ORA-00030: 用户会话 ID 不存在。

• 删除该表

SQL> drop table temp_aud;

表已删除。