Today an sql script to calculate averange row len and total table size (Is extracted from the Knowledge Xpert for Oracle Administration library).
REM LOCATION: Object Management\Tables\Utilities
REM FUNCTION: Display the average row size of table and total table
REM size
REM TESTED ON: 10.2.0.3, 11.1.0.6 (should work with Oracle 9iR2)
REM PLATFORM: non-specific
REM REQUIRES: dba_tables, dba_extents
REM
REM INPUTS: Owner of table and name of table to report on
REM NOTE: This report requires that the table be analyzed.
REM
REM
REM This is a part of the Knowledge Xpert for Oracle Administration library.
REM Copyright (C) 2008 Quest Software
REM All rights reserved.
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME
SET PAGESIZE 66 HEADING ON VERIFY OFF
SET FEEDBACK OFF SQLCASE UPPER NEWPAGE 3
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME
COLUMN table_name format a30 wrap
COLUMN avg_row_len format 9,999,999,999 heading "Average|Row|Length"
COLUMN actual_size_of_data format 9,999,999,999 heading "Total|Data|Size"
COLUMN total_size format 9,999,999,999 heading "Total|Size|Of|Table"
TTITLE left _date center "Table Average Row Length and Total Size Report"
WITH table_size AS
(SELECT owner, segment_name, SUM (BYTES) total_size
FROM dba_extents
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name)
SELECT table_name, avg_row_len, num_rows * avg_row_len actual_size_of_data,
b.total_size
FROM dba_tables a, table_size b
WHERE a.owner = UPPER ('&INSERT_OWNER_NAME')
AND a.table_name = UPPER ('&INSERT_TABLE_NAME')
AND a.owner = b.owner
AND a.table_name = b.segment_name;
Stay tuned.
