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;