i developed a REORG-Tool for Indexes and Tables with PL/SQL.
One of the Procedures moves all Objects of an Tablespace to another. The DDL's are executed with EXECUTE IMMEDIATE ...
When the Destination-Tablespace grows due to moved Objects the Source-Tablespace shows always the same total allocated size. When the script finished then no allocated place is seen in the Source-Tablespace.
- I have seen tempoary segments in the Source-Tablespace
- every objects moved created a temporary Segment in the Source-Tablespace
Why Oracle holds the segments till the procedure ends?
Hi, thanks for ( future ) reply;
The problem is not in the TEMP-Tablespace, it's in the Source-Tablespace ( LOCAL MANAGED, UNIFORM, ... )
Every Table / Index-segment which is moved to the Dest-Tablespace is not deleted immediatly but renamed/marked as 'TEMPORARY'.
This must be a sideeffect with EXECUTE IMMEDIATE!