DBA script (1)

From: Paultje Bakker <bakker_at_cs.uq.oz.au>
Date: 21 Feb 1994 01:02:10 GMT
Message-ID: <2k916i$3p5_at_uqcspe.cs.uq.oz.au>

Hi people,

This is the second in a series of seven postings by me. The first contained my questions to the group. This posting has a script for finding oversized tables. Try it; you may be surprised!

REM FIND_OVERSIZE_TABLES.SQL
REM Created 31-OCT-93 by R. Bakker
REM Purpose is to identify those tables that are taking up much more REM space than they need to, eg. by being estimated incorrectly. REM
REM Currently, tables which are less than one-fifth full and consume more REM than 20K total space are selected, but this can be easily changed. REM This script creates and runs OVERSIZE_TABLES_TEMP.SQL, which REM puts a list of oversize table in OVERSIZE_TABLES.TXT. REM
REM Oversize tables don't happen too often, so it's left to REM the user to do something about it. One way of downsizing the REM table is to export the user who owns it (so that grants aren't REM lost), recreate the table definition by running RECREATE_TABLE.SQL REM (or looking for the script), recreating the table with the adapted REM parameters and importing just that table. Be aware that with this REM method, all second-hand grants on the table (ie. grants given by users REM who were given "WITH GRANT OPTION" privileges on the table) will REM be lost!
REM Also, beware of reducing empty tables too far; they may be temporary REM ones that get pretty well filled sometimes. REM
REM You may want to copy and change this script so that it only runs for REM tables you're interested in. It can take quite a while with large REM tables.
REM
REM Requires DBA privilege. Note that minor adjustments need to be made REM for non-VMS environments (eg. if the block size is different). REM
set echo off
set feedback off
set head off
set embedded on
set timing off
set termout off
set pagesize 9999
column line1 format a80 newline
column line2 format a80 newline
column line3 format a80 newline
column line4 format a80 newline
column line5 format a80 newline
column line6 format a80 newline
column line7 format a80 newline
column line8 format a80 newline
column line9 format a80 newline
column line10 format a80 newline
column line11 format a80 newline
column line12 format a80 newline
column line13 format a80 newline
column line14 format a80 newline
spool oversize_temp.sql

select 'set echo off'                  line1


,      'set feedback off'              line2

,      'spool oversize_tables.txt'     line3

,      'prompt'                        line4


,      'prompt Searching for tables using less than 20% of space taken...'


                                       line5


,      'prompt'                        line6

,      'set heading off'               line7

,      'set embedded on'               line8


,      'select ''Date run: ''||to_char(sysdate) from sys.dual;'


                                       line9


,      'prompt'                        line10

from sys.dual;

select

 'select'                line1
,'''OWNER           TABLE                          SPACE USED   SPACE TAKEN'''
                         line2
,'from   sys.dual;'      line3
,''                      line4
,'select'                line5
,'''--------------- ------------------------------ ------------ ------------'''
                         line6
,'from   sys.dual;'      line7

from sys.dual;

select

 'column owner format a15'         line1
,'column segment_name format a30'  line2
,'column actual_size format a12'   line3
,'column space_taken format a12'   line4

from sys.dual;

select 'drop view temp;'

       line1


,      'create view temp as'


       line2


,      'select '''||owner||''' owner,'''||table_name||''' table_name,'


       line3


,      '       2*count(distinct(substr(rowid,1,8)||'


       line4


,      '                        substr(rowid,15,4))) actual_size'


       line5


,      'from   '||owner||'.'||table_name||';'


       line6


,      ''

       line7


,      'select T1.owner,'


       line8


,      '       T1.segment_name,substr(to_char(min(T2.actual_size))||''K'',1,11)'


       line9


,      '                                                    actual_size,'


       line10


,      '       substr(to_char(sum(T1.bytes)/1024)||''K'',1,11) space_taken'


       line11


,      'from   dba_extents T1,temp T2'


       line12


,      'where  T2.owner = T1.owner and T2.table_name = T1.segment_name'


       line13


,      'group by T1.owner, T1.segment_name'


       line14


,      'having sum(T1.bytes)/1024 > 20'


       line15


,      '   and sum(T1.bytes)/1024 > 5*min(T2.actual_size);'


       line16

from dba_tables
where owner not in ('SYS','SYSTEM')
order by owner,table_name;

select 'spool off'           line1


,      'prompt'              line2


,      'prompt A listing is available in OVERSIZE_TABLES.TXT'


                             line3


,      'prompt'              line4

,      'set heading on'      line5

,      'set embedded off'    line6

,      'set feedback on'     line7


,      'clear columns'       line8


from   sys.dual;

spool off
set termout on
_at_oversize_temp
$ delete/nolog oversize_temp.sql;*
set termout off
set echo on
set feedback on
set head on
set embedded off
set termout on

Rick Bakker, roving DBA

--
Paul Bakker  bakker_at_cs.uq.oz.au | "PhD theses usually marshall an
Computer Science Dept.          | army of facts to starve a
The University of Qld           | slender and tedious truth
QLD 4072 Australia              | into submission" - The Guardian              

Received on Mon Feb 21 1994 - 02:02:10 CET