Comparing your Oracle databases
October 26, 2015 Leave a comment
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.