Temp Segments in Normal Datafile (ORA-1652)

It has been over 2 months since my last entry, but i have reasons to be offline, like major location and job changes. I moved to UK (London) 2 months ago and joined to a huge organization. I am not just an Oracle DBA anymore, so you should see some SQL Server posts in this blog, please do not panic when you see them 🙂 I am still in an endless love with Oracle.

First of all, I am sorry that I can’t write any adventures about UKOUG because one of my colleagues already arranged his attendance when I joined the company. I hope I will write my own adventures next year.

Now its time to write technical stuff. Today we faced “ORA-1652: unable to extend temp segment”. One of my team members asked my opinion about “why the user getting this error on a normal tablespace instead of TEMP tablespace”. He said, the user was trying to create a table by using “create table xxx as select” clause. At first I thought that this should not happen, but when I re-think, I realized that, this is behaviour is like index rebuild. “Create table as select” must be different then “insert into” clause. In my opinion, It first uses temp segments on a permanent tablespace and if it is successful then it converts the segments as permanent. (Updated: Metalink note 181132.1 Thanks YAS) Lets see how

User HR using USERS tablespace as default tablespace and TEMP as temporary tablespace;

Size of the source table T1 is 264MB;

Available free space on USERS tablespace is;

Auto Extend option on USERS tablespace is off;

So I don’t have enough space for new table T2. Lets see what error is raised with different clauses.

When I try to create table T2 from T1 I got error 01652 which is about using temp segment on default tablespace USERS.

When I try to insert data to T2 from T1 I got error 01653 which is about cannot allocate normal extent on default tablespace. (where 1=2 syntax is just for creating the same table structure without data)

Lets try it about with index rebuild. As you might know you need free space with the same size of index when you to rebuld it.

First set auto extend on with 16MB extents;

Then create an index on T1;

Size of the index is 43MB;

Set auto extent off while free space on USERS tablespace is 6 MB;

When I tried to rebuild the index I expect to get temp segment error ORA-10252 on normal USERS tablespace, because it is really a temp usage.

Moral of the story is, temp segments are not always located on TEMP tablespaces.

updated (11/12/2007): Taken from metalink note id 181132.1

4. Temporary Segments for Permanent Segments Creation
-----------------------------------------------------
Besides sort operations, there are other SQL operations, which also require 
temporary segments: 
--> CREATE PRIMARY/UNIQUE KEY CONSTRAINT
--> ALTER TABLE ... ENABLE PRIMARY/UNIQUE CONSTRAINT
--> CREATE TABLE STORAGE (MINEXTENTS>1)
--> CREATE TABLE AS SELECT
    --> The CTAS creates a data segment in the target tablespace and marks this 
        segment as temporary in dictionary. On completion, the dictionary type 
        is changed from temporary to table. In addition, if the SELECT performs 
        a SORT operation, temporary space may be used as for a standard select.
    --> For a Parallel CTAS statement, each slave builds its own data segment 
        (marked as temporary in the dictionary) from the row source which feeds
        it. 
        Similarly, for Parallel Direct Load or Parallel Insert, each slave 
        process creates its own single temporary segment to load data into.
--> CREATE PARTITION TABLE
--> ALTER TABLE ... SPLIT PARTITION
--> CREATE SNAPSHOT
--> CREATE INDEX
    The CREATE INDEX statement, after sorting the index values, builds a 
    temporary segment in the INDEX tablespace; once the index is completely
    built, the segment type is changed to INDEX.
--> ALTER INDEX REBUILD
    During an index rebuild, besides the temporary segments used to store 
    partial sort (segments built in the user's default TEMPORARY tablespace), 
    Oracle uses a segment which is defined as a temporary segment until the 
    rebuild is complete. 
    Once this segment is fully populated, the old index can be dropped and the 
    temporary segment is redefined as a permanent segment with the index name. 
    The new version of the index, currently a temporary segment, resides in the 
    tablespace where the index is required. Note that the old index segment that
    is to be dropped is itself converted to a temporary segment first (like drop
    ping a table). Therefore, an index rebuild involves three temporary segments,
    one of which is a sort segment, that all may be located in different tablespaces.
--> DROP TABLE
    Oracle first converts the segment to a temporary segment, and starts 
    cleaning up the now temporary segments extents. If the drop is interrupted, 
    the temporary segment is cleaned up by SMON. If the SMON is interrupted by a
    shutdown abort, this may cause serious problem, and the total time to
    cleanup is increased.