Home » RDBMS Server » Performance Tuning » trace unused tables
trace unused tables [message #65978] Tue, 15 February 2005 00:50 Go to next message
Panki
Messages: 1
Registered: February 2005
Junior Member
Hi
I would like to know how can i trace all the tables in my DB which are not accessed since last year.
Thnks in advance
Re: trace unused tables [message #65980 is a reply to message #65978] Tue, 15 February 2005 05:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Unless you have your database auditing turned on, it is impossible.
Re: trace unused tables [message #65982 is a reply to message #65978] Tue, 15 February 2005 10:57 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
well,
this is not an exact solution you are looking into.
without auditing ( database audit / monitoring in 9i) there is no way we can say whether historically table/index is actually used or NOT.

but with information provided in v$segment_statistics you see whether the name is accessed since the startup,
and this information is flushed with database restart.

--
-- just bounced the database.
-- NO information in v$segment_statistics.
mag@mutation_mutation > get seg
  1  select statistic_name,value
  2  from v$segment_statistics
  3* where owner='MAG' and object_name='EMP'
mag@mutation_mutation > /

no rows selected

mag@mutation_mutation >  select * from emp where ename='SCOTT';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20

mag@mutation_mutation > @seg

STATISTIC_NAME                                                        VALUE
---------------------------------------------------------------- ----------
logical reads                                                            32
buffer busy waits                                                         0
db block changes                                                          0
physical reads                                                           15
physical writes                                                           0
physical reads direct                                                     0
physical writes direct                                                    0
global cache cr blocks served                                             0
global cache current blocks served                                        0
ITL waits                                                                 0
row lock waits                                                            0

11 rows selected.

Previous Topic: Connect By Cause Full tablescan:pl help
Next Topic: Performance Tuning.very critical
Goto Forum:
  


Current Time: Wed Sep 23 12:19:43 CDT 2020