ORA-1652 ORA_TEMP_%_DS_% Used Too Much Temp Space After Setting ESTIMATE_PERCENT For A Large Table
Last updated on MAY 17, 2018
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168 to 22.214.171.124 [Release 11.2 to 12.2]
Information in this document applies to any platform.
- 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).
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms