ORA-00604-ORA-20001处理排查

视图不能删除问题。检查报错信息后，初步判断可能是账户权限或触发器问题导致的此问题。

经过测试，可以发现使用SYS/SYSTEM账号可以正常创建删除；业务账号可以创建、不能删除，基本确定是有相关安全策略或触发器的影响。

但是到底哪些触发器影响呢，系统中有很多默认及应用的触发器，排查起来还有是难度的。

本次使用10046trace来跟踪删除语句，查找出对应的触发器SQL，应用厂商人员据此很快找到了对应的触发器。之后可以通过禁用触发器或使用高权限账户(system)来删除处理

过程如下：

1.排除权限问题
SYS用户验证“
SQL> create table TEST111.test20210303 as select * from dba_users;
Table created.
 
SQL> drop table TEST111.test20210303 ;
Table dropped.
 
业务用户验证：
SQL> show user
USER is "TEST111"
SQL> create table test20210303 as select * from dba_users;
Table created.
 
SQL> drop table test20210303 ;
drop table test20210303
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TEST111.TEST20210303 ?????drop??!
ORA-06512: at line 7
 
检查业务用户的权限：
SQL> select * from dba_role_privs where grantee='TEST111';
 
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TEST111                      RESOURCE                       NO  YES
TEST111                      DBA                            NO  YES
TEST111                      CONNECT                        NO  YES
2.10046跟踪分析的过程
1.查出进程的SID及OSPID
SQL> select sid from v$mystat where rownum=1;
       SID
----------
       786
SQL> select  b.spid,a.sid,a.username,a.program,a.machine,BLOCKING_SESSION 
  2  from v$session a,v$process b 
  3  where a.paddr=b.addr  and a.sid=786;
 
SPID                            SID USERNAME
------------------------ ---------- ------------------------------
PROGRAM
------------------------------------------------
MACHINE
----------------------------------------------------------------
BLOCKING_SESSION
----------------
24317                           786 TEST111
sqlplus@nf-dboracle (TNS V1-V3)
nf-dboracle
 
2.新开一个会话连接打开10046 TRACE
SQL> oradebug setospid 24317
Oracle pid: 92, Unix process pid: 24317, image: oracle@nf-dboracle (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
3.原进程执行删除语句
SQL> drop table test20210303 ;
drop table test20210303
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TEST111.TEST20210303 ?????drop??!
ORA-06512: at line 7
 
 
4.停止10046 TRACE
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orclpri/orcl/trace/orcl_ora_24317.trc
SQL> oradebug event 10046 trace name context off;
Statement processed.
 
5.格式化后检查分析10046 TRACE文件
********************************************************************************
********************************************************************************
 
SQL ID: asrh3b8ngw9zg Plan Hash: 0
 
drop table test20210303 
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 124  
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message from client                     1       35.97         35.97
  log file sync                                   1        0.00          0.00
  SQL*Net break/reset to client                   2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
…………………………之后有如下语句
DECLARE
  l_errmsg VARCHAR2(100) := '不允许进行drop操作!';
BEGIN
 
  IF ora_login_user NOT IN ('SYS','SYSTEM','DATA_MONITOR') THEN
 
    raise_application_error(-20001, ora_dict_obj_owner||'.'||ora_dict_obj_name||' '||l_errmsg);
 
  END IF;
 
END;