Home » RDBMS Server » Security » TIMESTAMP on FGA_AUDIT (Oracle 10g R2 XP)
TIMESTAMP on FGA_AUDIT [message #469177] Tue, 03 August 2010 11:34 Go to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Hi Oracle Experts,

We have the fga enabled in our database & our plan is to transfer the
log data into a local table which I can call FGA_LOCAL_TBL. The process
on transferring the data is via procedure with job for execution schedule.

Problem: Viewing the dba_fga_audit_trail on TIMESTAMP everything seems fine
but once inserted into the local table it change into wrong time.

Please help to figure out the solution.


Select TIMESTAMP, EXTENDED_TIMESTAMP from
dba_fga_audit_trail where username='GAN';

TIMESTAMP EXTENDED_TIMESTAMP
8/3/2010 9:51:55 AM 8/3/2010 9:51:55.054340 AM -06:00


This is how it look like in local table:

FGA_LOCAL_TBL
TIMESTAMP
2010-08-03 03:51:55


This is the select line on the procedure:

SELECT
TO_CHAR(DBA_FGA_AUDIT_TRAIL.TIMESTAMP,'YYYY-MM-DD HH12:MI:SS'),


Thank you,
GAN
Re: TIMESTAMP on FGA_AUDIT [message #469178 is a reply to message #469177] Tue, 03 August 2010 11:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
it appears TIMESTAMP is really a DATE datatype

SQL> desc dba_fga_audit_trail
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SESSION_ID				   NOT NULL NUMBER
 TIMESTAMP					    DATE
 DB_USER					    VARCHAR2(30)
 OS_USER					    VARCHAR2(255)
 USERHOST					    VARCHAR2(128)
 CLIENT_ID					    VARCHAR2(64)
 ECONTEXT_ID					    VARCHAR2(64)
 EXT_NAME					    VARCHAR2(4000)
 OBJECT_SCHEMA					    VARCHAR2(30)
 OBJECT_NAME					    VARCHAR2(128)
 POLICY_NAME					    VARCHAR2(30)
 SCN						    NUMBER
 SQL_TEXT					    NVARCHAR2(2000)
 SQL_BIND					    NVARCHAR2(2000)
 COMMENT$TEXT					    VARCHAR2(4000)
 STATEMENT_TYPE 				    VARCHAR2(7)
 EXTENDED_TIMESTAMP				    TIMESTAMP(6) WITH TIME ZONE
 PROXY_SESSIONID				    NUMBER
 GLOBAL_UID					    VARCHAR2(32)
 INSTANCE_NUMBER				    NUMBER
 OS_PROCESS					    VARCHAR2(16)
 TRANSACTIONID					    RAW(8)
 STATEMENTID					    NUMBER
 ENTRYID					    NUMBER
 OBJ_EDITION_NAME				    VARCHAR2(30)
 DBID						    NUMBER
Re: TIMESTAMP on FGA_AUDIT [message #469180 is a reply to message #469178] Tue, 03 August 2010 11:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the datatype of FGA_LOCAL_TBL.TIMESTAMP?
Re: TIMESTAMP on FGA_AUDIT [message #469181 is a reply to message #469178] Tue, 03 August 2010 11:53 Go to previous messageGo to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Hi BlackSwan,

Yes Timestamp in DBA_FGA_AUDIT_TRAIL is date datatype but in my local table I set it to varchar2 so I need to_char to convert it. Actually I set it that way to investigate the problem coz even my local table set to date datatype still the time is wrong. Do you think it something to do with timezone as you can see in the extended_timestamp it has -06:00 at the end part?

Thank you,
GAN
Re: TIMESTAMP on FGA_AUDIT [message #469182 is a reply to message #469181] Tue, 03 August 2010 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>SELECT TO_CHAR(DBA_FGA_AUDIT_TRAIL.TIMESTAMP,'YYYY-MM-DD HH12:MI:SS'),
What happens where time is after noon?
This format mask has no way to accurately represent times which occur after noon.
Re: TIMESTAMP on FGA_AUDIT [message #469184 is a reply to message #469182] Tue, 03 August 2010 12:08 Go to previous messageGo to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
I'm sorry for misleading you BlackSwan but I intent to do it HH24:mi:ss. I'm trying to do different combination but I guess focusing in the wrong area.

I ran this & I got:
select dbtimezone from dual;
-06:00

Not to much familiar with timezone but I'm thingking that it should be 00:00 so it's current?

Re: TIMESTAMP on FGA_AUDIT [message #469186 is a reply to message #469184] Tue, 03 August 2010 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Not to much familiar with timezone but I'm thingking that it should be 00:00 so it's current?

Why? Is your database in Greenwich/London/GB?

Regards
Michel
Re: TIMESTAMP on FGA_AUDIT [message #469187 is a reply to message #469186] Tue, 03 August 2010 12:57 Go to previous messageGo to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Hi Michel,

That's just a guess. My database is in Canada and with timezone MDT. As I query the timestamp from dba_fga_audit_trail everything seems fine but as I inserted the log data into local table I'm getting 6 hours over.

I'm trying another solution, to add 6 hours before insert but that won't clear the real problem though.

Thank you,
-GAN-
Re: TIMESTAMP on FGA_AUDIT [message #469188 is a reply to message #469187] Tue, 03 August 2010 13:00 Go to previous messageGo to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Correction on my last reply.
instead of adding 6 hours it should be less 6 hours before insert.

Thank you
Re: TIMESTAMP on FGA_AUDIT [message #469192 is a reply to message #469188] Tue, 03 August 2010 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Date/time is determined by 2 components: database time zone and session time zone.
So check both.

Regards
Michel
Re: TIMESTAMP on FGA_AUDIT [message #469195 is a reply to message #469177] Tue, 03 August 2010 14:37 Go to previous message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
I'm getting the right time now by applying below select statement from dba_fga_audit_trail. It's not the best solution but good enough to move forward in our project.

TO_CHAR(TIMESTAMP - INTERVAL '6' HOUR,'YYYY-MM-DD HH24:MI:SS')

Thank you all!

-GAN-
Previous Topic: virtual private database, Need Guidance
Next Topic: Network data Encryption between oracle client adn server on unix
Goto Forum:
  


Current Time: Thu Mar 28 15:14:35 CDT 2024