CODEWITHSUNDEEP
oracle-database
lalachka
lalachka

Reputation: 413

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

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?

Upvotes: 0

Views: 1538

Answers (1)

Gary_W
Gary_W

Reputation: 10360

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.

Upvotes: 2

Related Questions