Home » Other » General » How to view current date audit records from DBA_AUDIT_TRAIL (oracle 11gr2 linux 6.3)
How to view current date audit records from DBA_AUDIT_TRAIL [message #611222] |
Sun, 30 March 2014 21:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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;
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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;
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ?
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:29:35 CDT 2024
|