If you need to access the audit trail directly from database then what you have to do is simple. First query the audit_trail table. You'll see several lines for a single cikey. Convert chunks to binary and concatenate all rows for a cikey but do this for each block separately. Finally uncompress it and what you'll get is a raw audit trail xml. You can convert it to table or just search a specific keyword or even you can code your own display engine if you don't like the oracle's em console.
For me this function is important because for an archived instance I don't have to move the instance to production in order to see the audit trail.
Function works for both 10g and 11g. I didn't have the chance to test 12c but if audit_trail is still there then I think it will work for 12c as well..
You will need blob_to_clob function. you can get it from here; http://www.dba-oracle.com/t_convert_blob_to_clob_script.htm
create or replace function get_audit_xml(vcikey in number) return clob is
tmpblob blob;
vblob blob;
audit_trail clob:='<audit_trail>';
begin
for b in (select distinct block from audit_trail where block=1 and cikey=vcikey order by 1)
loop
tmpblob:=to_blob('0');
vblob:=to_blob('0');
for x in (select row_number() over (order by count_id) row_num ,to_blob(log) chunk from audit_trail where cikey=vcikey and block=b.block)
loop
if(x.row_num=1) then
tmpblob:= x.chunk;
else
dbms_lob.append(dest_lob => tmpblob,src_lob => x.chunk);
end if;
end loop;
utl_compress.lz_uncompress(src => tmpblob, dst => vblob);
audit_trail:=audit_trail||blob_to_clob(vblob);
end loop;
return audit_trail||'</audit_trail>';
end get_audit_xml;
You can use this function in your posts or website but please give credit to my blog when doing this.
