Emergency UNIX and Linux Support

From morning we are facing some issues in tablespaces in oracle9i.

Tried deleting some huge records... but even thought it is still giving tablespcases in full.

Then i tried and googling ... found we need to purge recyclebin of oracle..
but thats not woking in oracle9i...

can any one help in cleaningup some spaces for time being ....

You don't have a recycle bin in Oracle < 10g and, in any case, you don't need to purge it manually (it gets purged automatically when space pressure occurs).

In order to reclaim the free space in the tablespace, in Oracle 9i, you need to recreate (move) the table (and rebuild its relative indexes) subject to mass deletions.

Code:

alter table <table_name> move;
alter index index1 rebuild;
alter index index2 rebuild;
...

You need to take special care if you need to do this in production, because (this is not a complete list):

1. During the move the table will be locked in exclusive mode.
2. Right after the move the indexes on the table will be marked unusable and statements that try to use those indexes will fail (you'll need a maintenance window).
3. Before you begin you'll need to check the definitions of the involved objects - for example if you have compressed indexes you may end up with no compression after the rebuild (i.e. you'll need to include compress in the alter statement too).
4. You could speed up the operations by using parallel degree greater than 1 (only EE), just remember to reset the degree back to their original values.
5. You could rebuild the indexes online (EE only), but you could move the table online only if it's an IOT (Index Organized Table).
6. Remember to re-gather statistics after the move/rebuild operations.

That said, you can, of course, ad some space to the tablespace.

Hope this helps.