Geek Answers Handbook

Using Schema Compare for Oracle to create a deployment script

We have a large (2000+) set of scripts used to deploy database code. Does anyone know if there is a tool that can be used to create a single deployment script that matches the correct order of dependency?

I understand that this can be done using Redgate SQL Compare, however this is great if you have SQL Server. Of the limited information about the Schema Compare for Oracle tool, it does not have this feature.

+4
source share
4 answers

I would go with the following order (mainly based on the fact that objects are extracted using EXPDP):

  • SYNONYM
  • TYPE (table column may depend on user-defined type)
  • DB_LINK
  • SEQUENCE
  • TABLE TABLE
  • TABLE GRANT
  • TABLE-INDEX
  • TABLE-CONSTRAINT (you may need a PK constraint introduced by an existing index)
  • TABLE COMMENTS
  • PACKAGE
  • Function
  • Procedure
  • VIEW
  • CONTROLLED TABLES
  • TABLE-TRIGGER
  • TABLE-INDEX (FUNCTIONAL)
  • MATERIALIZED VIEW
  • INTERACTIONAL VIEW MATERIALS
  • Job
+3
source

I would create a script to run your scripts in the correct order, depending on how they are implemented.

  • Tables / Sequences
  • Species / Types
    • (usually with the force option, so that they are created if there is a generation order of the problem or dependence on the types of "code" or other types and types).
  • Packages / Procedures / Functions / Triggers
    • (if they are created from the order of dependencies, they will still be created, but will be invalid)
  • Foreign Key Constraints

Remove rdbms / admin / utlrp to recompile invalid objects when you finish recompiling invalid objects.

+2
source

I'm not sure I fully understood your problem, but you can try the following:

  • Run your current scripts to create the database
  • Create an empty schema
  • Use Red Gate Schema Compare to compare your database with an empty schema.
  • Save the script (which should be in the order of dependency)
+1
source

The following is a general guide on how to run installation scripts for various types of database objects:

 Package specifications Tables (with constraints and indexes) in proper order Sequences (because they are most often used by triggers) Triggers Synonyms Views (because they may reference functions, procedures, or synonyms) Package bodies Data (optionally disabling all constraints before loading the data and re-enabling them afterwards)

Package specifications are listed first because they will always be valid, and other objects can reference them. Packaging bodies must be the last type of object created because they are likely to refer to other types of objects. Due to dependency issues, you are encouraged to put functions and procedures in packages.

0
source

More articles:


All Articles