This content has been marked as final. Show 3 replies
Error is very clear. You cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the end of the datafile.
Also, the tables may be having extents allocated with empty space.
The fact that you cannot shrink the size of the file significantly means that the tablespace was most likely almost full at some point in the past. If it was almost full before, it may well get full again. The STREAMS tablespace stores spilled application data, among other things, so the tables grow and shrink depending on the replication load.
If I were you, I would reevaluate whether reclaiming the disk space is worthwhile.
there are commands for moving tables, indexes and LOB segments.
ALTER TABLE <table> MOVE TABLESPACE ...
ALTER TABLE <index> REBUILD [ONLINE] TABLESPACE ...
ALTER TABLE <table> MOVE LOB(<lob>) STORE AS (TABLESPACE ...)
Heap tables and LOBs can be moved offline only. Means Streams should be most likely stopped.
IOTs and indexes can be moved online.
You can try to move first segments that have largest end block # to same TS.
Most likely they will be moved to beginning of TS.