Re: Coelesce Tablespace in 7.2?

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/08/14
Message-ID: <3212B0DC.2A78_at_teldta.com>

David R. Gilbert wrote:
>
> Is there any way to force Oracle 7.2 to coelesce a
> tablespace right away?
>
> I know it's available in 7.3, but that's not an option
> for us right now.
>
> Thanks in advance.
>
> doc_at_balr.com
>
> --
> David Gilbert Internet: doc_at_balr.com, xanadu_at_mcs.net
> Consultant BALR Corporation - Information Technologies
> Web - PERSONAL: http://www.mcs.net/~xanadu WORK: http://www.balr.com

Maybe there is a better way but this has always worked for me (I hope my copy/paste worked okay).......

REM BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN 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;
    previous_row := this_row;
    fetch query into this_row;
  end loop;
end;
.
/
commit;

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

   from map_contig_space
   group by tablespace_name,

            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
where tablespace_name = upper('&USER_INPUT1') order by sum_bytes desc

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 END END END END END END END END END END END END END END

                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo

I think I've got the hang of it now .... :w :q :wq :wq! ^d X exit ^X^C ~. system X Q :quitbye CtrlAltDel ~~q :~q logout save/quit :!QUIT ^[zz ^[ZZ man help ^C ^c help exit ?Quit ?q CtrlShftDel "Hey, what does this button d..." Received on Wed Aug 14 1996 - 00:00:00 CEST