cx_Oracle DatabaseError: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Question

I am using python version 3.6.8. Recently using code I have been using for several months over jupyter notebook, I began getting the above error. The code uses cx_Oracle to connect run several queries. I have never gotten this error before when running these queries, and the queries generally completed within 1 minute.

The code creates a sql query string in the following form (here I use *** to mean the query is actually much longer but I have stripped it to the basics):

def function(d1,d2):
conn=cx_Oracle.connect(dsn="connection")
sqlstr=f"""select *** from table 
          where date between {d1} and {d2}
       """

query1=pd.read_sql(f"""
select * from 
({sqlstr})
""",conn)


*several similar queries**

conn.close()

return [(query1,query2...)]

I think the error likely comes from the string formatting I am doing based on the testing I have done, but I am not sure why it has become an issue now/randomly, when again the code has been working for quite a while now. The sql if I remove the date formatting, runs perfectly fine, and very quickly elsewhere, so that is why the formatting seems to be the issue*.

EDIT*

Even after editing out the string formatting I was still having issues, but I was able to isolate it down to one single query actually--which is actually not** able to run on a direct sql query either so it is likely not a python/string issue but a DB issue (my apologies for the misplaced assurance),

the query is:

select column1,column2,sum(column3)
from
(select several variables,Case When ... as column2
from table1 inner join table2 inner join table3
where several conditions
UNION ALL
select several variables, Case When ... as column2
from table1 inner join table2 inner join table3
where several conditions)
group by column1, column2
having (ABS(sum(column3))>=10000 and column1 in ('a','b','c'))
                or (column1 not in ('a','b','c'))
                order by column1, sum(column3) desc

I assume there must of been some changes on the DB end that would make running this somewhat hefty query currently un-runnable to give the above error? Isolating it further-- it looks like its potentially related to grouping by the CASE When variable column 2

Answer 1

As others have commented this error has to do with TEMP Tablespace Sizing and the workload on the DB at the time. Also, when you get TEMP “blowouts”, it is possible that a particular SQL execution becomes the “victim” of rather than the cause of high temp usage. Sadly, there is no way to predict in advance how much TEMP tablespace a particular workload will require, so getting to the right setting is a process involving incrementally increasing TEMP as per your best estimations; TEMP needs to be big enough to handle peak workload conditions. Having said that you can use the Active Session History [requires diagnostics pack) to find the high TEMP consuming SQL and possibly tune it to use less TEMP. Alternatively, you can use/instrument v$sort_usage to find which SQL are using the most TEMP. The following query can be used to examine the current TEMP tablespace usage:

with sort as 
(
SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr
       , sum(blocks) sum_blocks
FROM v$sort_usage
GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
)
, temp as
(
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total
)
SELECT to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') sample_time
, sess.sql_id
, CASE WHEN elapsed_time > 2*86399*1000000
THEN '2 ' || to_char(to_date(round((elapsed_time-(2*86399*1000000))/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
WHEN elapsed_time > 86399*1000000
THEN '1 ' || to_char(to_date(round((elapsed_time-(86399*1000000))/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
WHEN elapsed_time <= 86399*1000000
THEN to_char(to_date(round(elapsed_time/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
END  as time_per_execution
, sum_blocks*dt.block_size/1024/1024 usage_mb, sort.tablespace
, temp.mb_used, temp.mb_free, temp.mb_total
, sort.username, sess.sid, sess.serial#
, p.spid, sess.osuser, sess.module, sess.machine, p.program
, vst.sql_text
FROM sort,
     v$sqlarea vst,
     v$session sess,
     v$process p,
     dba_tablespaces dt
     , temp
WHERE sess.sql_id = vst.sql_id (+) 
  AND sort.session_addr = sess.saddr (+)
  AND sess.paddr = p.addr (+)
  AND sort.tablespace = dt.tablespace_name (+)
  AND sort.tablespace = temp.tablespace
order by 4 desc
;

When I use the term "instrument" I mean, you can periodically persist the results of running this query so that you can look at a later time to see what was running when you got a TEMP blowout.

Answer 2

In my case there're some select sqls execute very long time, about 10 minutes, but general sql usually can completed in 5 seconds.

and I find in the sql it uses SELECT COLUMN_A||COLUMN_B FROM TABLE_C ... , that is it uses || to concatenate columns, and compare with another table, and another table also use ||, so it may cause Oracle Database execute Table Full Scan and use lots of memory, and ORA-01652 occur.

after change || to general column compare: SELECT COLUMN_A, COLUMN_B FROM TABLE_C ... , it can normally execute, without error.