Partitioned table space stats ... Incremental question and TEMP tablespace usage...

From: David Mann <>
Date: Thu, 22 Mar 2012 11:12:00 -0400
Message-ID: <>

  1. I have been steeped in a bunch of Incremental Stats blog posts for the past week and I think I am just confusing myself at this point.

If I want to use Incremental stats on my Partitioned tables, I am under the impression I need to use AUTO_SAMPLE_SIZE. Is this accurate?

Is it not possible to use a custom Sample Size in conjunction with Incremental Stats? Or is it possible but my synopses will be less accurate?

2) Reason I am asking is that I have a table that I can't get stats on... keeps erroring out so I am trying to figure out how I can get a successful stats run on it. I would like to try reducing the sample size by changing from Auto to a small percentage of the table size.

This is a generic (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:


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

I am still a newbie on stats for partitioned tables... Seems odds that a 15gb table requires so much (64gb) of temp while gathering. Not sure if I am hitting a bug. Metalink searches sent me on some wild goose chases. Any suggestions before I request a swath of disk space for more TEMP files appreciated as I have to get this worked out before I move on to the larger tables in this schema (50gb+).


Dave Mann - Database Stuff -
Received on Thu Mar 22 2012 - 10:12:00 CDT

Original text of this message