DBATOOLS > cat audit_tablespace_view.sql
def fs=’&1′
–
– Need to pass the parameter of the file system to put the datafile
– Example would be @edba_audit_trail_report /u01
col db new_value v_db noprint
select name db
from v$database;
set markup html on spool on
spool audit_tablespace_&v_db..log
CREATE TABLESPACE SYSAUDIT_D DATAFILE
‘/&fs/oradata/&v_db/sysaudit_d_01.dbf’ SIZE 200m AUTOEXTEND OFF
LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
create table audx tablespace SYSAUDIT_D as select * from aud$ where 1 = 2;
rename AUD$ to AUD$$;
rename audx to aud$;
drop index i_aud1;
create index i_aud1 on aud$(sessionid, ses$tid) tablespace SYSAUDIT_D;
–
– And create a view that is worth something
–
CREATE OR REPLACE VIEW sys.edba_audit_trail_report (
os_username,
username,
terminal,
timestamp,
owner,
obj_name,
ses_actions,
alt,
aud,
com,
del,
gra,
ind,
ins,
loc,
ren,
sel,
upd,
ref,
exe,
Action )
AS
select /*+ rule */
spare1 /* OS_USERNAME */,
userid /* USERNAME */,
terminal /* TERMINAL */,
timestamp# /* TIMESTAMP */,
obj$creator /* OWNER */,
obj$name /* OBJECT_NAME */,
ses$actions /* SES_ACTIONS */,
substr(ses$actions,1,1),
substr(ses$actions,2,1),
substr(ses$actions,3,1),
substr(ses$actions,4,1),
substr(ses$actions,5,1),
substr(ses$actions,6,1),
substr(ses$actions,7,1),
substr(ses$actions,8,1),
substr(ses$actions,9,1),
substr(ses$actions,10,1),
substr(ses$actions,11,1),
substr(ses$actions,12,1),
substr(ses$actions,13,1),
act.name
from sys.aud$ aud,
system_privilege_map spm,
system_privilege_map spx,
STMT_AUDIT_OPTION_MAP aom,
audit_actions act
where aud.action# = act.action (+)
and – aud.logoff$dead = spm.privilege (+)
and aud.logoff$dead = aom.option# (+)
and – aud.priv$used = spx.privilege (+)
order by timestamp# desc,userid
/
grant select on sys.edba_audit_trail_report to rodba;
spool off