How to avoid ORA-01652 after executing "alter table compress..."

Strange thing happened to me few months ago on one of our biggest databases.
DB Oracle 10.2.0.2.0 on Solaris 9 (64-bit).

This database contains several range partitioned tables where date column is partitioning key. During my regular maintenance tasks I usually compress old partitions to save up disk space and speed up querying.

For some time everything was working smoothly without any ORA errors or such problems.
But several months ago I experienced strange Oracle behavior. I was executing the same commands as I did for a quite time but now my compress operation suddenly failed.



I've checked alert log to see what happened and there I found:

Errors in file /opt/oracle/admin/db/bdump/db_p000_18580.trc:
ORA-01652: unable to extend temp segment by 16384 in tablespace DATA_2007_10_ARCH
Mon Jul 13 14:55:38 2009
Errors in file /opt/oracle/admin/db/bdump/db_p000_18580.trc:
ORA-07445: exception encountered: core dump [kghssgdmp()+176] [SIGFPE] [Integer divide by zero] [0x10401C670] [] []
ORA-01652: unable to extend temp segment by 16384 in tablespace DATA_2007_10_ARCH
Mon Jul 13 14:55:38 2009
Errors in file /opt/oracle/admin/db/bdump/db_p000_18580.trc:
ORA-07445: exception encountered: core dump [kghssgdmp()+176] [SIGFPE] [Integer divide by zero] [0x10401C670] [] []
ORA-07445: exception encountered: core dump [kghssgdmp()+176] [SIGFPE] [Integer divide by zero] [0x10401C670] [] []
ORA-01652: unable to extend temp segment by 16384 in tablespace DATA_2007_10_ARCH
Mon Jul 13 14:55:38 2009
Errors in file /opt/oracle/admin/db/bdump/db_p000_18580.trc:
ORA-07445: exception encountered: core dump [kghssgdmp()+176] [SIGFPE] [Integer divide by zero] [0x10401C670] [] []
ORA-07445: exception encountered: core dump [kghssgdmp()+176] [SIGFPE] [Integer divide by zero] [0x10401C670] [] []
ORA-07445: exception encountered: core dump [kghssgdmp()+176] [SIGFPE] [Integer divide by zero] [0x10401C670] [] []
ORA-01652: unable to extend temp segment by 16384 in tablespace DATA_2007_10_ARCH
Mon Jul 13 14:55:45 2009
Errors in file /opt/oracle/admin/db/bdump/db_p002_18584.trc:
ORA-01652: unable to extend temp segment by 16384 in tablespace DATA_2007_10_ARCH
Mon Jul 13 14:55:46 2009
Errors in file /opt/oracle/admin/db/bdump/db_p002_18584.trc:
ORA-07445: exception encountered: core dump [kghssgdmp()+176] [SIGFPE] [Integer divide by zero] [0x10401C670] [] []
ORA-01652: unable to extend temp segment by 16384 in tablespace DATA_2007_10_ARCH
Mon Jul 13 14:55:46 2009
...
[CUT]
...

"ORA-01652: unable to extend temp segment by 16384 in tablespace DATA_2007_10_ARCH" was the problem.

Tablespace was created with this command:

CREATE TABLESPACE DATA_2007_10_ARCH DATAFILE '/data2/data_2007_10_01_arch.dbf' SIZE 8193M
AUTOEXTEND ON NEXT 128M MAXSIZE 30721M LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL FLASHBACK ON;

Expected size of tablespace DATA_2007_10_ARCH was about 13 GB and autoextend option was set to 30 GB so I thought there should be enough space for temporary segments.
As I said before SAME command worked before. Same sizes, same options, same database version, same parameters and machine - but now I receive error?!

This both commands failed:

-- with parallel option
ALTER TABLE user.data PARTITION DT_2007_10 TABLESPACE 
DATA_2007_10_ARCH COMPRESS PARALLEL 4;

-- without parallel option
ALTER TABLE user.data PARTITION DT_2007_10 TABLESPACE 
DATA_2007_10_ARCH COMPRESS;

As I was receiving errors constantly I created SR and asked support for help. After exchanging several notes with Oracle analyst the answer was that this is bug and I have to apply patch:

Later I asked:
Q: If this is known bug for 10.2.0.2.0 version, why everything worked before few months?
A: not all bugs show themselves right away -- in many cases we see that a given bug first manifests after long time (or never). What triggers a given bug is that we traverse down the Oracle code path where the bug is; we may not have used the buggy part of the code layer until now, but e.g. changes in parameters, increased size of DB, statistics etc. will cause that we begin to use the parts of the code where the bug then 'lurks' ...


So the only solution was to patch this frequent database which wasn't so easy task because it requires quite preparation.


In the meantime one thing came on my mind.

Even if my tablespace was extensible to 30GB Oracle fails when it is needed to extend datafile during compress operation.

So I created tablespace again but this time with initial size of 15GB instead 8 GB to avoid extending datafile.

CREATE TABLESPACE DATA_2007_10_ARCH DATAFILE '/data2/data_2007_10_01_arch.dbf' SIZE 15361M
AUTOEXTEND ON NEXT 128M MAXSIZE 30721M LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL FLASHBACK ON;

And guess what - my compress operation completed successfully.

I don't know why Oracle fails when it is supposed to grab more disk space to extend datafile, but I am happy that problem is solved without applying patch and now I can continue with regular maintenance.