oracleDiff – a schema differential tool for Oracle

On friday last week I got a requirement from a co-worker. He asked me for a simple Java based application which compares two different revisions of Oracle database schemas. After some discussion about the sense of this tool, we made a quick requirement and workflow analysis.

  1. User extracts target database schema information with oracleDiff. Only column names and types are required. There is no need for exporting Stored Procedures, Functions, References and so on. Schema information should be stored as XML.
  2. User exports full table data with oracleDiff. The extracted data should be stored as binary or flat file – XML export for large data is too memory intensive.
  3. User does a manual upgrade of the changes with help of Oracle Enterprise Manager Console or other 3rd party tools.
  4. User imports exported data on upgraded database, oracleDiff asks for remapping of columns and informs about column type changes.

Yesterday I started the project and stumbled over two Oracle specific facts:

  1. DESC $TABLENAME does not work in PreparedStatements. You will receive ORA-00900 from Oracle JDBC driver. For retrieving table information you have to use SELECT * FROM user_tab_columns WHERE table_name = ?.
  2. As a long time MySQL programmer I used SHOW TABLES for retrieving all tables of a schema. This will also not work. You have to to use SELECT table_name FROM user_tables.

The current development status extracts the database schema information and compares two schemas. I hope that I will finish the tool within the next two weeks.

Comments ( 2 )

  1. / Replyichwars
    May be, it's easy to use the Oracle package dbms_metadata. Example (sqlplus): select dbms_metadata.get_ddl(object_type => 'TABLE',name => 'EMP',schema => 'SCOTT') from dual; If you look deeper in documenten, you find the option direct to export XML or SQL DDL. To avoid Store Parameter use presetting for dbms_metadata. EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE','FALSE'); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE','FALSE'); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES','FALSE');
  2. / ReplySchakko
    Thank you for the information, it's really useful!

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>