STATSPACK.SNAP is slow and got ORA-1652 ON 11.1

This is related to bug BUG:8323663/8226792 in 11.1 and Oracle support has confirmed that this is fixed in 11.2. They have not provided any fix in 11.1.

This is also related to statspack.snap taking longer time to run.

However, if you are stuck with 11.1 database, you can use the following solution as a fix to this issue:

1. Connect to the database as sys
2. Recreate the following view with the below command:
create or replace view STATS$V_$FILESTATXS as
select /*+RULE */ ts.name tsname
, df.name filename
, fs.phyrds
, fs.phywrts
, fs.readtim
, fs.writetim
, fs.singleblkrds
, fs.phyblkrd
, fs.phyblkwrt
, fs.singleblkrdtim
, fw.count wait_count
, fw.time time
, df.file#
from x$kcbfwait fw
, v$filestat fs
, v$tablespace ts
, v$datafile df
where ts.ts# = df.ts#
and fs.file# = df.file#
and fw.indx+1 = df.file#;

3. Run the statspack.snap procedure and it should run quicker with errors.

Enjoy...