Re: Those anyone have a script to retrieve information about

From: AJ Allen <aallen20_at_ford.com>
Date: Tue, 08 Feb 2000 11:26:37 -0500
Message-ID: <38A043BD.D8D5D5AA@ford.com>

Serge Nantel wrote:
>
> Those anyone have a script to retrieve information about
> Table name
> Initial Next
> Size Used
> Size Allocated %
> Used Extents
> Extent Extent
>
> Thank you
> snantel_at_mei.ca
> snantel_at_sympatico.ca

Try this. Some of the information requires current statistics so pick the detail items you need.
- - - - - - - - C U T H E R - - - - - - - SET PAGESIZE 40
SET LINESIZE 155
SET NEWPAGE 0

SET VERIFY     ON
SET ECHO       OFF

SET UNDERLINE =
SET HEADING OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET LONG 1000

COLUMN sys_id           NOPRINT new_value system_id
COLUMN sys_date         NOPRINT new_value system_date
COLUMN sys_user         NOPRINT new_value system_user

COLUMN table_name       FORMAT a30        HEADING 'Table Name' 
COLUMN tablespace_name  FORMAT a10        HEADING 'Tablespace' 
COLUMN owner            FORMAT a8         HEADING 'Owner'
COLUMN initial_extent   FORMAT 9,999,990  HEADING 'Inital   |Ext(K)   ' 
COLUMN next_extent      FORMAT 999,990    HEADING 'Next   |Ext(K) ' 
COLUMN max_extents      FORMAT 990        HEADING 'Max|Ext' 
COLUMN pct_increase     FORMAT 90         HEADING 'Pct|Inc' 
COLUMN pct_free         FORMAT 90         HEADING 'Pct|Free' 
COLUMN pct_full         FORMAT 999.99     HEADING 'Pct|Full' 
COLUMN num_rows         FORMAT 9,999,990  HEADING '# of|Rows'
COLUMN chain_cnt        FORMAT 99990      HEADING 'Chain|Count'
COLUMN blocks           FORMAT 99,990     HEADING 'Data|Blks'
COLUMN empty_blocks     FORMAT 999,990    HEADING 'Empty|Blks '
COLUMN avg_row_len      FORMAT 9,990      HEADING ' Avg |Lengt'
COLUMN ext_count        FORMAT 990        HEADING 'Ext|Cnt'

TTITLE Right  'Date: ' system_date   -
  skip Left   'Database: ' system_id -
       Center 'Table Storage Information'  -
       Right  'User: ' system_user   -
  skip Center '~~~~~~~~~~~~~~~~~~~~~~~~~' -

  skip 2

REPFOOTER -

     SKIP 2 -
     LEFT 'The sum of the pctree and the pctused must always be less
than or equal'-
     SKIP 1 -
     LEFT 'to 100, and must be chosen so that a block does not keep
getting swapped' -
     SKIP 1 -
     LEFT 'from available to not available to store data (on the free
block list).' -
     SKIP 2 -

   CENTER '### ## END OF REPORT ## ###' BTITLE -
SKIP 2 RIGHT 'Page: ' format 99 sql.pno - SKIP 1 CENTER '### ## Confidential Information -- Dispose of properly ## ###'

SELECT  d.name               sys_id,
        to_char(SysDate,'mm/dd/yyyy  HH24:MI:SS') sys_date,
        User                 sys_user

  FROM v$database d
;

SET termout ON
SET heading ON

--SPOOL Table_Info.lst

SELECT  t.table_name,
        t.tablespace_name,
        t.owner,
        t.initial_extent/1024  initial_extent,
        t.next_extent/1024     next_extent,
        t.max_extents,
        t.pct_increase,
        t.pct_free,
        t.num_rows,
        t.avg_row_len,
        t.chain_cnt,
        e.ext_count,
        t.blocks,
        t.empty_blocks,
        100*((NUM_ROWS * AVG_ROW_LEN)/
             ((GREATEST(blocks,1) + empty_blocks) *
             4096)) pct_full
  FROM  sys.dba_tables       t,
        (SELECT  owner              owner, 
                 segment_name       table_name, 
                 count(*)           ext_count 
           FROM  sys.dba_extents
          WHERE  segment_type = 'TABLE'
            AND   owner      <> 'SYS'
            AND   owner      <> 'SYSTEM'
          GROUP
            BY owner, 
               segment_name 
        ) e
 WHERE  t.owner          NOT IN ('SYS', 'SYSTEM') 
   AND  t.owner               = e.owner
   AND  t.table_name          = e.table_name

 ORDER
    BY t.owner,

        t.table_name
;
--spool off

• - - - - - - - - - - - - - - - - - - - - - - -- *------------------------------*----------------------------* | Andrew Allen | Against fools, the gods | | Process Leadership / PSL DBA | themselves contend in vain | | Dearborn, MI USA | -- author unknown | *------------------------------*----------------------------*

Received on Tue Feb 08 2000 - 10:26:37 CST