DBA script (5)

From: Paultje Bakker <bakker_at_cs.uq.oz.au>
Date: 21 Feb 1994 01:04:57 GMT
Message-ID: <2k91bp$3t0_at_uqcspe.cs.uq.oz.au>

Hi people,

Here is a script to amalgamate any adjoining free space in all the tablespaces. Yes, it uses a temporary table and yes, I'm ashamed.

REM COMPRESS_FREE_SPACE.SQL
REM Created 19-DEC-93 by R. Bakker
REM
REM Forces groups of adjoining free space in a tablespace into one REM piece. Useful for preventing fragmentation, which can seriously REM slow or even halt the functioning of a tablespace. REM
REM Uses the query of SHOW_COMPRESSED_FREE_SPACE to generate REM CREATE TABLE statements, which forces ORACLE to squeeze the REM free pieces together - but only if the RDBMS has no choice. REM This is the case when one starts with the largest free chunk REM in each file of each tablespace.
REM Note that "adjoining" pieces that consist of one piece also REM get selected - and temporarily filled with a table, but this is necessary REM in order to go from the biggest to the smallest. REM
REM The minimum size used is 20K.
REM
REM The file id and block id (separated by "A") are used to give the temporary REM tables unique names over all tablespaces. REM
REM Set Termout Off is used, but there can be a problem with dc_free_extents REM and row_cache_enqueues when the database is already too fragmented and REM the error won't be seen.
REM Best to use OU:BLOCK_ALLOCATION.SQL every now and then to see if the REM free space is really being compressed. REM
REM Assumption: one ORACLE block = 2k. True on VMS, may have to be changed REM for other operating systems.
REM
REM Modifications:
REM 23-DEC-93 R. Bakker Don't consider tablespaces with just one free extent REM
set echo off
REM _at_ou:free_space_statistics
set termout off
set head off
set embedded on
set linesize 80
set pagesize 9999
ttitle off
set feedback off
set verify off
define CR = "CHR(10)"
spool create_temp_tables.tmp
select 'create table T_DROP_ME_COMPRESS'||to_char(t1.file_id)||'A'||

       to_char(t1.block_id)||'(a char(1)) '||&CR||
       'tablespace '||t1.tablespace_name||' '||
       'storage (initial '||
       to_char(min((t2.block_id+t2.blocks)-t1.block_id)*2)||'K '||&CR||
       'next 4K minextents 1 maxextents 99 pctincrease 0);'

from sys.dba_free_space t2, sys.dba_free_space t1

where  t1.tablespace_name =  t2.tablespace_name
and    t1.file_id         =  t2.file_id
and    t1.block_id        <= t2.block_id
and    not exists (select 'x'
                   from   sys.dba_free_space t3
                   where  t3.tablespace_name = t1.tablespace_name
                   and    t3.file_id = t1.file_id
                   and    (t3.block_id + t3.blocks) = t1.block_id)
and    not exists (select 'x'
                   from   sys.dba_free_space t4
                   where  t4.tablespace_name = t2.tablespace_name
                   and    t4.file_id = t2.file_id
                   and    (t2.block_id + t2.blocks) = t4.block_id)
and    1 != (select count(*)
             from   dba_free_space t3
             where  t3.tablespace_name = t1.tablespace_name)

group by t1.tablespace_name,t1.file_id,t1.block_id having min((t2.block_id+t2.blocks)-t1.block_id)*2 >= 20 order by t1.tablespace_name,min((t2.block_id+t2.blocks)-t1.block_id) desc; spool off
_at_create_temp_tables.tmp

spool drop_temp_tables.tmp
select 'drop table '||table_name||';'
from dba_tables
where table_name like 'T_DROP_ME_COMPRESS%'; spool off
_at_drop_temp_tables.tmp
_at_ou:delete_file.sql create_temp_tables.tmp
_at_ou:delete_file.sql drop_temp_tables.tmp
set feedback on
set embedded off
set head on
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:04:57 CET