OraFAQ Forum: Server Administration » view table change date

Home » RDBMS Server » Server Administration » view table change date

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

view table change date [message #228355]

Mon, 02 April 2007 12:23

dazormiq
Messages: 4
Registered: August 2006

Junior Member

I hope I'm asking this in the correct forum. I am very new to oracle and it's administration. I have a coldfusion script that copies data from one table to a backup table every night. Is there a way I can see when the copy-to table was last updated/changed to make sure my script is actually moving data?

It is basically truncating the backup table and rebuilding it on a nightly schedule.

Any ideas or suggestions are more than welcome. Hopefully this is a very easy and silly question for the more knowledgeable folks here.

Report message to a moderator

Re: view table change date [message #228367 is a reply to message #228355]

Mon, 02 April 2007 13:01

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Add a column called TIMESTAMP (or whatever yuo like) and load it with SYSDATE during the INSERT.

Report message to a moderator

Re: view table change date [message #228457 is a reply to message #228355]

Tue, 03 April 2007 02:21

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Create CONTROL_TABLE and add there a row for each successfull batch process.

Report message to a moderator

Re: view table change date [message #228475 is a reply to message #228457]

Tue, 03 April 2007 03:09

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Quote:

One option is as follows:

(1) Turn the auditing on: AUDIT_TRAIL = true in init.ora

(2) Restart the instance if its running.

(3) Audit the table:

AUDIT INSERT,SELECT,DELETE,UPDATE on TableName
by ACCESS WHENEVER SUCCESSFUL

(4) Get the desired information using :
SELECT OBJ_NAME,ACTION_NAME ,to_char(timestamp,'dd/mm/yyyy , HH:MM:SS') from sys.dba_audit_object.

source

Report message to a moderator

Re: view table change date [message #228479 is a reply to message #228475]

Tue, 03 April 2007 03:32

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Code: [Select all] [Show/ hide]

Hi,
(1) Turn the auditing on: AUDIT_TRAIL = true in init.ora

(2) Restart the instance if its running.

I want to add one more step...
after edit INIT<SID>.ORA file create new SPFILE from pfile.
if you are using SPFILE file for startup.

or just set like

alter system set AUDIT_TRAIL=TRUE SCOPE=spfile;
and bounce your database
shutdown immediate;
startup;

Report message to a moderator

Re: view table change date [message #228486 is a reply to message #228479]

Tue, 03 April 2007 03:43

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

A bit careless of me Thumbs Down

Thanks for pointing that out Mohammad Thumbs Up

Report message to a moderator

Previous Topic:	Database Down after Server Reboot
Next Topic:	Senior DBA pls help to

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 14 05:06:32 CDT 2025