Re: defragmenting tablespaces in oracle 7.2.3

Brian Rasmusson wrote:
>
> Hi,
>
> How do i defragment my tablespaces in an Oracle 7.2.3 database? I do
> _not_ want to use exp/imp with compression. I know that the SMON process
> will do it for me if my PCTINCREASE is non-zero, but unfortunately it
> _is_ zero. I also know that in Oracle 7.3 i can use ALTER TABLESPACE
> <name> COALESCE, but that doesn't help me either because it is an Oracle
> 7.2 database...
>
> Any suggestions?
>
> Thanks,
>
> Brian
>
> ---------------------------------------------------------------
>
> Name: vcard.vcf
> Part 1.2 Type: text/x-vcard
> Encoding: 7bit
> Description: Card for Brian Rasmusson
Hi Brian,
Enter following statement as dba (system) user   alter tablespace [tablespace_name] default storage (pctincrease 10)

then create a temp table in the tablespace   create table temp_table (flda varchar2(1))

     tablespace tablespace_name storage(initial 8192 next 8192)

look at your free space
select * from dba_free_space where tablespace_name = [tablespace_name] order by block_id

look at each row listed if the blockid + blocksize is equal to the next rows block_id it did not coalesce. Bld a table with the size of the biggest size shown.
after you are done alter the tablespace to set pctincrease back to 0.

hope this helps.
Dick Received on Thu Sep 04 1997 - 00:00:00 CDT