table_stats.sql

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

--* File Name    : Table_Stats.sql
--* Author       : DR Timothy S Hall
--* Description  : Displays the table statistics belonging to the specified schema.
--* Requirements : Access to the DBA and v$ views.
--* Call Syntax  : @Table_Stats (schema-name or all)
--* Last Modified: 15/07/2000
SELECT t.table_name AS "Table Name", 
       t.num_rows AS "Rows", 
       t.avg_row_len AS "Avg Row Len", 
       Trunc((t.blocks * p.value)/1024) AS "Size KB", 
       t.last_analyzed AS "Last Analyzed"
FROM   dba_tables t,
       v$parameter p
WHERE t.owner = Decode(Upper('&1'), 'ALL', t.owner, Upper('&1'))
AND   p.name = 'db_block_size'
ORDER by t.table_name;