SQL utilities
Date: Wed, 6 Oct 1993 19:38:29 GMT
Message-ID: <74993630916975_at_oxygen.aps1.anl.gov>
This message contains two SQL utilities demonstrated by Joe Trezzo of the Ultimate Software Consultants at the Oracle International Conference in Orlando These are being posted at the request of attendees of Joe's presentation on Oracle freespace management.
rem Program: defragsp.sql
rem
rem Created By: The Ultimate Software Consultants (TUSC)
rem Created: 04/01/93
rem
rem Copyright 1993 The Ultimate Software Consultants
rem
rem This program is for Oracle Version 6 Databases.
rem This program defragments all tablespaces by creating and dropping tables.
rem The program combines all contiguous blocks that are side by side. When
rem complete, all tablespaces will be defragmented.
rem
rem Input: Oracle_Block_Size rem Note: PC typically 1024 rem Other platforms typically 2048rem
rem NOTE: If after running the degragmentor, there still appears to be rem fragmentation, then verify all the CREATEs are succeeding. If there rem are a large number of contiguous segments that are going to be combined,
rem the init.ora parameters may be surpassed causing an error on the rem creation. rem Two init.ora parameters to possibly bump up if this problem occurs.rem The dc_free_extents (default of 50) may need to be bumped up to 100 or rem higher. Also, the row_cache_enqueues (default of 100) may need to be rem bumped up to 200 or higher.
rem *************************** NOTICE *********************************rem This program is to be used at your own risk. TUSC does not warrant the rem accuracy of this program and is not liable for any damage caused by rem the use of this program.
rem ********************************************************************define oracle_block_size=&&oracle_block_size set termout off
set numwidth 3
set wrap on
set verify off
set recsep off
set feedback off
set space 2
set newpage 0
set pagesize 58
set echo off
create table fragment_temp
(tablespace_name char(30) not null, file_id number not null, block_id number not null, blocks number)
pctfree 0 pctused 99
tablespace system;
declare
tablespace_name char(30); file_id number; block_id number; blocks number; prev_tablespace_name char(30); prev_file_id number; start_block_id number; total_blocks number := 0;
cursor space_cursor is
select tablespace_name,
file_id,
block_id,
blocks
from sys.dba_free_space
order by tablespace_name,
file_id,
block_id;
begin
open space_cursor;
fetch space_cursor
into tablespace_name,
file_id,
block_id,
blocks;
prev_tablespace_name := tablespace_name;
prev_file_id := file_id;
start_block_id := block_id;
total_blocks := blocks;
loop
fetch space_cursor
into tablespace_name, file_id, block_id, blocks;
exit when space_cursor %NOTFOUND;
if (block_id = (start_block_id + total_blocks)) and
(file_id = prev_file_id)
then
total_blocks := total_blocks + blocks; else
insert into fragment_temp
values(prev_tablespace_name, prev_file_id, start_block_id, total_blocks);
prev_tablespace_name := tablespace_name;
prev_file_id := file_id;
start_block_id := block_id;
total_blocks := blocks;
end if;
end loop;
insert into fragment_temp
values(prev_tablespace_name, prev_file_id, start_block_id, total_blocks);
commit;
end;
/
spool defragc.log
set feedback off
set heading off
set pagesize 0
set linesize 100
select 'create table space' || rownum || ' (temp char(10)) tablespace ' ||
tablespace_name || ' storage (initial ' || blocks * &&oracle_block_size
|| ');'
from fragment_temp
where blocks > 1
order by tablespace_name, blocks desc
/
spool off
set termout on
set echo on
start defragc.log
set termout off
set echo off
spool defragd.log
select 'drop table space' || rownum || ';'
from fragment_temp
where blocks > 1
order by tablespace_name, blocks desc
/
spool off
set termout on
set echo on
start defragd.log
drop table fragment_temp
/
exit
rem Program: truesp.sql
rem
rem Created By: The Ultimate Software Consultants (TUSC)
rem Created: 04/01/93
rem
rem Copyright 1993 The Ultimate Software Consultants
rem
rem This program is for Oracle Version 6 Databases.
rem This program displays the contiguous segments for all tablespaces.
rem The program gives the true free space listing by creating a temporary
rem table to store the information. This temporary table is dropped after
rem the listing is created. There is a listing for the contiguous free list
rem and non-contiguous free list. There is no defragmentation performed in
rem this procedure. To perform defragmentation, the program defragsp.sql is
rem used.
rem
rem Input: Oracle_Block_Size rem Note: PC typically 1024 rem Other platforms typically 2048 rem rem Output Files: truesp1.lis Contiguous Free Space Listing rem truesp2.lis Non-Contiguous Free Space Listingrem
rem *************************** NOTICE *********************************rem This program is to be used at your own risk. TUSC does not warrant the rem accuracy of this program and is not liable for any damage caused by rem the use of this program.
rem ********************************************************************define oracle_block_size=&&oracle_block_size set termout off
set numwidth 3
set wrap on
set verify off
set recsep off
set feedback off
set space 2
set newpage 0
set pagesize 58
set echo off
create table fragment_temp
(tablespace_name char(30) not null, file_id number not null, block_id number not null, blocks number)
pctfree 0 pctused 99
tablespace system;
declare
tablespace_name char(30); file_id number; block_id number; blocks number; prev_tablespace_name char(30); prev_file_id number; start_block_id number; total_blocks number := 0;
cursor space_cursor is
select tablespace_name,
file_id,
block_id,
blocks
from sys.dba_free_space
order by tablespace_name,
file_id,
block_id;
begin
open space_cursor;
fetch space_cursor
into tablespace_name,
file_id,
block_id,
blocks;
prev_tablespace_name := tablespace_name;
prev_file_id := file_id;
start_block_id := block_id;
total_blocks := blocks;
loop
fetch space_cursor
into tablespace_name, file_id, block_id, blocks;
exit when space_cursor %NOTFOUND;
if (block_id = (start_block_id + total_blocks)) and
(file_id = prev_file_id)
then
total_blocks := total_blocks + blocks; else
insert into fragment_temp
values(prev_tablespace_name, prev_file_id, start_block_id, total_blocks);
prev_tablespace_name := tablespace_name;
prev_file_id := file_id;
start_block_id := block_id;
total_blocks := blocks;
end if;
end loop;
insert into fragment_temp
values(prev_tablespace_name, prev_file_id, start_block_id, total_blocks);
commit;
end;
/
column tablespace_name format a20 heading 'Tablespace|Name' column blocks format 999,999,990 heading 'Size|of Extent|in Blocks' column bytes format 999,999,999,999 heading 'Size|of Extent|in Bytes' column block_cnt format 9,990 heading 'Number of Extents|With|This Block Size'spool truesp1.lis
ttitle 'The Ultimate Software Consultants|Contiguous Free Blocks' break on tablespace_name duplicate skip 1 on report compute sum of block_cnt on tablespace_name compute sum of block_cnt on report
compute sum of bytes on tablespace_name
compute sum of bytes on report
select tablespace_name,
blocks,
blocks * &&oracle_block_size bytes,
count(blocks) block_cnt
from fragment_temp
group by tablespace_name,
blocks
order by tablespace_name,
blocks desc
/
spool off
set feedback on
ttitle 'The Ultimate Software Consultants|Free Space Listing'
spool truesp2.lis
column tablespace_name format a20 heading 'Tablespace Name' column file_id format 99 heading 'Id' column block_id format 99999999 heading 'Block Id' column bytes format 9,999,999,999 heading 'Bytes' column blocks format 999,999 heading 'Blocks' column total format 9999999999999 heading 'Next Block Id'break on tablespace_name duplicate skip 1 on report compute sum of bytes on tablespace_name
compute sum of bytes on report
select tablespace_name,
file_id,
bytes,
blocks,
block_id,
block_id + blocks total
from sys.dba_free_space
order by tablespace_name,
file_id,
block_id
/
spool off
set feedback off
drop table fragment_temp
/
set termout on
prompt
prompt Output File Listings are in truesp1.lis and truesp2.lis
prompt
exit
Received on Wed Oct 06 1993 - 20:38:29 CET