Database Reference
In-Depth Information
For applications that have already been released into a production environment, the
process can be much more complex. You need to take into account the version that is in
production and how the underlying database structure may differ from the version
you've created in development and are ready to deploy.
Luckily there are tools available to help identify the differences between two schem-
as. These tools can also generate the necessary DDL scripts to implement the differen-
ces.
However, the unfortunate truth is, although the APEX SQL Workshop utilities do
include a schema-compare tool, it has some severe limitations. For one, both schemas
that are being compared must be available from the same workspace. This isn't pos-
sible if your production schema exists on a separate server, as it often does. The second
limitation is that the APEX-based comparison tool identifies the objects that are differ-
ent, but it doesn't say how they're different, nor does it generate the DDL that would
be required to synch up the schemas.
For this type of functionality, you have to rely on external programs or scripts. The
following list mentions a number of options, all of which can generate the scripts re-
quired to synchronize the production environment's database objects structure with the
changes you may have introduced in development:
•
SQL Developer
: Oracle's own product can run a full schema comparison
between two separate schemas on separate servers and generate a script that
synchronizes one schema with another. Older versions of this tool suffered
from some problems, but as of SQL Developer version 3.2 the comparison
engine has been significantly upgraded and the generated scripts are solid.
•
Oracle Enterprise Manager
: If you have the Change Management Pack
and Oracle Enterprise Manager (OEM), then you can compare schemas and
generate a synchronization script. However, developers are very rarely giv-
en access to OEM because it's more of a database administration tool and
would potentially give developers access to several sensitive utilities they'd
rather us not have access to.
•
Schema Compare for Oracle
: Red Gate Software has taken its extensive
experience in creating tools for the SQL Server market and turned its at-
tention to the Oracle database market. The result is a tool that allows you
to compare, view, and generate synchronization scripts between two Oracle
schemas. This is probably the best third-party tool on the market, but the
one downside is that it only runs on Windows.