Oracle error
ORA-01652
ORA-01652: unable to extend temp segment by %s in tablespace %s
What usually causes it
A sort, hash join or temporary table needed more temp space. If the message names a permanent tablespace, it came from an index build or CREATE TABLE AS SELECT.
What to check first
Find the session and SQL using temp; a bad plan is often the real cause. Add a tempfile only after that.
Scripts that help
- Temporary tablespace usageSize, allocated and free space for each temporary tablespace.
- Sessions using temp spaceWho is using temp right now and for what (sort, hash, LOB), largest first. Run it when you see ORA-01652.
- Add space: resize or add a datafileTemplates for the three usual fixes. The resize runs as written; the other two are commented out. Change names and sizes, and uncomment the one you…
Tool
- Tablespace runway and datafilesDays until full, and the statements to add space