Thursday, December 18, 2014

SUCCESS and FAILURE Columns in DBA_STMT_AUDIT_OPTS

I tested this in Oracle 10.2. DBA_STMT_AUDIT_OPTS records auditing options which are currently in effect. If no auditing has been requested, it will be empty:
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>
 
You can audit successful connections as follows:
 
SQL> audit create session by system whenever successful
  2  /
 
Audit succeeded.
 
SQL>
 
This will then be recorded in DBA_STMT_AUDIT_OPTS. The SUCCESS column will be set to BY ACCESS to show that only successful connections are being audited:   
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
USER_NAME  AUDIT_OPTION    SUCCESS    FAILURE
---------- --------------- ---------- ----------
SYSTEM     CREATE SESSION  BY ACCESS  NOT SET
 
SQL>
 
You can stop the auditing like this:
 
SQL> noaudit create session by system whenever successful
  2  /
 
Noaudit succeeded.
 
SQL>
 
…and the entry will disappear from DBA_STMT_AUDIT_OPTS:
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>
 
Here is how you audit unsuccessful connections. Note that in the context of the AUDIT and NOAUDIT commands, CREATE SESSION and CONNECT have the same meaning:
 
SQL> audit connect by system whenever not successful
  2  /
 
Audit succeeded.
 
SQL>
 
This will also be recorded in DBA_STMT_AUDIT_OPTS but the FAILURE column will be set to BY ACCESS to show that only failed connections are being audited:
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
USER_NAME  AUDIT_OPTION    SUCCESS    FAILURE
---------- --------------- ---------- ----------
SYSTEM     CREATE SESSION  NOT SET    BY ACCESS
 
SQL>
 
As before, stopping the auditing removes the entry from DBA_STMT_AUDIT_OPTS:
 
SQL> noaudit connect by system whenever not successful
  2  /
 
Noaudit succeeded.
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>

Saturday, December 06, 2014

Segment Creation Deferred and ORA-02266

If you try to truncate a table with a primary key which is referenced by an enabled foreign key, you usually get an ORA-02266 error. This happens straight away if the table is set up with segment creation immediate. However, if the table is set up with segment creation deferred, the error is not reported until the segment has been created. You can see what I mean in the example below, which I tested on Oracle 11.2:
 
SQL> create table dept1
  2  (dept_code varchar2(4),
  3   dept_desc varchar2(20),
  4   constraint dept1_constraint
  5   primary key(dept_code))
  6   segment creation deferred
  7  /
 
Table created.
 
SQL> create table emp1
  2  (empno varchar2(6),
  3   dept_code varchar2(4),
  4   constraint emp1_constraint
  5   foreign key (dept_code)
  6   references dept1(dept_code))
  7  /
 
Table created.
 
SQL> select count(*) from dba_segments
  2  where segment_name = 'DEPT1'
  3  /
 
  COUNT(*)
----------
         0
 
SQL> truncate table dept1
  2  /
 
Table truncated.
 
SQL> insert into dept1 values('0001','SALES')
  2  /
 
1 row created.
 
SQL> select count(*) from dba_segments
  2  where segment_name = 'DEPT1'
  3  /
 
  COUNT(*)
----------
         1
 
SQL> truncate table dept1
  2  /
truncate table dept1
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by
enabled foreign keys
 
SQL> create table dept2
  2  (dept_code varchar2(4),
  3   dept_desc varchar2(20),
  4   constraint dept2_constraint
  5   primary key(dept_code))
  6   segment creation immediate
  7  /
 
Table created.
 
SQL> create table emp2
  2  (empno varchar2(6),
  3   dept_code varchar2(4),
  4   constraint emp2_constraint
  5   foreign key (dept_code)
  6   references dept2(dept_code))
  7  /
 
Table created.
 
SQL> truncate table dept2
  2  /
truncate table dept2
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by
enabled foreign keys
 
SQL>