Sean, I've checked on the support web site and developer network for the "RemedyAndOracleCLOBS.pdf". Could you point me to where you found the document? Thanks, Dave ________________________________
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Garrison, Sean (Norcross) Sent: Tuesday, October 28, 2008 10:49 AM To: [email protected] Subject: Re: LOB Space Reclaim Issue ** I have not had your exact issue but we were able to reclaim our db by running a script located in the "RemedyAndOracleCLOBS.pdf" document. It details how to reclaim space and gives an oracle stored procedure you can use to change all lob storage to "In-Row" from "Out of Row". By running the procedure our db size was reduced from 43 gig to less than 8 gig successfully. We did not run into the same issues you are having. We are on Oracle 10g patch 10.2.0.3.0 64 bit. Thanks, Sean From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Do Duy Sent: Tuesday, October 28, 2008 1:10 AM To: [email protected] Subject: LOB Space Reclaim Issue ** Hi, I just wanted to find out if anyone has had this problem and possibly how the issue was resolved. Details of the problem is below. Thank you in advance, Duy Do Broadcast Australia Database Version Database is on Oracle 10G 10.2.0.2.0 Standard Edition on Sun Solaris. Operation done Database is growing, so after the deletes in Development database, we tried Space reclaim of the tables containing LOB segments by running 'alter table table_name enable row movement' and 'alter table table_name shrink space cascade'. Space Reclaim was successful. Observed Problem But after that, the read access to the database Lobs gave "ORA-1555 Snapshot too old" error messsage. Patch Apply Found & Applied Patch 5636728 which was to address "FALSE ORA-1555 WHEN READING LOBS AFTER SHRINK", but it didn't help. Later Steps Finally, we had to restore the original Dev database from the backup. Fortunately this test was done on the Development database with the intention of doing it on Production later. Currently the DB is on Oracle 10.2.0.2.0, latest patchset 10.2.0.4 will be applied at some point in time as per the compatibility with the Application. But the patch 5636728, which was to address false Ora-1555 after LOB shrink, was applied successfully. But the ORA-1555 problem remained. Error showed up when Action Remedy application tried to access the data after the LOB space shrink. Even export gave the same error. The Original Dev database as per before the Space shrink was restored from the backup. Frequency of the error Consistently Impact to the Business Production database is growing and we need to be able to reclaim space We still have the problematic database copy with the LOBs shrunk, so any diagnostic scripts or actions as advised can be run there. ************************************************************************ ************************************************************************ ******************************* IMPORTANT NOTICE This email and any attachments may be confidential and/or privileged, in which case neither is intended to be waived. If received in error, please contact us and delete all copies. Before opening or using attachments, check them for viruses and defects. Our liability is limited to resupplying any affected attachments. __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
