Home » SQL & PL/SQL » SQL & PL/SQL » timestamp = sysdate-1 (merged 2) (Oracle10g2)
timestamp = sysdate-1 (merged 2) [message #445774] Thu, 04 March 2010 01:32 Go to next message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
I am auditing DB users, due to some reasons my batch file didnt run successfull, now I want to check yesterday's (sysdate-1) or (03-MAR-2010) audit logs.

I have written below query but its not returning any rows, please do help me..
---------------------------------------------------------------------
select username, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM') time, owner, obj_name, ses_actions "---D--I--SU-----", os_username, userhost, sql_text, sql_bind
from DBA_audit_trail
where to_char(extended_timestamp, 'fmDd-MM-YYYY') = to_char(SYSDATE-1, 'fmDd-MM-YYYY')
and username = 'ERP'
order by time;

spool off;

EXIT;
---------------------------------------------------------------------------
select username, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM') time, owner, obj_name, os_username, userhost, sql_text, sql_bind
from DBA_audit_trail
where to_char(extended_timestamp, 'fmDd-MM-YYYY') = to_char('03-03-2010', 'fmDd-MM-YYYY')
and username = 'ERP'
order by time;

spool off;

EXIT;
---------------------------------------------------------------------------
Re: timestamp = sysdate-1 (merged 2) [message #445776 is a reply to message #445774] Thu, 04 March 2010 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

1/
trunc(tim) = trunc(sysdate)-1
other solution
tim >= trunc(sysdate)-1 and tim < trunc(sysdate)

2/
As '03-03-2010' is a string, TO_CHAR(<a string>) is meaningless.
trunc(tim) = to_date('03-03-2010', 'DD-MM-YYYY')
other solution
tim >= to_date('03-03-2010', 'DD-MM-YYYY') and tim < to_date('03-03-2010', 'DD-MM-YYYY')+1

Regards
Michel
Re: timestamp = sysdate-1 (merged 2) [message #445785 is a reply to message #445776] Thu, 04 March 2010 02:11 Go to previous messageGo to next message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
thanks alot, I have solved my problem....
Re: timestamp = sysdate-1 (merged 2) [message #445786 is a reply to message #445785] Thu, 04 March 2010 02:12 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So post it, and post as requested in forum guide.

Regards
Michel
Previous Topic: Generating dates
Next Topic: Execute Immediate Issue (merged 4)
Goto Forum:
  


Current Time: Tue Dec 06 12:25:02 CST 2016

Total time taken to generate the page: 0.11303 seconds