Skip to Content
0
Former Member
Apr 07, 2008 at 07:59 PM

Oracle Database Reorganization

443 Views

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

System: Microsoft Windows Server 2003 Enterprise Edition

-


Hello everyone. I have read all the threads on this forum concerning Oracle database reorganization and a few SAP notes and I have a few questions. I guess my first question is how many of you actually do a database re-org? If you do how many times do you run a re-org on a, daily, bi-weekly, monthly, yearly? Believe or not I actually do a database online table re-org on all my servers monthly using BRSPACE on our main tablespaces (in R/3 production this would be PSAPDAT tablespace) . Also, on my production servers I generate a report on the top DB objects identified through transaction ST14/Basis & Others. I re-org these objects using a third party tool from Quest called Space Manager (with LiveReorg) daily.

We find that we can better control our freepace on the actual tablespace specified. For example if PSAPDAT tablespace is showing 99.8% freespace used, running a re-org will some time drop this down to 90.5% or even lower.

I have found a good article Boost SAP R/3 Performance by Reorganizing Your Oracle Database: A Proven Reorganization Strategy by Charles Davis. This article appeared in the July/Aug 2005 issue of SAP Professional Journal.

http://www.quest.com/Quest_Site_Assets/News/SAPPro_JulAug05_LiveReorg.pdf.

The only problem that I am running into when a do a monthly re-org on my R/3 environment using BRSPACE is that it’s taking way to long to complete. This is after BRSAPCE skips all tables having LONG (RAW) columns. My last re-org on R/3 took 14 hours to complete. So now I am wondering if it’s worth doing my monthly re-org or just keep my daily re-org going on the few top tables identified through ST14. Your feedback would be greatly appreciated.

Regards

Billy