big full backup problem

[Hr. RJH Verhagen]

Hello,
I have a backup problem with Oracle: to make a full database backup I put the database in backup mode and backup de tablespaces with cpio and gzip. In time this backup was getting bigger and bigger so that a time ago I cleared some very big tables. Now is the data 1/10th from the data before but the backup size keeps the same (I also make an exp. witch is 1/10 of the original size). I think I understand why: I cleared the table but the tablespaces are as patitions on a OS, clearing a file only clears the pointer to this file, the file contains remains on the harddisk.
Does anybody knows how to really clear a tablespace so that my full backup is smaller again or is the only way to delete the tablesspaces, make them again and do an import?
I use Oracle 7.3.3
Thanks,
Robert Verhagen.

[Yogeeraj_1]

hi,

There is a chance you can shrink some of the datafiles. We can only shrink files back to their high water mark -- if there is an extent way out at the end of a file -- we'll not be able to shrink it.

If not, the only thing that will undo this is to recreate the database (exp, imp).

hope this helps!

regards
Yogeeraj

[Yogeeraj_1 · Accepted solution]

hi again,

Script to guide you on which datafiles can be shrinked is attached!

CAUTION: Adjust the values accordingly otherwise you may run into space problems.

regards
Yogeeraj

[Greg OBarr]

I don't know how many tables and tablespaces you're talking about here, but if it's not too many, you could try this:

1) Create a new tablespace large enough to hold what's in the one you want to shrink
2) Find all tables in the tablespace you want to shrink and move them to the new tablespace using
SQL> alter table
move tablespace ;
If you're going to leave them there, recreate your indexes in the new tablespace too.
3) When they're all moved, drop the original tablespace
4) Now, if you want, you can re-create the original tablespace with a smaller size
5) Reverse the procedure in step 2 to move the tables back to the original tablespace (if desired) and re-build the indexes.

This should work in the higher 8i versions and above.

-greg

[Ed Sampson]

Robert,
You may want to do a dbexport of the data. This will only backup the actual table entries, not the entire tablespace. You can dump it to a flat file in another filesystem, compress it and use regular backups on it.
You will need to do the dbexport for each table in the database. This gives you a 2 tiered backup scheme, with the most current on disk, and +1 on tape.
Hope this helps..

[Michael Steele_2]

This is a fragmentation issue and has always been a problem with fixed table sizes. All oracle file systems are 99% full. So defragmentation is only allowed through exporting and importing.

If the oracle file system was 85% full then you could use online JFS's defrag. utility.

You're other alternative is to dbstop and copy the data in and out from the O/S instead of using Oracle.

cp -p -r /dir/* /temp/*
rm /dir/*
cp -p -r /temp/* /dir/*

Data is not fragmented when loaded for the first time or copied back in after deletion.