OraFAQ Forum: Security » TIMESTAMP on FGA

Home » RDBMS Server » Security » TIMESTAMP on FGA_AUDIT (Oracle 10g R2 XP)

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

TIMESTAMP on FGA_AUDIT [message #469177]

Tue, 03 August 2010 11:34

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

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469178 is a reply to message #469177]

Tue, 03 August 2010 11:42

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

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469180 is a reply to message #469178]

Tue, 03 August 2010 11:50

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

What's the datatype of FGA_LOCAL_TBL.TIMESTAMP?

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469181 is a reply to message #469178]

Tue, 03 August 2010 11:53

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

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469182 is a reply to message #469181]

Tue, 03 August 2010 12:00

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.

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469184 is a reply to message #469182]

Tue, 03 August 2010 12:08

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?

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469186 is a reply to message #469184]

Tue, 03 August 2010 12:48

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

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

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469187 is a reply to message #469186]

Tue, 03 August 2010 12:57

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-

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469188 is a reply to message #469187]

Tue, 03 August 2010 13:00

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

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469192 is a reply to message #469188]

Tue, 03 August 2010 13:41

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

Senior Member
Account Moderator

Date/time is determined by 2 components: database time zone and session time zone.
So check both.

Regards
Michel

Report message to a moderator

Re: TIMESTAMP on FGA_AUDIT [message #469195 is a reply to message #469177]

Tue, 03 August 2010 14:37

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-

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 06 17:23:55 CST 2025