Home » RDBMS Server » Server Administration » Date and time of last table access
Date and time of last table access [message #236665] Thu, 10 May 2007 10:45 Go to next message
mc_tyner
Messages: 2
Registered: May 2007
Junior Member
Hello,

Is there any way to know last time a table has been accessed (via select or insert or update or delete commands)?
Is this function present in TOAD?

THANKS
Re: Date and time of last table access [message #236670 is a reply to message #236665] Thu, 10 May 2007 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No unless you activate auditing on these tables.

Regards
Michel
Re: Date and time of last table access [message #236672 is a reply to message #236665] Thu, 10 May 2007 10:53 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
You can put a trigger on the table that records the relevant information in a lookup table, whenever the table in question is accessed. The other option would be to enable auditing.
Re: Date and time of last table access [message #236674 is a reply to message #236672] Thu, 10 May 2007 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no trigger on select.
Never write a trigger if you can do it with standard audit.

Regards
Michel
Re: Date and time of last table access [message #236675 is a reply to message #236665] Thu, 10 May 2007 10:59 Go to previous messageGo to next message
mc_tyner
Messages: 2
Registered: May 2007
Junior Member
Thanks guys,

Is it possible to enable auditing at single table level?
Can I do it via TOAD?
Re: Date and time of last table access [message #236677 is a reply to message #236675] Thu, 10 May 2007 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
See AUDIT statement.

Regards
Michel
Re: Date and time of last table access [message #236679 is a reply to message #236672] Thu, 10 May 2007 11:06 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
True. I noticed that the OP wanted to note the selects on the table as well, so the trigger won't serve the purpose.
As for the second part of your answer, while auditing is the normal way to keep track of who accessed the table, triggers 'can' be written to do exactly that. There will be circumstances where one may be better than the other. It all 'depends'.
Re: Date and time of last table access [message #236682 is a reply to message #236679] Thu, 10 May 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only circonstances I see where trigger is better than audit is when audit doesn't give you the data you want.

Regards
Michel
Re: Date and time of last table access [message #236689 is a reply to message #236682] Thu, 10 May 2007 11:17 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Is there any cut and dried reason about why auditing is better than
triggers?
Re: Date and time of last table access [message #236708 is a reply to message #236689] Thu, 10 May 2007 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Performances!

Regards
Michel
Re: Date and time of last table access [message #236712 is a reply to message #236665] Thu, 10 May 2007 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
AUDIT has already been debugged by Oracle; ZERO local programmer overhead.
Re: Date and time of last table access [message #236718 is a reply to message #236689] Thu, 10 May 2007 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AUDIT will be available in all Oracle versions.
Nothing to do or check when upgrading. No non-regression tests.

Regards
Michel
Re: Date and time of last table access [message #236903 is a reply to message #236665] Fri, 11 May 2007 04:45 Go to previous messageGo to next message
hotmercury
Messages: 13
Registered: February 2007
Location: Mumbai
Junior Member
Wat would be the effect on performance when starting the audit on a single object..? If auditing has to be done for a single object...wdnt trigger be the better option...?
Re: Date and time of last table access [message #236939 is a reply to message #236903] Fri, 11 May 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a small exemple:
SQL> create table t (id integer, val char(80));

Table created.

SQL> declare
  2    st pls_integer := dbms_utility.get_time;
  3  begin
  4    for i in 1..10000 loop
  5      insert into t values(i, 'A');
  6    end loop;
  7    update t set val='B';
  8    delete t;
  9    dbms_output.put_line('No audit done in '||(dbms_utility.get_time-st)/100||'s');
 10  end;
 11  /
No audit done in 2.86s

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> drop table t purge;

Table dropped.

SQL> create table t (id integer, val char(80));

Table created.

SQL> audit insert, update, delete on t;

Audit succeeded.

SQL> declare
  2    st pls_integer := dbms_utility.get_time;
  3  begin
  4    for i in 1..10000 loop
  5      insert into t values(i, 'A');
  6    end loop;
  7    update t set val='B';
  8    delete t;
  9    dbms_output.put_line('Audit done in '||(dbms_utility.get_time-st)/100||'s');
 10  end;
 11  /
Audit done in 6.71s

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> noaudit insert, update, delete on t;

Noaudit succeeded.

SQL> drop table t purge;

Table dropped.

SQL> create table t (id integer, val char(80));

Table created.

SQL> create table a as select * from dba_audit_trail where 1=0;

Table created.

SQL> create or replace trigger trg_t 
  2  before insert or update or delete on t
  3  begin
  4    insert into a 
  5          (os_username, username, userhost, terminal, timestamp, owner, obj_name,
  6           action, action_name, sessionid, os_process, entryid, statementid, returncode)
  7    select osuser,      username, machine,  terminal, sysdate,   user,  'T',
  8           0,      'MY_ACTION', sid,       process,    0, 0, 0
  9    from v$session where sid = sys_context('userenv','SID');
 10  end;
 11  /

Trigger created.

SQL> declare
  2    st pls_integer := dbms_utility.get_time;
  3  begin
  4    for i in 1..10000 loop
  5      insert into t values(i, 'A');
  6    end loop;
  7    update t set val='B';
  8    delete t;
  9    dbms_output.put_line('With trigger done in '||(dbms_utility.get_time-st)/100||'s');
 10  end;
 11  /
With trigger done in 10.33s

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

No audit: 2.86s
Audit: 6.71s
Trigger: 10.33s

Note: trigger records much less data than audit.
Note: I did not display other important data: generated redo/undo, latches...

Regards
Michel

Re: Date and time of last table access [message #237116 is a reply to message #236665] Sat, 12 May 2007 04:18 Go to previous messageGo to next message
hotmercury
Messages: 13
Registered: February 2007
Location: Mumbai
Junior Member
U didnt leave much space for a debate there.!! Smile.. Thanks for the effort.....
Re: Date and time of last table access [message #237156 is a reply to message #236939] Sat, 12 May 2007 14:18 Go to previous message
milindss
Messages: 5
Registered: May 2007
Junior Member
Michel,
That was excellent ! you rock !
Previous Topic: pct free and pct used ????
Next Topic: "ORA-12518: TNS:listener could not hand off client connection", fixed but...
Goto Forum:
  


Current Time: Sat Dec 10 20:34:45 CST 2016

Total time taken to generate the page: 0.04992 seconds