Oracle Query - Ora-01652: Unable to Extend Temp Segment But Only in Some Versions of Sql*Plus

oracle query - ORA-01652: unable to extend temp segment but only in some versions of sql*plus

Not really an answer - but a bit more information....

Our local DBAs were able to confirm that the 16Gb (!) TEMP tablespace was indeed being used and had filled up, but only from the Linux clients (I was able to recreate the error making an oci8 call from PHP). In the case of the sqlplus client I was actually using exactly the same file to run the query on both clients (copied via scp without text conversion - so line endings were CRLF - i.e. byte for byte the same as was running on the Windows client).

So the only rational solution was that the 2 client stacks were resulting in different execution plans!

Running the query from both clients approx simultaeneously on a DBMS with very little load gave the same result - meaning that the two clients also generated different sqlids for the query.

(and also Oracle was ignoring my hints - I hate when it does that).

There is no way Oracle should be doing this - even if it were doing some internal munging of the query before presenting it to the DBMS (which would give rise to the different sqlids) the client stack used should be totally transparent regarding the choice of an execution plan - this should only ever change based on the content of the query and the state of the DBMS.

The problem was complicated by not being to see any explain plans - but for the query to use up so much temporary tablespace, it had to be doing a very ugly join (at least partially cartesian) before filtering the resultset. Adding hints to override this had no effect. So I resolved the problem by splitting the query into 2 cursors and doing a nested lookup using PL/SQL. A very ugly solution, but it solved my immediate problem. Fortunately I just need to generate a text file.

For the benefit of anyone finding themselves in a similar pickle:

BEGIN

DECLARE
CURSOR query_outer IS
    SELECT some_primary_key, some_other_stuff
    FROM atable
    WHERE....

CURSOR query_details (p_some_pk) IS
    SELECT COUNT(*), SUM(avalue)
    FROM btable
    WHERE fk=p_some_pk
    AND....

FOR m IN query_outer
LOOP
    FOR n IN query_details(m.some_primary_key)
    LOOP
        dbms_out.put_line(....);
    END LOOP;
END LOOP;

END;

The more I use Oracle, the more I hate it!

PreparedStatement and ORA-01652( unable to extend temp segment)

You're probably running into issues with bind variable peeking.

For the same query, the best plan can be significantly different depending on the actual bind variables. In 10g, Oracle builds the execution plan based on the first set of bind variables used. 11g mostly fixed this problem with adaptive cursor sharing, a feature that creates multiple plans for different bind variables.

Here are some ideas for solving this problem:

Use literals This isn't always as bad as people assume. If the good version of your query runs in 10 seconds, the overhead of hard-parsing the query will be negligible. But you may need to be careful to avoid SQL injection.

Force a hard-parse There are a few ways to force Oracle to hard-parse every query. One method is to call DBMS_STATS with NO_INVALIDATE=>FALSE on one of the tables in the query.

Disable bind-variable peeking / hints You can do this by removing the relevant histograms, or using one of the parameters in the link provided by OldProgrammer. This will stabilize your plan, but will not necessarily pick the correct plan. You may also need to use hints to pick the right plan. But then you may not have the right plan for every combination of inputs.

Upgrade to 11g This may not be an option, but this issue is another good reason to start planning an upgrade.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP but there is 500GB available

That error can also be a sign of a query needing optimization and sucking up space while running inefficiently. We use the optimizer from Quest (Toad) and check for indexes, analyze the explain plan for problem areas to rework, etc. Look for full table scans that can be eliminated with indexes, Cartesian products, etc.

ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend?

Each tablespace has one or more datafiles that it uses to store data.

The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.

To find out if the actual limit is 32gb, run the following:

select value from v$parameter where name = 'db_block_size';

Compare the result you get with the first column below, and that will indicate what your max datafile size is.

I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

--------   --------------------   --------------

   2,048                  8,192          524,264

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you've currrently set the max file size to (which cannot exceed the aforementioned 32gb):

select bytes/1024/1024 as mb_size,
       maxbytes/1024/1024 as maxsize_set,
       x.*
from   dba_data_files x

MAXSIZE_SET is the maximum size you've set the datafile to. Also relevant is whether you've set the AUTOEXTEND option to ON (its name does what it implies).

If your datafile has a low max size or autoextend is not on you could simply run:

alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;

However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:

alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;

Pentaho Spoon transformation throws: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

The transformation is performing a sort or creating a temporary data set for a join that requires TEMP space. Depending on your transformation and the explain plan of the query, it might not even matter if you limit the number of rows you are returning if the transformation must process all of the source data before applying your filter. It is very likely that the only way to get this to run is to increase the size of the TEMP tablespace. You will need to work with your DBA to confirm this and work out a solution.

A query works in SQL*Plus but fails in JDBC with an ORA-00911 exception

The column name statistic# is not the problem.

My bet is that you also send the terminating ; from inside your Java program.

But you may not include the the ; when executing a SQL statement through JDBC (at least not in Oracle and some other DBMS).

Remove the ; from your SQL String and it should be fine.

---

Related Topics