DBAs will usually have a range of
responsibilities, including managing growth and change (capacity planning) and
performance monitoring and tuning of the database and application.
In an application development environment, the DBA’s job may also include responsibility for the design or enhancement of the data model.
This may legitimately lead the DBA to want to adjust the physical
properties of existing objects. Common reasons include the following:
•
Add, remove, or change an index.
•
Move an object to a different tablespace.
•
Change the physical parameters (PCTUSED, PCTFREE,
etc.).
Consider what happens if the DBA
adjusts a table that is then altered by a PeopleSoft patch or some other
customization. The upgrade process will include generating and applying a
script to alter the table structure, usually by re-creating it.
If the new parameters introduced by
the DBA are not placed in the Application Designer, altering the table will
effectively erase the work performed by the DBA. Re-creating the table with the
original parameters may reintroduce the original problem, or it may cause an
error during the upgrade.
If the DBA moves
a growing table to a larger tablespace, and that table continues to grow beyond
the capacity of the old tablespace, then if that table is re-created by a
PeopleSoft ALTER script, it will be re-created in the original tablespace. It
will not be possible to repopulate the upgraded table with the application
data, and the ALTER script will fail. Worse still, if that space management
error is not caught immediately, it is possible to lose data if the script goes
on to drop the original table. If the new tablespace was specified in
PeopleTools, then not only would the error not occur during the upgrade, but
the DBA wouldn’t have had to write the script to move the table in the first
place. It could have been generated by the Application Designer.
Therefore, it is very important that
object-level changes that the DBA makes to tables and indexes should at least
be reflected in the Application Designer, and preferably they will be made in
the Application Designer in the first place.
At some PeopleSoft customer sites, the scripts that are
produced by the Application Designer must be reviewed by a DBA before they are
applied. The DBA then edits them to conform to a local “standard.” While it may
be appropriate to have the DBA apply the DDL scripts, the sheer number of
PeopleSoft tables and views makes this approach virtually impossible and
introduces further opportunity for human error. I would suggest that it is
better to edit the PeopleSoft DDL models, and set appropriate DDL model
defaults and overrides, and restrict manual editing to Oracle features that the
Application Designer cannot handle.
