Oracle version control tips
Oracle Database Tips by Donald BurlesonFebruary 4, 2015
Proper version control is essential
for all information systems and mainframe database management
have used version control for decades. In the CODASYL
Network database IDMS, change control was built-in to the data
dictionary. All fields (columns) were versioned, and all
records (tables) could be stored with specific version numbers.
Today, the scope of version control
has expanded, partly due to Oracle extension of the base data
management functions to include working code (PL/SQL stored
procedures and functions).
Oracle version control is
an industry best practice
In a real-world production
database, change control and version control are an absolute
necessity, especially for dynamic databases where the data
structures are continuously changing.
||BC has Oracle Certified Professionals who can develop customized
version control procedures for any mission-critical database.
Unfortunately, Oracle 11g does not
yet provide integrated version control for objects (tables,
indexes, columns, &c), but there are 3rd party consultants who
have proprietary tools that you can use to implement a
certifiable change control procedure for Oracle.
What can Oracle version
control do for me?
Besides the obvious necessity of
ensuring that all database objects are in-sync for each
production release, implementing version control for database
objects allow the DBA to perform temporal database management,
restoring the schema to a specific point in time. The DBA
can also use version control to perform reverse engineering and
What Oracle components
need version control?
There are many areas of Oracle
databases that require version control, an absolute necessity
for any production shop that makes changes to their schema and
Database objects - All schema objects
including indexes, columns and tables.
Process code - All PL/SQL stored
procedures and functions.
Procedural language code - All Apex
applications, Pro*Cobol, Pro*C and other pre-compiled languages should be
under the check-in, check-out of a version control system.
External entities - All Oracle parameter
files (init.ora, sqlnet.ora, protocol.ora, listener.ora) and all
scheduling crontab files should be under version control. Also, all
server files (/etc/passwd) might have version control.
Before exploring 3rd party version
control solutions, let's take a look at the Oracle vendor
The Oracle Change
Oracle offers an extra cost
change management pack (CMP) to compete with 3rd party offerings, but
it's critical to note that CPM IS NOT integrated into the Oracle
Oracle claims that their CMP allow
the DBA to "evaluate, plan, and implement database schema
changes. Using the Change Management Pack, administrators can
investigate and track changes, compare and synchronize objects
and schemas, modify schema objects, evaluate the change impact,
and if required undo changes that have been made previously.".
However, because of the loosely-coupled integration and more
mature strap-on version control packages, many Oracle shops
choose less expensive version control solutions.
A review of Oracle change control
There are a variety of third-party Oracle change control and
version control tools
source tools such as SCCS (Source Code Control System) for Oracle.
This is superseded by the Gnu CSSC
version control software.
SQL Developer also has an interface for third-party change control and
(Serena) version control for Oracle
( Formally Kintana) for Oracle revision, change control and Oracle version
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.