Stats on Huge Partitioned Table and ORA-01555

From: Michael Cunningham <>
Date: Tue, 26 May 2015 11:28:45 -0700
Message-ID: <>

Oracle with UNDO 30G, undo_retention=900

Table has 7.1 billion+ rows, 503 partitions, 3TB

I am relatively new to partitioned tables and am getting ORA-01555 errors during execution of dbms_stats.gather_database_stats_job_proc.

The alert log shows something like (I hope this is helpful)

/* SQL Analyze(0) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monito
ring xmlindex_sel_idx_tbl no_substrb_pad */to_char(count(

Question: Is there a better way to gather stats on this table? Last time this happened I set the table stats manually (so they would not show stale), but now I need to fix this properly with a permanent solution.

P.S. I can increase UNDO - which I'm willing to do - but I'm also trying to learn if there is a better way to solve this.



Michael Cunningham

-- Received on Tue May 26 2015 - 20:28:45 CEST

Original text of this message