OraFAQ Forum: General » How to view current date audit records from DBA_AUDIT

Home » Other » General » How to view current date audit records from DBA_AUDIT_TRAIL (oracle 11gr2 linux 6.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to view current date audit records from DBA_AUDIT_TRAIL [message #611222]

Sun, 30 March 2014 21:20

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Team,

How to view current date audit records from DBA_AUDIT_TRAIL ?

21:51:32 SQL> select to_char(sysdate,'Day dd-mon-yyyy:hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DAYDD-MON-YYYY:HH24:MI:SS')
------------------------------------------------------------------
Monday    31-mar-2014:01:51:41



SQL> set lines 1000
SQL> col username for a20
SQL> col EXTENDED_TIMESTAMP for a35
SQL> col sql_text for a40
SQL> select username,EXTENDED_TIMESTAMP,sql_text from dba_audit_trail order by EXTENDED_TIMESTAMP desc;

USERNAME             EXTENDED_TIMESTAMP                  SQL_TEXT
-------------------- ----------------------------------- ----------------------------------------
TESTUSER1         30-MAR-14 09.34.26.032901 PM -04:00
TESTUSER2         30-MAR-14 09.34.00.095528 PM -04:00
TESTUSER2         30-MAR-14 09.34.00.091115 PM -04:00
TESTUSER1         30-MAR-14 09.33.51.266063 PM -04:00
TESTUSER2         30-MAR-14 09.33.00.106877 PM -04:00
TESTUSER2         30-MAR-14 09.33.00.078289 PM -04:00
TESTUSER2         30-MAR-14 09.32.00.110269 PM -04:00
TESTUSER2         30-MAR-14 09.32.00.081766 PM -04:00
TESTUSER1         30-MAR-14 09.31.45.244947 PM -04:00 create user t010 identified by *
TESTUSER1         30-MAR-14 09.31.45.191724 PM -04:00 create user t009 identified by *
TESTUSER1         30-MAR-14 09.31.45.087375 PM -04:00 create user t008 identified by *

USERNAME             EXTENDED_TIMESTAMP                  SQL_TEXT
-------------------- ----------------------------------- ----------------------------------------
TESTUSER1         30-MAR-14 09.31.43.017216 PM -04:00 create user t007 identified by *
TESTUSER1         30-MAR-14 09.31.42.954545 PM -04:00 create user t006 identified by *
TESTUSER1         30-MAR-14 09.31.42.902938 PM -04:00 create user t005 identified by *
TESTUSER1         30-MAR-14 09.31.42.838710 PM -04:00 create user t004 identified by *
TESTUSER1         30-MAR-14 09.31.42.783759 PM -04:00 create user t003 identified by *
TESTUSER1         30-MAR-14 09.31.42.724429 PM -04:00 create user t002 identified by *
TESTUSER1         30-MAR-14 09.31.42.644759 PM -04:00 create user t001 identified by *
TESTUSER1         30-MAR-14 09.31.38.486043 PM -04:00
TESTUSER2         30-MAR-14 09.31.00.320498 PM -04:00
TESTUSER2         30-MAR-14 09.31.00.254226 PM -04:00
TESTUSER2         30-MAR-14 09.30.44.208226 PM -04:00

i just want to query only records done on 30-MAR-14 using EXTENDED_TIMESTAMP column.
I am using below query. but not able to get output. could you please guide me to get only rows belongs to 30-MAR-14 ?

select 	D.NAME "DBMS",
	I.HOST_NAME "Host Name",
	A.OS_USERNAME "OS UserID", 								
	A.USERHOST "User Machine",
 	A.TIMESTAMP "Local Time",							
	A.EXTENDED_TIMESTAMP "Time(G)",								
	A.ACTION_NAME "Action Type",
	A.PRIV_USED "Priv Used",	
	A.USERNAME "DB UserID",
	A.OWNER "Obj Owner",
	A.OBJ_NAME "Obj Name",								
        A.SQL_TEXT "SQL Text",
	A.RETURNCODE	"Err code"
from DBA_AUDIT_TRAIL A, V$DATABASE D, V$INSTANCE I
where  A.DBID = D.DBID
and    D.NAME = I.INSTANCE_NAME
and  to_char(A.EXTENDED_TIMESTAMP,'dd-mon-yyyy') = to_char(sysdate,'dd-mon-yyyy')
order by A.EXTENDED_TIMESTAMP;

OUTPUT IS NO ROWS SELECTED.

please guide me

Thank you

[Updated on: Sun, 30 March 2014 21:24]

Report message to a moderator

Re: How to view current date audit records from DBA_AUDIT_TRAIL [message #611223 is a reply to message #611222]

Sun, 30 March 2014 22:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

select 	D.NAME "DBMS",
	I.HOST_NAME "Host Name",
	A.OS_USERNAME "OS UserID", 								
	A.USERHOST "User Machine",
 	A.TIMESTAMP "Local Time",							
	A.EXTENDED_TIMESTAMP "Time(G)",								
	A.ACTION_NAME "Action Type",
	A.PRIV_USED "Priv Used",	
	A.USERNAME "DB UserID",
	A.OWNER "Obj Owner",
	A.OBJ_NAME "Obj Name",								
        A.SQL_TEXT "SQL Text",
	A.RETURNCODE	"Err code"
from DBA_AUDIT_TRAIL A, V$DATABASE D, V$INSTANCE I
where  A.DBID = D.DBID
and    D.NAME = I.INSTANCE_NAME
and  to_char(A.EXTENDED_TIMESTAMP,'dd-mon-yyyy') = '30-MAR-2014'
order by A.EXTENDED_TIMESTAMP;

Report message to a moderator

Re: How to view current date audit records from DBA_AUDIT_TRAIL [message #611224 is a reply to message #611223]

Sun, 30 March 2014 22:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member


  1* select to_char(logged,'YYYY-MM-DD') from SYSMAN.MGMT_JOB_STATE_CHANGES
SQL> /

TO_CHAR(LO
----------
2013-07-11
2013-07-12
2013-07-12
2013-07-12
2013-07-12
2013-07-11
2013-07-12
2013-07-12
2013-07-11

9 rows selected.

SQL> ed
SQL> ed
Wrote file afiedt.buf

  1  select count(*)
  2  from SYSMAN.MGMT_JOB_STATE_CHANGES
  3* where to_char(logged,'YYYY-MM-DD') = '2013-07-12'
SQL> /

  COUNT(*)
----------
         6

SQL> 



select 	D.NAME "DBMS",
	I.HOST_NAME "Host Name",
	A.OS_USERNAME "OS UserID", 								
	A.USERHOST "User Machine",
 	A.TIMESTAMP "Local Time",							
	A.EXTENDED_TIMESTAMP "Time(G)",								
	A.ACTION_NAME "Action Type",
	A.PRIV_USED "Priv Used",	
	A.USERNAME "DB UserID",
	A.OWNER "Obj Owner",
	A.OBJ_NAME "Obj Name",								
        A.SQL_TEXT "SQL Text",
	A.RETURNCODE	"Err code"
from DBA_AUDIT_TRAIL A, V$DATABASE D, V$INSTANCE I
where  A.DBID = D.DBID
and    D.NAME = I.INSTANCE_NAME
and  to_char(A.EXTENDED_TIMESTAMP,'dd-mon-yyyy') = '30-MAR-2014'
order by A.EXTENDED_TIMESTAMP;

Report message to a moderator

Re: How to view current date audit records from DBA_AUDIT_TRAIL [message #611225 is a reply to message #611224]

Mon, 31 March 2014 00:08

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Thank you Team.

Actually this is amazon RDS Oracle instance. I need to call his .sql script (sql query from dba_audit_trail ) on daily basis. so that it will exeucte on daily basis and will generate only current date audit records into spool file.

i want to avoid hardcode 30-MAR-2014 in the query.

Could you please help me ?

Report message to a moderator

Re: How to view current date audit records from DBA_AUDIT_TRAIL [message #611227 is a reply to message #611225]

Mon, 31 March 2014 00:21

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

trunc(A.EXTENDED_TIMESTAMP) = trunc(sysdate)

Report message to a moderator

Re: How to view current date audit records from DBA_AUDIT_TRAIL [message #611228 is a reply to message #611227]

Mon, 31 March 2014 00:34

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Thank you Mike and every one who worked for me.

below works well for me now.

col "DBMS" for a15
col "Host Name" for a30
col "OS UserID" for a15
col "User Machine" for a18
col "Time(G)" for a36
col "Action Type" for a28
col "Priv Used" for a40
col "DB UserID" for a15
col "Obj Owner" for a15
col "Obj Name" for a30
col "SQL Text" for a200
col "Err code" for a10 
col "Local Time" for a36

set lines 1000

select 	D.NAME "DBMS",
	I.HOST_NAME "Host Name",
	A.OS_USERNAME "OS UserID", 								
	A.USERHOST "User Machine",
 	to_char(A.TIMESTAMP,'DD-MON-YY HH24:MI:SS') "Local Time",							
	A.EXTENDED_TIMESTAMP "Time(G)",								
	A.ACTION_NAME "Action Type",
	A.PRIV_USED "Priv Used",	
	A.USERNAME "DB UserID",
	A.OWNER "Obj Owner",
	A.OBJ_NAME "Obj Name",								
        A.SQL_TEXT "SQL Text",
	A.RETURNCODE	"Err code"
from DBA_AUDIT_TRAIL A, V$DATABASE D, V$INSTANCE I
where  A.DBID = D.DBID
and    D.NAME = I.INSTANCE_NAME
and  trunc(A.EXTENDED_TIMESTAMP) = trunc(sysdate)
order by A.EXTENDED_TIMESTAMP desc ;

Thank again

Report message to a moderator

Previous Topic:	import failed when processing synonyms
Next Topic:	Required suggestion on peculiar dimensional model (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 10:24:25 CST 2024