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

0
votes

I keep getting this error "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP" but the DBA tells me there's over 500GB available.

While what I'm trying to run will return a few million rows, I have queries that create 100M rows run successfully.

what can be the cause of this one failing if there's so much free space available? The DBA doesn't seem to know what to do.

Upd. This was solved in comments, turned out my schema only had 60GB. Seems to work now. But I'm now confused, why isn't 60GB enough to run a query that will produce a few million rows. All tables in my schema add up to 1GB.

Also, when they say that I have 60GB available, is that at the time of the run?

1
oracle
Possibly the DBA is looking at the CDB rather than the PDB (vice versa), i.e. you are both seeing different TEMP tablespaces. – TenG
Maybe the TEMP tablespace is not set to auto-extend and has reached its maximum size. – a_horse_with_no_name
Then, post the result of select file_name, AUTOEXTENSIBLE, BYTES, MAXBYTES, INCREMENT_BY from dba_data_files where tablespace_name='TEMP' on both CDB and PDB, then also do a df -h on the file systems (or equivalent on ASM if using that). – TenG
The TS having 500GB available when you check it (after the failed process) does not mean it had 500GB at the point the process failed. When processes terminate (fail or normal) they release the space they were using. And the fact that you have queries that successfully return more rows doesn't mean anything. Perhaps your failing query is working with more rows than are finally returned, doing more sorting and/or joining. – EdStevens
Number of rows is pretty meaningless. I could have a million rows of two integers take up less space than a single row that contains a single CLOB. The average width of your rows is as important as the number of rows. – Brian Leach

1 Answers

1
votes

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.