Re: Subject: Block Fragementation

From: DAtheDBA <dathedba_at_aol.com>
Date: 1996/10/03
Message-ID: <531pa4$m4t_at_newsbf02.news.aol.com>#1/1


If what you're talking about is the defragmentation of free extents, then there
is a command that you can execute to cause the defrag to happen. On Oracle 7.3.2, the command is 'ALTER TABLESPACE tsname COALESCE'. On some previous versions of Oracle7 you may have to do the following nasty
command...
  ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME COALESCE LEVEL tablespace_number';

   Just do this for tablespace_number from 1 through the number of tablespaces
that you have. It shouldn't hurt anything.

   Before you do this you can see the number of free extents per tablespace...

      SELECT TABLESPACE_NAME, COUNT(*), SUM(BYTES), MAX(BYTES)
         FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME;

  This will also show you the total amount of space AND the largest free extent
for each tablespace which has free space.

   Good luck.
-Doug Anderson
 DAtheDBA_at_aol.com Received on Thu Oct 03 1996 - 00:00:00 CEST

Original text of this message