Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Compressing emptied User-Datafiles

Re: Compressing emptied User-Datafiles

From: <Kenneth>
Date: Tue, 20 Apr 2004 08:19:56 GMT
Message-ID: <>

On Mon, 19 Apr 2004 22:53:59 +0200, "Clemens Keil" <> wrote:

>Hello All,
>Since I found great help in this group maybe you can sugest a solution for
>the following:
>I have a large 8i database with 4 User-Datafiles each carrying about 5 GB
>for Tablespaces.
>The server was full one day since there were large uncompressed pictures
>stored in the db as blobs (stupid application). We changed that to file
>paths in a separate file system, exported the blobs and dropped the
>blob-records and only store the file path string of the pictures in the
>external file system. So the Datafiles are almost empty now. Nevertheless
>the User-Datafiles are full (red bar in the Storage Manager is on maximum)
>and therefore the Datafiles cannot be shrinked.
>Nevertheless the whole database dump is only a small 200MB-file (compared to
>the 20GB datafiles).
>Is there a way to let the system know that there is much free space in the
>datafiles? A kind of compressing feature as eg. in MS-Access?
>Thank you for your help.

Hi Clemens,

You are in the classic situation of needing to reorganize your table segments.

You have deleted most of the content of your tables, but Oracle does not free unused table segment space when just deleting rows or row contents.

You can either :

  1. Export the tables in question, drop and re-import them.

Or (awkward)

b) create table mytab_copy tablespace mytbs as select * from mytab;

    drop table mytab;
   alter table mytab_copy rename to mytab;

Beware that you lose indexes,constraints and triggers on mytab this way, if you don't keep them manually.


c) alter table mytab move tablespace reorg_tbs;

    alter table mytab move tablespace mytbs;

 Beware that indexes on mytab are marked unusable and need to be rebuilt afterwards.

If you only have a few tables needing reorg, go for c). If there's a bunch of them, consider a).

Received on Tue Apr 20 2004 - 03:19:56 CDT

Original text of this message