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: Holger Baer <>
Date: Tue, 20 Apr 2004 10:49:37 +0200
Message-ID: <c62o72$d2o$1@news.BelWue.DE>

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.

An application that stores every data it needs within the database is not stupid IMO. You make a backup of your database and your done. Now you have to make sure, that you don't accidentally loose some files you might need later on. The database did keep them for you (assuming a sound design of the schema). Now you can get inkonsistencys because someone loads new pictures while your backup is running but for some reason the new files don't get picked up, later on your database is backed up having the file paths in there.

Now 3 Months later someone needs the new pictures for the first time, but unfortunately because of the disk outage just after the first backup, they are no longer on disk. Screw that, the full backup from 2 months before is also gone... er never succeeded. Throw in a little user error and the data is gone irrecoverably.

Keep the data in the database if you value your data. If you don't, then why bother at all.

With one application I support, we actually move the pictures *into* the database (that _was_ a stupid application until I picked it up).

Just my 2c

Holger Received on Tue Apr 20 2004 - 03:49:37 CDT

Original text of this message