TimArcher.com A blog about technology...and more.

So you keep extending your Oracle datafiles, but do you know what objects are the true space hogs? I use the query below, to look at the size of the objects in a specific tablespace.

SELECT owner,
       segment_name,
       segment_type,
       tablespace_name,
       bytes
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'DEVELOPMENT'
ORDER BY bytes desc

This will give me a list of my tables, indexes, and other objects in the specified tablespace in order by their size in bytes. The largest objects will be displayed first based on my sort criteria.

From this output, I usually see various tables that I can start purging, such as log tables, temp data tables, etc. This part will be specific to your own environment, but you'll want to delete data you don't need anymore.

Next, you need to compress the table extents and rebuild any indexes so that the space they used in the datafiles can be reused by other objects. It is not enough to just delete the data in the tables. You must either export/import the table, or use the much easier "alter table xxx move" syntax.

The two simple commands to do this are:

1. alter table tablename move;
2. alter index indexname rebuild;

So, lets say you have a log table named activity_log that is 1 GB in size. You delete all but the last 3 months of log data from in, but the table is still consuming the 1 GB. To pack the table and compress its extents you would run the command:

alter table activity_log move;

Now, that table probably had a few indexes on it that are also consuming a large chuck of space. We'll rebuild these to reclaim their space. It is important to note that your indexes will become invalidated after doing the alter table move, so you'll have to rebuild your indexes.

alter index act_event_code_date rebuild;

Lastly, I'll re-analyze the tables that I did mass deletes on and recompute the statistics for them:

analyze table activity_log compute statistics;

One important note is that the alter table move syntax does not work on tables with BLOB columns. You'll have to do your standard export/import to compress these tables.