table_usage2.sql

rem Script Description: The script tells you how often specific tables are loaded into memory 
rem                     and the number of times those tables have been executed. 
rem                     The statistics are based on the last start of the instance. 
rem                     Run this script after the instance has been running under a normal 
rem                     daily load.
rem
rem Output file:        tableusage.lis
rem
rem Prepared By:        Oracle Resource Stop
rem
rem Usage Information:  SQLPLUS SYS/pswd
rem                     @tableusage.sql
rem

set termout off;
set feedback off;
set echo off ;
set pagesize 59;
set linesize 79;
set newpage 0;
set space 1;
set escape off;

col iname noprint new_val instance
col today noprint new_val datetime

select name iname from v$database;

select to_char(sysdate, 'Mon DD, YYYY HH24:MI') today from dual;

btitle ce '======================================================' skip 1 -
   le 'tableusage.sql' col 70 'Page: 'FORMAT 999 sql.pno

ttitle center 'Database: ' instance ' - Table Usage Report' -
      skip 1 col 62 datetime skip 2

set pagesize 60
col name format A28 heading "TABLE NAME"
col tablespace format A10
col KB format 99,999,999
col sharable_mem format 99,990 heading "SHARE MEM"
col loads format 9,990
col executions format 999,990
col own noprint

break on tablespace

spool tableusage.lis

select c.loads, c.executions, c.owner||'.'||c.name name,
       t.tablespace_name tablespace, s.bytes/1024 KB, c.sharable_mem
from dba_tables        t,
   dba_segments      s,
   v$db_object_cache c
where c.type  = 'TABLE'
  and c.executions > 0
  and c.name  = t.table_name
  and c.owner = t.owner
  and c.name  = s.segment_name
  and c.owner = s.owner
order by 4, 1 desc, 2 desc, 3;

spool off;