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.
If 3 of the 4 tablespaces are empty, why don't you drop them from the database? The command is 'Drop Tablespace <tablespace_name>'. (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.)
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.
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.