A circuit comparison tool is a good idea. A database schema is much more complex than most people give credit, and every difference between two database schemas can cause errors.
If you still want to do it yourself, the best approach I have found is to extract the schema definitions into text, and then run a text comparison. While everything is sorted alphabetically, you can use the Compare Documents feature in Microsoft Word (or FC.EXE, DIFF, or its equivalent) to highlight the differences.
The following SQLPlus script prints the schema definition in alphabetical order to allow comparison. There are two sections. The first section shows each column in the format:
table_name.column_name: data_type = data_default <nullable>
The second section lists indexes and restrictions:
PK constraint_name on table_name (pk_column_list) FK constraint_name on table_name (fk_column_list) CHECK constraint_name on table_name (constraint_definition)
The script serves as a useful reference for extracting some details of the Oracle schema. This can be good knowledge when you are on client sites and you don’t have the usual tools available or when security policies prevent access to the client’s site database directly from your PC.
set serveroutput on; set serveroutput on size 1000000; declare rowcnt pls_integer := 0; cursor c_column is select table_name, column_name, data_type, data_precision, data_length, data_scale, data_default, nullable, decode(data_scale, null, null, ',') scale_comma, decode(default_length, null, null, '= ') default_equals from all_tab_columns where owner = 'BCC' order by table_name, column_name; cursor c_constraint is select c.table_name, c.constraint_name, decode(c.constraint_type, 'P','PK', 'R','FK', 'C','CHECK', c.constraint_type) constraint_type, c.search_condition, cc.column_1||cc.comma_2||cc.column_2||cc.comma_3||cc.column_3||cc.comma_4||cc.column_4|| cc.comma_5||cc.column_5||cc.comma_6||cc.column_6||cc.comma_7||cc.column_7 r_columns from all_constraints c, ( select owner, table_name, constraint_name, nvl(max(position),0) max_position, max( decode( position, 1, column_name, null ) ) column_1, max( decode( position, 2, decode(column_name, null, null, ',' ), null ) ) comma_2, max( decode( position, 2, column_name, null ) ) column_2, max( decode( position, 3, decode(column_name, null, null, ',' ), null ) ) comma_3, max( decode( position, 3, column_name, null ) ) column_3, max( decode( position, 4, decode(column_name, null, null, ',' ), null ) ) comma_4, max( decode( position, 4, column_name, null ) ) column_4, max( decode( position, 5, decode(column_name, null, null, ',' ), null ) ) comma_5, max( decode( position, 5, column_name, null ) ) column_5, max( decode( position, 6, decode(column_name, null, null, ',' ), null ) ) comma_6, max( decode( position, 6, column_name, null ) ) column_6, max( decode( position, 7, decode(column_name, null, null, ',' ), null ) ) comma_7, max( decode( position, 7, column_name, null ) ) column_7 from all_cons_columns group by owner, table_name, constraint_name ) cc where c.owner = 'BCC' and c.generated != 'GENERATED NAME' and cc.owner = c.owner and cc.table_name = c.table_name and cc.constraint_name = c.constraint_name order by c.table_name, decode(c.constraint_type, 'P','PK', 'R','FK', 'C','CHECK', c.constraint_type) desc, c.constraint_name; begin for c_columnRow in c_column loop dbms_output.put_line(substr(c_columnRow.table_name||'.'||c_columnRow.column_name||': '|| c_columnRow.data_type||'('|| nvl(c_columnRow.data_precision, c_columnRow.data_length)|| c_columnRow.scale_comma||c_columnRow.data_scale||') '|| c_columnRow.default_equals||c_columnRow.data_default|| ' <'||c_columnRow.nullable||'>',1,255)); rowcnt := rowcnt + 1; end loop; for c_constraintRow in c_constraint loop dbms_output.put_line(substr(c_constraintRow.constraint_type||' '||c_constraintRow.constraint_name||' on '|| c_constraintRow.table_name||' ('|| c_constraintRow.search_condition|| c_constraintRow.r_columns||') ',1,255)); if length(c_constraintRow.constraint_type||' '||c_constraintRow.constraint_name||' on '|| c_constraintRow.table_name||' ('|| c_constraintRow.search_condition|| c_constraintRow.r_columns||') ') > 255 then dbms_output.put_line('... '||substr(c_constraintRow.constraint_type||' '||c_constraintRow.constraint_name||' on '|| c_constraintRow.table_name||' ('|| c_constraintRow.search_condition|| c_constraintRow.r_columns||') ',256,251)); end if; rowcnt := rowcnt + 1; end loop; end; /
Unfortunately, there are several limitations:
- Nested carriages and spaces in data_defaults and checks for constraint definitions can be highlighted as differences, even if they have zero effect on the circuit.
- Does not include alternate keys, unique indices, or performance indices. To do this, you need the third SELECT statement in the script directory, referencing the views all_ind_columns and all_indexes.
- Does not contain security information, synonyms, packages, triggers, etc. Packages and triggers are best compared using an approach similar to the one you originally proposed. Other aspects of the schema definition can be added to the script above.
- The FK definitions above define foreign-key reference columns, but not the PK or referenced table. Another detail that I never did.
Even if you are not using a script. There is a certain pleasure in playing with this material .; -)
Matthew