Oracle 11g – How to Know Insert Record Details Which Failed to Insert

auditoracleoracle-11g

I have started auditing insert records by user on failure to any table in my oracle 11g Database. I have used following command to do the same.

AUDIT INSERT ANY TABLE BY SHENA BY ACCESS WHENEVER NOT SUCCESSFUL;

I would like to know whenever the record insert will fail, Can i know what was the records which failed to insert into table.

Where we can see such information. Or if you know any other way of auditing of the same please suggest. One way which i know is to write a trigger on insert. In that trigger handle insert failure EXCEPTION and save those values to some table.

Best Answer

Use SQL Loader Utility with following control file format.

options(skip=1,rows=65534,errors=65534,readsize=16777216,bindsize=16777216)
load data
infile 'c:\users\shena\desktop\1.txt'
badfile 'C:\Users\shena\Desktop\test.bad'
discardfile 'C:\Users\shena\Desktop\test.dsc'
log 'C:\Users\shena\Desktop\test.log'
append
into table ma_basic_bd
fields terminated by '|' optionally enclosed by '"' trailing nullcols
(fs_perm_sec_id, 
"DATE" "to_date(:DATE,'YYYY-MM-DD')", 
adjdate "to_date(:adjdate,'YYYY-MM-DD')", 
currency, 
p_price,  
p_price_open, 
p_price_high, 
p_price_low, 
p_volume)

You are requested to use the conventional path loading so that we can get the rejected(rejected because of datatype mismatch and business rule violation) records in .bad file. Conventional path loading is a default option.

Following URL can be used for the detailed knowledge.

https://youtu.be/eovTBGAc2RI

Total 4 videos are there. Very helpful.

Related Solutions

Can this be accomplished with Oracles Fine Grained Auditing

Get the client to call dbms_session.set_identifier('PHILTEST');. This will then be set in the CLIENT_ID audit field. Obviously you'l need to call it first from the client whenever a new connection is pulled from the pool.

For example:

PHIL@PHILL11G2 > conn / as sysdba
Connected.
Loading glogin.sql

Session altered.

SYS@PHILL11G2 AS SYSDBA> audit all by phil by access;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ACCESS;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> truncate table aud$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> TRUNCATE TABLE fga_log$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> conn phil/phil

PHIL@PHILL11G2 > exec dbms_session.set_identifier('PHILTEST');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > select count(*) from fgatest;

  COUNT(*)
----------
     1

PHIL@PHILL11G2 > conn / as sysdba

SYS@PHILL11G2 AS SYSDBA> select CLIENT_ID from dba_audit_trail;

CLIENT_ID
----------------------------------------------------------------
PHILTEST
PHILTEST

2 rows selected.

SYS@PHILL11G2 AS SYSDBA>

Accessing the insert statement which triggers a trigger inside the trigger’s body

You can meet all your requirements with Standard Database Auditing or with Fine-Grained Auditing.

For the standard auditing to also capture the SQL statements, you would set AUDIT_TRAIL initialization parameter to DB,EXTENDED or XML,EXTENDED (See "Settings for the AUDIT_TRAIL Initialization Parameter" in Database Security Guide). The audit records would include timestamps and SQL text, and could be queried from SYS.AUD$ table if AUDIT_TRAIL value is set to DB,EXTENDED or DB, or read from OS files if AUDIT_TRAIL is set to XML,EXTENDED or XML. In the latter case you can determine where the audit files are located based on the value of the initialization parameter AUDIT_FILE_DEST:

SQL> show parameter audit

NAME                 TYPE        VALUE
-------------------- ----------- ------------------------------
audit_file_dest      string      /u01/app/oracle/admin/orcl/adump

As far as FGA is concerned, I have already described it in my other post here. You would just need to set the other value for the audit_trail parameter when creating the FGA with DBMS_FGA.ADD_POLICY procedure, e. g. DBMS_FGA.DB_EXTENDED.

Best Answer

Related Solutions

Can this be accomplished with Oracles Fine Grained Auditing

Accessing the insert statement which triggers a trigger inside the trigger’s body

Related Question