index$_join$ or Other Internal Table Appears in Audit Trail Instead of Base Table.

(Doc ID 955968.1)

Last updated on OCTOBER 21, 2009

Applies to:

Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.


When checking the audit trail (DBA_AUDIT_TRAIL or AUD$), the object name on which the statement is supposedly run is index$_join$_xxx or similar temporary objects (eg: from$_subquery$) instead of the actual object.
Reproducible testcase:

conn / as sysdba
truncate table aud$;

drop user test cascade;

create user test identified by test;
grant connect,resource to test;
audit select table by session;

connect test/test

create table test.emp
(empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno);

create index emp_mgr_idx on emp (mgr);
create index emp_dptno_idx on emp (deptno);

connect test/test

select /*+ index_join(E EMP_MGR_IDX EMP_DPTNO_IDX) */ DEPTNO
from test.EMP E
where MGR < 110
and DEPTNO < 50;

set linesize 150
set pagesize 300
column owner format a10
column username format a10
column obj_name format a20
column sql_text format a70
select username,owner,obj_name,sql_text from dba_audit_trail where username='TEST';

-------- ------ ---------------  -------------------------------------------
TEST     TEST   index$_join$_001 SELECT /*+ INDEX_JOIN 
                                 (e emp_mgr_idx emp_dptno_idx) */ deptno 
                                 TEST.emp e 
                                 mgr < 110 
                                 deptno < 50



Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms