Comparing your Oracle databases

This article suggests an approach to compare your development, test and production databases using SQL scripts executed from a Servlet.

This article appeared first on techtavern.wordpress.com

Create a Servlet that produces following SQL statements as a text report: (replace **** with a proper value applicable to your database).

  • Tables, sequences and views: SELECT OBJECT_TYPE, OBJECT_NAME, STATUS FROM ALL_OBJECTS WHERE OWNER = ‘****’ ORDER BY OBJECT_TYPE, OBJECT_NAME
  • Columns of each table: SELECT TABLE_NAME, COLUMN_NAME, QUALIFIED_COL_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH, CHAR_LENGTH, CHAR_USED FROM ALL_TAB_COLS WHERE OWNER = ‘****’ ORDER BY TABLE_NAME, COLUMN_NAME
  • Constrains of each table: SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, SEARCH_CONDITION, R_CONSTRAINT_NAME, DELETE_RULE, DEFERRABLE, DEFERRED, VALIDATED, GENERATED FROM ALL_CONSTRAINTS WHERE OWNER = ‘****’ ORDER BY TABLE_NAME, CONSTRAINT_NAME
  • Sequences: SELECT SEQUENCE_NAME, INCREMENT_BY, MIN_VALUE, MAX_VALUE, CACHE_SIZE, CYCLE_FLAG, ORDER_FLAG FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = ‘****’ ORDER BY SEQUENCE_NAME

For security reasons, you may prefer to output them into the application log.

You may request these reports on each of your environments and compare them side-by-side, for example, using Beyond Compare.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: