Empty Tables

Hi All,

I recently joined a small company running Oracle 8.1.6 on NT.
All tables are partitioned to 4 tablespaces. I am not sure whether these are hash or range partitions. The data in these tables are relatively small and the tablespaces are still using the first extent. The tablespaces are however 90% full but I think the space was taken up by the first extent right from the start. These empty spaces are taking a lot of space during backup and the CEO wants me to to something about it.
1. Should I remove 3 of these 4 tablespaces from backup?
2. How can I claim back these empty tablespaces?
3. How can I find out the type of partition used for these tables.

Please help!

Ac

To find out if the partitions are hash or range :

Select * From SYS.DBA_PART_TABLES

(look at the PARITION_TYPE column)

If 3 of the 4 tablespaces are empty, why don't you drop them from the database? The command is 'Drop Tablespace '. (then go and delete the corresponding datafiles at the OS level, because Oracle does not do this for you.)

Then when you need the space you can create the tablespace again.

This way you don't have to worry about if it is safe or not to exclude them from the backups. (By the way. you can just exclude them from backups, but it makes recovery a little trickier.)

-John

Thanks John,

I know I can "DROP TABLESPACE INCLUDING CONTENTS" but we are going to run Oracle Parallel Server soon so I'm not sure if this is a wis edecision. The recovery issue is another thing and I've not done it before.

Is it possible to claim back the space taken by the empty tables in these tablespaces? Please advice.

Ac

may be you can use alter table xxx deallocate unused?

If there are tables in the tablespace, then the tablespace is not empty even though the tables are. If you need your tablespaces to be smaller, you need to make your tables smaller. The 'deallocate unused' might help but I suspect it will not get you as much as you want.

You could drop all of your empty tables, coalesce the tablespaces, and then build your empty tables again except with a small initial extent (and normal sized next extent). Then you should be able to resize your datafiles so they are not quite so big. When the time comes that you need the space then you can make your datafiles big again.

-John

Thanks John,

You are right, I have tried the ALTER TABLE XXX DEALLOCATE UNUSED but it didn't solve the problem.

I will try your suggestion and see how it goes but I think it will resolve the problem.

-Ac

deallocate unused will deallocate unused space from second extent, if you only have one extent try

ALTER TABLE XXX DEALLOCATE UNUSED KEEP 1K;

Hi Pando,

I've used the command "ALTER TABLESPACE ...." and it worked. My tablespaces are heavily fragmented at the moment.

After I've done this, the backup is still taking the same amount of space as before. Please advice.

Ac

Hi

which command did you issue...? alter tablespace...?
if you have extents at end of datafile then I recogn the best way now would be export the schema/table and import

Hi AC,
Before removing your tablespace, be sure they are no more used by your clients. Set them "Offline" before....

Happy new year .
;-))

Hi Pando,

I used "ALTER TABLESPACE XXX DEALLOCATE UNUSED KEEP XXK"

Ac

ops I really meant

Alter Table not tablespace... but nevertheless I can imagine how fragmentated is your tablespace, try resize the tablespace if you cant resize it to a small enough size then you would have to EXP/IMP I am afraid....

Pando,

Thanks again for the correction but I actually meant "ALTER TABLE..."

I don't thing that "ALTER TABLESPACE XXX RESIZE" is a valid command but please let me know if I'm wrong.

Ac

alter database datafile '/u01/xyzdata01.dbf' resize 1M;