今天接到客戶自己的測試環境，從源資料庫導緻一個表到目标資料庫，select * from tabname報ORA-01435: user does not exist，select count(*) from tabname正常，原來隻了解細粒度審計，但是在環境中還沒有真真的見過，是以整個過程分析用了一點時間。
下面是整個過程：
scott@EM10G> select count(*) from EMPLOYEES;
COUNT(*)
----------
107
scott@EM10G> select * from EMPLOYEES;
select * from EMPLOYEES
*
ERROR at line 1:
ORA-01435: user does not exist
下面是10046的過程
[oracle@RAC2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 22 17:03:50 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@EM10G> select spid from v$process where addr=(select paddr from v$session where username='SCOTT');
SPID
------------
30255
sys@EM10G> oradebug setospid 30255
Oracle pid: 22, Unix process pid: 30255, image: oracle@RAC2 (TNS V1-V3)
sys@EM10G> oradebug event 10046 trace name context forever ,level 12;
Statement processed.
sys@EM10G> oradebug tracefile_name
/u01/app/oracle/admin/em10g/udump/em10g_ora_30255.trc
sys@EM10G> !vi /u01/app/oracle/admin/em10g/udump/em10g_ora_30255.trc
Dump file /u01/app/oracle/admin/em10g/udump/em10g_ora_30255.trc
ORACLE_HOME = /u01/app/oracle
System name: Linux
Node name: RAC2
Release: 2.6.9-89.0.0.0.1.ELsmp
Version: #1 SMP Tue May 19 04:23:49 EDT 2009
Machine: i686
Instance name: em10g
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 30255, image: oracle@RAC2 (TNS V1-V3)
*** ACTION NAME:() 2013-03-22 17:02:24.565
*** MODULE NAME:(SQL*Plus) 2013-03-22 17:02:24.565
*** SERVICE NAME:(SYS$USERS) 2013-03-22 17:02:24.565
*** SESSION ID:(312.17658) 2013-03-22 17:02:24.565
-----------------------------------
Error during execution of handler in Fine Grained Auditing
Audit handler : begin SEC.LOG_EMPS_SALARY(:sn, :on, :pl); end;
Error Number 1 : 1435
Logon user : SCOTT
Object Schema: SCOTT, Object Name: EMPLOYEES, Policy Name: AUDIT_EMPS_SALARY
*** 2013-03-22 17:04:36.852
Received ORADEBUG command 'event 10046 trace name context forever ,level 12' from process Unix process pid: 30298, image:
WAIT #0: nam='SQL*Net message from client' ela= 110477763 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1331975666205121
=====================
PARSING IN CURSOR #1 len=23 dep=0 uid=27 oct=3 lid=27 tim=1331975666206838 hv=1609818433 ad='58ce67f0'
END OF STMT
PARSE #1:c=1000,e=1593,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1331975666206830
BINDS #1:
PARSING IN CURSOR #2 len=382 dep=1 uid=0 oct=2 lid=0 tim=1331975666208989 hv=1818756823 ad='58f2cf18'
insert into sys.fga_log$ (sessionid, ntimestamp#, dbuid, osuid, obj$schema, obj$name, policyname, scn, oshst, clientid, extid, lsqltext, proxy$sid,user$guid, instance#, process#, xid, statement, entryid, stmt_type, lsqlbind, auditid) values( :1, SYS_EXTRACT_UTC(SYSTIMESTAMP), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21 )
PARSE #2:c=1000,e=975,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1331975666208982
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72b8a94 bln=22 avl=04 flg=05
value=273276
Bind#1
oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=01 siz=32 off=0
kxsbbbfp=bfffb9de bln=32 avl=05 flg=09
value="SCOTT"
Bind#2
oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=11 fl2=0001 frm=01 csi=01 siz=32 off=0
kxsbbbfp=bfffba02 bln=32 avl=06 flg=09
value="oracle"
Bind#3
kxsbbbfp=bfffba22 bln=32 avl=05 flg=09
Bind#4
oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
kxsbbbfp=bfffba42 bln=32 avl=09 flg=09
value="EMPLOYEES"
Bind#5
oacdty=01 mxl=32(17) mxlc=00 mal=00 scl=00 pre=00
kxsbbbfp=bfffba62 bln=32 avl=17 flg=09
value="AUDIT_EMPS_SALARY"
Bind#6
oacdty=02 mxl=22(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=bfffba80 bln=22 avl=05 flg=09
value=76930924
Bind#7
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
kxsbbbfp=bfffba9e bln=32 avl=04 flg=09
value="RAC2"
Bind#8
oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#9
kxsbbbfp=bfffbb68 bln=32 avl=06 flg=09
Bind#10
oacdty=112 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=0001 frm=01 csi=00 siz=4000 off=0
kxsbbbfp=b72b7700 bln=4000 avl=4000 flg=05
value=
Dump of memory from 0xB72B7700 to 0xB72B86A0
B72B7700 01002600 03000802 00000100 013844FA [.&...........D8.]
B72B7710 02000000 16000100 01000000 00000000 [................]
B72B7720 D36EF96C 4EE43585 00000000 00000000 [l.n..5.N........]
B72B7730 00000000 00000000 00000000 00000000 [................]
Repeat 246 times
Bind#11
oacflg=01 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72b8a70 bln=22 avl=00 flg=05
Bind#12
Bind#13
kxsbbbfp=b72b8a4c bln=22 avl=01 flg=05
value=0
Bind#14
kxsbbbfp=bfffbf94 bln=32 avl=05 flg=09
value="30255"
Bind#15
oacdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=11 fl2=0001 frm=00 csi=00 siz=32 off=0
Bind#16
oacflg=00 fl2=0001 frm=00 csi=00 siz=72 off=0
kxsbbbfp=b72b89f8 bln=22 avl=02 flg=05
value=9
Bind#17
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=b72b8a10 bln=22 avl=02 flg=01
value=2
Bind#18
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=b72b8a28 bln=22 avl=02 flg=01
value=1
Bind#19
kxsbbbfp=b72b6740 bln=4000 avl=00 flg=05
Bind#20
Begin cleaning out block ...
Found all committed transactions
Block cleanout record, scn: 0xffff.ffffffff ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0495ddcb
itli: 2 flg: 2 scn: 0x0000.0495df3e
Block cleanout under the cache...
Block cleanout record, scn: 0x0000.0495df6c ver: 0x01 opt: 0x02, entries follow...
... clean out dump complete.
EXEC #2:c=2000,e=1974,p=0,cr=2,cu=2,mis=1,r=1,dep=1,og=4,tim=1331975666211048
WAIT #1: nam='log file sync' ela= 30974 buffer#=1013 p2=0 p3=0 obj#=-1 tim=1331975666243042
WAIT #1: nam='SQL*Net break/reset to client' ela= 4 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1331975666244102
WAIT #1: nam='SQL*Net break/reset to client' ela= 85 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1331975666244210
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1331975666244236
WAIT #1: nam='SQL*Net message from client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1331975666244269
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=21358 op='TABLE ACCESS FULL EMPLOYEES (cr=0 pr=0 pw=0 time=784 us)'
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1331975666245227
*** 2013-03-22 17:04:48.216
Received ORADEBUG command 'tracefile_name' from process Unix process pid: 30298, image:
已經看到報錯資訊了，打開了Grained Auditing的原因。
scott@EM10G> select * from DBA_AUDIT_POLICIES;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME
------------------------------ ------------------------------ ------------------------------
POLICY_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
POLICY_COLUMN PF_SCHEMA PF_PACKAGE PF_FUNCTION ENA SEL
------------------------------ ------------------------------ ------------------------------ ------------------------------ --- ---
INS UPD DEL AUDIT_TRAIL POLICY_COLU
--- --- --- ------------ -----------
SCOTT EMPLOYEES AUDIT_EMPS_SALARY
SALARY SEC LOG_EMPS_SALARY YES YES
NO NO NO DB+EXTENDED ANY_COLUMNS
HR EMPLOYEES AUDIT_EMPS_SALARY
由于是客戶自己的測試環境，是以直接drop
scott@EM10G> BEGIN
2 dbms_fga.drop_policy( object_schema => 'scott',
3 object_name => 'EMPLOYEES',
4 policy_name => 'AUDIT_EMPS_SALARY');
5 END;
6 /
PL/SQL procedure successfully completed.
可以看到問題已經解決
scott@EM10G> conn scott/tiger;
Connected.
scott@EM10G> select * from EMPLOYEES ;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ----------
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
198 Donald OConnell DOCONNEL 650.507.9833 21-JUN-99 SH_CLERK
2600 124 50
199 Douglas Grant DGRANT 650.507.9844 13-JAN-00 SH_CLERK
已經正常
本文轉自7343696 51CTO部落格，原文連結：http://blog.51cto.com/luoping/1161891，如需轉載請自行聯系原作者
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)