ORA-1652 ORA_TEMP_%_DS_% Used Too Much Temp Space After Setting ESTIMATE_PERCENT For A Large Table
(Doc ID 2397608.1)
Last updated on NOVEMBER 07, 2019
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterOracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- After setting ESTIMATE_PERCENT to 20 for a very large table ORA-1652 occurred when gathering stats:
exec dbms_stats.set_table_prefs(ownname => '<username>',tabname => '<table_name>',pname => 'ESTIMATE_PERCENT',pvalue => 20)
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"<USER>"','"<TAB_NAME>"','""', ...)
DBMS_STATS: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
DBMS_STATS: GATHER_STATS_JOB: Stopped by Scheduler. - Further investigation revealed that a SQL which queried against ORA_TEMP_%_DS_% uses too much temporary space; however before explicitly setting ESTIMATE_PERCENT, this error didn't occur (With AUTO sample size, it trends to use 100% sample size, so it is very curious that problem didn't occur when using a higher sample size).
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |