Oracle TableSpace Maintenance

Normally, I don’t get to worried about the size of TableSpaces, as they grow and shrink all the time. However, recently while setting up some automation scripts I was having problems with multiple databases being on the same server due to space issues.  So I decided it was time to take a review of the table spaces and see if there was some that I could easily do some quick maintenance to so that I could free up some space.

As always I found some guide examples out there using good old google.  The main item that I found that I could easily and quickly adjust was the undo tablespace.  Since  these scripts were for refreshing the environment and these environments are used by a very small subset of people, have a 32gb undo tablespace seemed a wee-bit excessive. So after the database clone is done I execute the following commands:

CREATE UNDO TABLESPACE undotbs2 DATAFILE ‘/u01/oracle/mynewdatabase/datafiles/undotbs02.dbf’ SIZE 2G AUTOEXTEND ON NEXT 250M;
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

After a stop and restart of the database 30 gigabytes of space are instantly reclaimed. So for 3 databases that is almost 100gb of dead space that is now free!

I also found a excellent bit of SQL for Oracle to show usage of the tablespace from Oracle-Base:

SELECT tablespace_name, size_mb, free_mb, max_size_mb, max_free_mb, TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM (SELECT a.tablespace_name, b.size_mb, a.free_mb, b.max_size_mb,a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name, TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ) ORDER BY tablespace_name;


For Temporary Tablespaces:

Maintenance on Temporary Tablespaces is a little easier than working with regular table spaces, you can use the following to easily shrink a temporary space:

ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;

or:

ALTER TABLESPACE temp SHRINK TEMPFILE ‘/u01/oracle/mynewdatabase/datafiles/temp01.dbf’ KEEP 40M;

If you don’t specify a KEEP option it while shrink to the smallest possible size it can.

For Regular Tablespaces

I am simply going to refer to guide I was using from Oracle-Base.  There are many ways to do maintenance on these tablespaces, and since I was looking for quick and simple ideas, the temporary and undo tablespace shrink was by far the easiest to do, but it is always a good strategy to do tablespace maintenance.