EXECUTE IMMEDIATE, ALTER OBJECT MOVE/REBUILD, TEMPORARY SEGMENTS STAY allocated

[Orca777]

Hi,


ORACLE : Oracle 8.1.7.3.50 EE

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?

Please let me know if you have an idea

Orca

[Orca777]

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!