Outside of Greg's very good question, I also have one- Are there any indexes on this partitioned table? I just took a quick scan through your email, but if you have chosen "TRUE" on CASCADE and you have global indexes on the partitioned table, that could easily explain the temp issue. I don't see anything specifying if there are any indexes or not, but thought I'd ask... :) Kellyn Pot'Vin Senior Technical Consultant Enkitec DBAKevlar.com ________________________________ From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> To: dmann99@xxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Sent: Thursday, March 22, 2012 9:34 AM Subject: Re: Partitioned table space stats ... Incremental question and TEMP tablespace usage... What sql statement (from dbms_stats) is the one spilling to temp and failing? Gathering table stats should not spill to temp with AUTO_SAMPLE_SIZE. This is because there is no sort in 11g when using AUTO_SAMPLE_SIZE due to the fast NDV algorithm. If you switch to a percent, it will require a sort and may spill to temp as well. FYI - only the first (incremental) set_table_prefs is required -- the other two are the defaults already. On Thu, Mar 22, 2012 at 8:12 AM, David Mann <dmann99@xxxxxxxxx> wrote: > This is a generic 11.1.0.7 (straight install with no patches) on Solaris > 64-bit > > Table MYSCHEMA.MYTABLE has 42 partitions, 15 gb across all partitions, > largest partition is 1.75gb, but most are in the 300mb range. > > I have 64gb temp space defined for this database, and no one else is > logged on. > > I have the table stats preferences configured like this: > > EXEC DBMS_STATS.set_table_prefs('MYSCHEMA', 'MYTABLE', 'INCREMENTAL', > 'TRUE'); > EXEC DBMS_STATS.set_table_prefs('MYSCHEMA', 'MYTABLE', 'GRANULARITY', > 'AUTO'); > EXEC DBMS_STATS.set_table_prefs('MYSCHEMA', 'MYTABLE', > 'ESTIMATE_PERCENT', DBMS_STATS.AUTO_SAMPLE_SIZE); > > And then execute the following: > EXEC DBMS_STATS.gather_table_stats('MYSCHEMA', 'MYTABLE',DEGREE=>4); > > The stats gathering chugs along and after about 45 minutes it fails with: > ERROR at line 1: > ORA-01652: unable to extend temp segment by 128 in tablespace TEMP > ORA-06512: at "SYS.DBMS_STATS", line 18408 > ORA-06512: at "SYS.DBMS_STATS", line 18429 > ORA-06512: at line 1 > > -- Regards, Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <http://bit.ly/v733dJ> | linkedin <http://linkd.in/gregrahn> -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l