12.21.2009

Shrink space on a lobsegment of 65G running since more than 5 days

A shrink space operation on a lobsegment of 65G in one of our business critical 2 instance RAC database (10gR2) is running more than five days now. As usual , when I opened a TAR with Oracle support, they coolly informed me that, look mate, you are hitting a bug (5768710), you either need to upgrade the db to v11g or apply a patch (8598989).

The lobsegment contains 8128896 blocks, 1183 extents and the size is around 65GB. We actually truncated the source table and then tried shrinking this lobsegment in order to release the free space.
I did test in development and UAT environments (single instance RAC databases) and it took almost a day and half duration to complete the task. However, the following get stuck on the production:

ALTER TABLE tablename MODIFY LOB (column_name)(SHRINK SPACE) nologging parallel 5;

Due to the bug, I am unable to predict how long this operation would take as no information is listed in  v$session_logops dynamic view as well. Unfortunately, Oracle support too can't confirm me when would this operation finishes. The only saving  point to me is that the other sessions working on the table works fine. Neither there is any performance impact nor any excessive redo generation causing.
Upon analyzing my trace files, 10046 and oradebug short_stack, Oracle support confirms that the operation is not hanged, it is progressing.
Keeping in view about no impact on the database, they requested me to leave it running as killing session may trigger SMON recovery on this table.

My boss says, whatever I am handling ending up hitting the bug, one of very rare unfortunately days of my life.


Happy reading,

Jaffar