Coskan’s Approach to Oracle

December 5, 2007

Temp Segments in Normal Datafile (ORA-1652)

Filed under: Basics, Tips, Uncategorized — coskan @ 6:44 pm

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;

free space

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.

unable to extent temp segment

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)
normal segments

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;

create index

Size of the index is 43MB;

index size

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: 
    --> 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
        Similarly, for Parallel Direct Load or Parallel Insert, each slave 
        process creates its own single temporary segment to load data into.
    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.
    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.
    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.

July 2, 2007

Campaign against separately licensed Diagnostics Pack

Filed under: Uncategorized — coskan @ 8:15 am

 Mark Brinsmead from Pythian Group wrote an open letter to Larry Ellison for reviewing the licensing policy of Diagnostics Pack. I digitally signed and hope you all sign that letter till 10 July the day before the planned announcement of Oracle 11g.

You can read and sign the blog entry from this link

April 5, 2007

Child Listener Problem

Filed under: Uncategorized — coskan @ 7:27 am

A while ago one of our systems faced with a child listener occurrence problem. The listener wasn’t answering the requests., when our clients were trying to reach the server through listener as a new connection.We couldn’t get the status of the listener with lsnrctl status command and nothing was written to the listener logs.

There wasn’t any problem on the database itself because local connections are accepted and database answers your requests. Also already connected users are working normally. Conclusion was “our listener hanged”

When we looked at the listener proceses we saw that there was a a child listener forked from the real listener.

#ps -ef | grep tns
oracle 22480 14188 0 18:33:57 $ORACLE_HOME/bin/tnslsnr listener_DBNAME -inherit
oracle 14188 1 0 Apr 1 $ORACLE_HOME/bin/tnslsnr listener_DBNAME -inherit

We tried to stop the listener with lsnrctl stop command but it didn’t work. (after closing the application servers)

So we killed the listener processes from OS

# kill -15 22480,14188

After the killing operation we opened the listener and everything worked well and all the connection requests were accepted. This fast action must be taken immediately and it was time to find a real solution for the problem.

Simple metalink search explained the problem and the solution. Note:340091.1 says that “This is a known problem addressed via non-published bug:4518443 (Abstract: Listener Gets Hung Up). The issue is that the TNS listener can hang under load while spawning a process” . Note has 2 solutions one is applying patch 4518443 or setting the listener parameter SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> =OFF. We choose the parameter setting option and did not face with the problem again. There is something to consider about the parameter setting option for RAC systems, when you set parameter to OFF, FAN (fast application notification) will not be possible.

PS: Note says this problem can occur on any platform for releases from to

March 27, 2007

Featured blog about application express from an ACE

Filed under: Uncategorized — coskan @ 6:24 am

This morning while loosing myself between the blogger links i found a nice blog which i wish i could find it 2 months ago while i was facing problems with Oracle XE installation. Blogs owner Sergio Leunissen is Oracle ACE and employee of Oracle as a Director of Linux Engineering Team.

If you are interested about Oracle, Application Express, and Linux there are good stuff to read on his blog.

Oracle Database 10g Express Edition (XE) Installation Issues Roundup entry helped me on the causes and solutions of the errors about installing Oracle Database 10g Express Edition.

you can find the solutions of

ERROR: ORA-01041: internal error. hostdef extension doesn't exist
ERROR: ORA-12638: Credential retrieval failed

errors from this blog.

have a nice reading.

March 16, 2007

Thomas Kyte Collections from Robert Vollman

Filed under: Uncategorized — coskan @ 6:47 pm

Today I found a nice blog of a big Thomas Kyte Fan, Robert Vollman.

He listed his all time bests threads and Thomas Kyte style answering classics from the I think you all must read those if you are a bit interested with Oracle World.

Fun With Tom Kyte

40 Tips From Tom

March 14, 2007

Autocommit with JDBC connections

Filed under: Uncategorized — coskan @ 3:33 pm

I hate software development all my IT life. I don’t know why but writing code (not query or simple pl/sql block) never satisfy me. For this reason i try to be far away from programming languages so i can never feel myself free about using code technics in this blog but now i have to give you an important clue about JDBC connections for the developers who works with Oracle (especially for the new ones).

Oracle never uses autocommit for transactions because of its optimistic locking mechanism which is the best point it differs from other RDBMS s which are autocommit by default. But when you use APIs ODBC and JDBC the connection is autocommit by default and this is against the  nature of Oracle.

suppose you have balance table and you are trying to transfer money from account 10 to account 20.

t1 => update balance set balance=balance-1000000 where account_id=10;

t2=> update balance set balance=balance+1000000 where account_id=20;

If you do this update with autocommit feature you take the risk of loosing 1 million dollars at a system fail between the t1 and t2.

normally in Oracle you do this two staments together and commit or rollback after all the transaction is done or fail.

To avoid this situation you must set to off the autocommit option of your JDBC connection

connection conn= DriveManager.getConnection (“jdbc:oracle:oci:database”,”hr”,”hr”);

conn.setAutoCommit (false);

References Used:

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution
(Mr Thomas Kytes Book Pg=272)

The Silver is the New Black Theme Blog at


Get every new post delivered to your Inbox.

Join 193 other followers