Re: Defrag Oracle V6.0 Database (UNIX)

From: Ruth Ann L. Larson <rlarson_at_relay.nswc.navy.mil>
Date: Thu, 10 Feb 1994 14:19:57 GMT
Message-ID: <1994Feb10.141957.4537_at_newserver.uucp>

In article <1994Feb8.131008.17989_at_amoco.com> zgjc01_at_hou.amoco.com writes:
>An article by Joseph C. Trezzo (The Ultimate Software Consultants) suggests a method of defraging V6.0 tablespaces using a script he includes in paper #27 volume 2 of IOUG proceedings. Basically you create a temp table in a tablespace that is the size of total contiguous free blocks in a tablespace, then drop the temp table. This process is supposed to combine contiguous free blocks into one extent. IT DOESN'T, at least not on UNIX. Is anyone aware of why the examples he gives in his paper cannot be recre

a
>
>
>
>
>ted on my UNIX database. I can create objects, and when I drop them they reappear in the blocks listed in dba_free_space, but I cannot create any objects in the table which are larger than any single free extent. i.e. Oracle will not combine adjacent free extents inspite of what Mr. Trezzo claims ?????
>
>
>

     The following SQL*Plus script will combine all adjacent free extents in your tablespace,
if run repeatedly. Run until the "before" and "after" free extents are the same. It's kind of a "kluge" and rather messy in terms of what it does, but it does work. I could have done triplets, quadruplets, etc., of free extents, but then the question becomes where to stop, AND, there would have to be more queries UNIONed in. The trick is to always create the largest table first, and work your way down, and to also include the "solitary" free extents in the list. Please read the remarks at the beginning before using.   

                              Ruth Larson 


 
      -------------------------------------------------


remark     file combine.sql
remark          usage: _at_combine TABLESPACE
remark          written by:  Ruth A. Larson
remark                       Naval Surface Warfare Center, Dahlgren Div.
remark                       Phone (301)394-2053
remark                         January, 1994
remark
remark       Version 6 free-space cleanup.

remark Replace '4096' by your Oracle block size. remark This script will combine pairs of adjacent free extents in remark tablespace &1. It can be run repeatedly to combine all adjacent remark free extents.
remark It should be run by an Oracle ID that:

remark       1).  Can access the dba_free_space view (implemented here as
remark            sys.dba_free_space, change to how you would access)
remark       2).  Has unlimited resource privileges on the tablespace to
remark            be cleaned up.
remark       3).  Does NOT have any tables - this script DROPS all tables
remark            belonging to the current user.

remark It also shouldn't be run when the system is heavily loaded or when remark other tables could be created in the tablespace.

set verify off;
set heading off;
set FEEDBACK OFF;
SELECT 'tablespace ','&1',' had ',count(blocks),' free extents before combining'

     from sys.dba_free_space
     where tablespace_name = '&1';

SET TERMOUT OFF;
SET PAUSE OFF;
set heading off;
set pages 0;
SPOOL TEMP.SQL
SELECT 'create table x'||a.file_id||
a.block_id,'(d char(10)) tablespace '||'&1'||' storage(initial ', ( (a.blocks+b.blocks)*4096),');'
from sys.dba_free_space a
,sys.dba_free_space b
where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id
and (a.block_id+a.blocks)=b.block_id
and a.tablespace_name = '&1'
UNION
select 'create table x'||file_id||block_id, '(d char(10)) tablespace '||'&1'||' storage(initial ', blocks*4096,');'
from sys.dba_free_space
where tablespace_name = '&1' AND BLOCKS > 1

order by 3                   desc;

spool off
_at_TEMP
SPOOL TEMP.SQL
select 'DROP TABLE',TABLE_NAME,';' from system.dba_tables WHERE OWNER = user ;
SPOOL OFF
_at_TEMP
SET PAUSE ON;
SET HEADING ON;
SET PAGES ON;
SET TERMOUT ON;

select '                   has ',count(blocks),' after combining.'
     from sys.dba_free_space
     where tablespace_name = '&1';

SET FEEDBACK ON; Received on Thu Feb 10 1994 - 15:19:57 CET