| trace unused tables [message #65978] |
Tue, 15 February 2005 00:50  |
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 #65982 is a reply to message #65978] |
Tue, 15 February 2005 10:57  |
 |
Mahesh Rajendran
Messages: 10708
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
