11g purge scripts growth

History of purge scripts and its portability

1st version of purge scripts came only in PS1 or 11.1.1.2.0 release this had purge based on instance state, composite name, and version


2nd version of purge scripts came only in PS2 or 11.1.1.3.0 release. for the 1st time Full partition concept was introduced


3rd version of purge scripts was available in PS3 or 11.1.1.4.0 release it was know as " purging for Extremely large environments support"
Purge script was changed and made it very simple. partition purge concept was also supported

this script was not compatible to work on older patch sets(PS1 amd ps2) as there was change in the way the scripts works.
So there is backport_scripts which would work fine on PS2 and PS1 was developed


4th version no enhancement were made available in PS4 or 11.1.1.5.0 release.


5th version more enhancement were made available in PS5 or 11.1.1.6.0 release. this includes

Partial Partitioning of Component tables
Purge By Composite DN
Partitioning Strategy for long running processes introducing "Row Movement strategy "
NOTE: there is no backport scripts available for this so far

java.sql.SQLException: ORA-01654: unable to extend index DEV3_SOAINFRA.COMPOSITE_INSTANCE_STATE by 8 in tablespace DEV3_SOAINFRA[[

if you are hitting anything like the below issues or similar to the below issues then this may be because of dbf file size issues

Issues:
java.sql.SQLException: ORA-01654: unable to extend index DEV3_SOAINFRA.COMPOSITE_INSTANCE_STATE by 8 in tablespace DEV3_SOAINFRA[[

oracle.toplink.exceptions.DatabaseException[[
Internal Exception: java.sql.BatchUpdateException: ORA-01691: unable to extend lob segment DEV3_SOAINFRA.SYS_LOB0000129222C00018$$ by 8 in tablespace DEV3_SOAINFRA

Solution: to extend datafile/dbf file size by 4gb
ALTER DATABASE DATAFILE ‘db1300/oradata/db1300/DB1300_soainfra.dbf’ RESIZE 4000M;

NOTE: make sure you take a backup of dbf files before executing above alter command

if the file got corrupted then u may have to recreat the file then use bleow command to recreate it

ALTER TABLESPACE DEV3_SOAINFRA ADD DATAFILE ‘db1300/oradata/db1300/DB1300_soainfra.dbf’ SIZE 4000M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;


refernce to some of the good material
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3002.htm

http://docs.oracle.com/cd/B10500_01/server.920/a96521/tspaces.htm

http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml