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: 10.2.0.4
This problem can occur on any platform.

Symptoms

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;

CONN / AS SYSDBA
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';

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


 

Cause

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