On a semi regular basis I get asked is it allowed to make an adjustment or use an Oracle feature which would make an Oracle installation different from a default configuration? The most recent case involved a question about switching the BLOB column on the WfProcess table to use undo "RETENTION" which is a new feature in 11g instead of using the older (and default) "PCTVERSION" specification (the problem involved getting around a snapshot too old message when exporting the database). My response to this request was that DBA's are allowed and encouraged to make whatever adjustments to the database they think would be beneficial. There are some caveats though:
- Don't touch the data. Any Delete/Update/Insert/Truncate or any other action which modifies the data are not allowed, no exceptions. However things like adding indexes and changing the storage parameters of a segment are allowed.
- Unless extreme care is taken when adjusting data, future problems will result guaranteed. In some rare cases when tables need to be adjusted using SQL, the SQL must come from development. We internally have a policy that any SQL which adjusts data must in all circumstances must originate from development. No exceptions. There are far too many interconnections among objects and exception cases to even begin thinking about removing or adjusting data manually.
- "Select" SQL statements are allowed (but not encouraged)
- PTC Technical Support has expertise to assist with improving Oracle performance of Windchill systems, but for other Oracle problems and issues Oracle support should be contacted.
- Some Oracle features which have a physical component like partitioning or materialized views may or may not require additional steps to be taken at the time of an upgrade to a new version of Windchill, It's hard to know for sure because these features aren't used very much (yet anyway). If these features are necessary; I'd go ahead and use them, and deal with upgrade problems if and when they occur.
DBA's should feel empowered to monitor and make changes to the database (indexes, init changes, advanced Oracle features etc.) and take whatever other steps they think is in the best interest of the system.
