Accessing raw Audit Trail xml directly from database

Hi Everyone, 

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.