| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: tablespace fragmentation
This is a code fragments from a previous discussion in this group, take your pick on which method you use:
From: Stuart Speers <stuarts_at_datacom.co.nz>
Subject: Re: Coelesce Tablespace in 7.2?
Date: Tue, 20 Aug 1996 09:28:57 +1300
Use the following. run as sys.
define ts_name = &tablespace_name
set heading off
set feedback off
set termout off
set verify off
spool /tmp/coal.sql
select 'alter session set events ''immediate trace name coalesce level
'||ts#||''';'
from ts$
where name like upper('%&ts_name%')
/
spool off
set heading on
set feedback on
set termout on
set verify on
@/tmp/coal.sql
From: tkyte_at_us.oracle.com (Thomas J. Kyte)
Subject: Re: Coelesce Tablespace in 7.2?
Date: Wed, 14 Aug 1996 21:55:47 GMT
Install the following pl/sql procedure in the SYS schema, grant execute on
it to
the appropriate people and then you can:
SQL> exec sys.coalesce( 'SomeTableSpaceName' )
or
SQL> exec sys.coalesce
to coalesce free space in all tablespaces
create or replace procedure coalesce( ts_name in varchar2 default NULL ) as
number_of_extents number default 255 * 16 * 16 * 16 * 16;
exec_cursor integer default 0;
rows_processed number default 0;
stmt varchar2( 255 );
begin
dbms_output.enable( 1000000 );
for x in ( select number_of_extents + ts# ts#, name
from ts$
where ( name = upper(ts_name) OR ts_name is NULL )
AND ( online$ != 3 ) ) loop
stmt := 'alter session set events ' ||
'''immediate trace name coalesce level ' || x.ts# || '''';
exec_cursor := dbms_sql.open_cursor;
dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
dbms_output.put_line( 'Coalesced Tablespace ' || x.name );
dbms_output.put_line( stmt );
when others then
dbms_output.put_line( substr( stmt, 1, 200 ) );
dbms_output.put_line( 'SQLCODE = ' || sqlcode );
dbms_output.put_line( 'SQLERRM = ' || sqlerrm );
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
Or you can use the following code. It's something that I copied from somewhere and modified for my use. It takes advantage of Oracles ability to coalesce extents when creating tables. Old code but still works well:
set verify off
set pagesize 23
set linesize 80
set pause on
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off
col aa format a18 heading "TableSpace|Name"
select distinct tablespace_name aa
from dba_tablespaces;
PROMPT
ACCEPT USER_INPUT1 CHAR PROMPT 'Please select one of the above tablespaces
to combine freespace>'
PROMPT
PROMPT Working, Please wait.....
set verify off echo off termout off feedback off heading off pause off
set pagesize 999 linesize 80 newpage 0
col X format a80 word_wrapped
create table map_contig_space
(tablespace_name varchar(30),
file_id number, block_id number, starting_file_id number, starting_block_id number, blocks number, bytes number);
declare
cursor query is
select *
from dba_free_space
order by tablespace_name, file_id, block_id;
this_row query%rowtype;
previous_row query%rowtype;
old_file_id integer;
old_block_id integer;
begin
open query;
fetch query into this_row;
previous_row := this_row; old_file_id := previous_row.file_id; old_block_id := previous_row.block_id;
while query%found loop
if this_row.file_id = previous_row.file_id
and this_row.block_id = previous_row.block_id + previous_row.blocks
then insert into map_contig_space
(tablespace_name,
file_id,
block_id,
starting_file_id,
starting_block_id,
blocks,
bytes)
values
(previous_row.tablespace_name,
previous_row.file_id,
this_row.block_id,
old_file_id,
old_block_id,
this_row.blocks,
this_row.bytes);
else insert into map_contig_space
(tablespace_name,
file_id,
block_id,
starting_file_id,
starting_block_id,
blocks,
bytes)
values
(this_row.tablespace_name,
this_row.file_id,
this_row.block_id,
this_row.file_id,
this_row.block_id,
this_row.blocks,
this_row.bytes);
old_file_id := this_row.file_id;
old_block_id := this_row.block_id;
end if;
drop view v_map_contig_space;
create view v_map_contig_space
as select tablespace_name,
starting_file_id,
starting_block_id,
sum(blocks) sum_blocks,
count(blocks) count_blocks,
max(blocks) max_blocks,
sum(bytes) sum_bytes
starting_file_id,
starting_block_id;
select 'create table ' || tablespace_name || '_' || starting_file_id || '_' || rownum ||
' (dummy char(1))' ||
' storage(initial ' || sum_bytes || ' next ' || sum_bytes ||
' minextents 1 maxextents 1)' ||
' tablespace ' || tablespace_name || ';' X
from v_map_contig_space
spool /tmp/&USER_INPUT1._creates.sql
prompt set echo on termout on feedback on
/
spool off
select 'drop table ' || tablespace_name || '_' || starting_file_id || '_' ||
rownum || ';' X
from v_map_contig_space
where tablespace_name = upper('&USER_INPUT1')
order by sum_bytes desc
spool /tmp/&USER_INPUT1._drops.sql
prompt set echo on termout on feedback on
/
spool off
drop table map_contig_space;
drop view v_map_contig_space;
set term on
PROMPT
PROMPT Scripts completed to file /tmp/&USER_INPUT1._creates.sql and
/tmp/&USER_INPUT1._drops.sql
PROMPT
PAUSE Hit enter to run these scripts or CTRL-D now>
PROMPT
set echo on termout on feedback on
start /tmp/&USER_INPUT1._creates.sql
start /tmp/&USER_INPUT1._drops.sql
exit;
REM =========================== END OF FILE ============================
Amit Aggarwal wrote:
> Hi Guys: > > I have a question about tablespace fragmentation. > > We have an Oracle 7.1.3 database running on Digital Unix. Recently I > noticed that most of the tablepsaces are badly fragmented and after > looking in the 'DBA_FREE_SPACE' table, I found out that there are lot of > contiguous free extents. I tried the command: > ALTER TABLESPACE <name> COALESCE; > > to defragment the tablespaces but it seems like that that command was > not there in 7.1.3. Is there any other way to fix this fragmentation in > the tablespaces in 7.1.3. I know this command works fine in 7.3. > > Thanks for replying > > amit > Oracle DBA/Unix SAReceived on Fri Nov 14 1997 - 00:00:00 CST
 |
 |