Home » RDBMS Server » Server Administration » view table change date
view table change date [message #228355] Mon, 02 April 2007 12:23 Go to next message
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.
Re: view table change date [message #228367 is a reply to message #228355] Mon, 02 April 2007 13:01 Go to previous messageGo to next message
joy_division
Messages: 4640
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.
Re: view table change date [message #228457 is a reply to message #228355] Tue, 03 April 2007 02:21 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Create CONTROL_TABLE and add there a row for each successfull batch process.
Re: view table change date [message #228475 is a reply to message #228457] Tue, 03 April 2007 03:09 Go to previous messageGo to next message
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
Re: view table change date [message #228479 is a reply to message #228475] Tue, 03 April 2007 03:32 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

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;


icon14.gif  Re: view table change date [message #228486 is a reply to message #228479] Tue, 03 April 2007 03:43 Go to previous message
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
Previous Topic: Database Down after Server Reboot
Next Topic: Senior DBA pls help to
Goto Forum:
  


Current Time: Tue Dec 06 06:36:41 CST 2016

Total time taken to generate the page: 0.09184 seconds