Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Synching your Oracle SQL Developer Data Model with the Database

4 Mins Read

So you’ve been working on your data model, and you’re wondering how far ‘out of whack’ it is with the production or development database instance. Sometimes folks manage to compile changes into the database, bypassing your design process and rules.

If you’re wondering how to compare 2 models instead of your model with the database, read this.

So your question is basically, show me what’s different. You just need to decide what version of the ‘truth’ to adhere to:

  • the database
  • the model

You need to make this call as the comparison results include DDL scripts to bring the system into alignment.

Which way are you going?
Which way are you going?

But Wait, Those Buttons Are Greyed Out For Me!

To compare your model with the data dictionary, we need to know where that data dictionary is. If you add the model objects from a data dictionary, the connection information is ‘known’ to the model. If you look at one of your model object’s properties, you can see if it’s ‘tied’ to a database:

This table is 'linked' to a SQL Developer connection name - which allows the modeler to do the compare
This table is ‘linked’ to a SQL Developer connection name – which allows the modeler to do the compare

Filtering and Selecting Your Results

The comparison dialog will come back with your results. You can review the differences and ‘approve’ the findings by checking those items in the tree. Key things to check are the ‘Tables that will be recreated’ and the ‘Options’ panels. Some object alters might not be possible without dropping and re-creating the table. Additionally, the ‘options’ panel allows you to decide exactly what properties to compare or not. This will have a dramatic impact on the type of DDL scripts the compare will generate.

A model compare/synch report
A model compare/synch report

Applying the Scripts

There’s no ‘go make it happen’ button in the modeler. You’ll have to take the generated script, test it, tweak it, and approve it. Then you’ll need to run it against your database either via SQL*Plus or SQL Developer. Looking for a ‘apply’ button that will automatically execute the script against your database? Share your thoughts in our Exchange with your enhancement requests. Personally, I think this would be ‘dangerous,’ but I see that many other modeling tools offer this feature, so maybe I’m just too paranoid.

But I Only Want to Compare a Few Things, Not Everything!

Ok, so your model is huge, and you don’t care about ‘everything’ right now. You have three levels of compare ‘depth’ to choose from:

  1. Compare everything
  2. Compare just the SubView
  3. Compare just selected objects

You already know how to compare ‘everything’ – just use the main toolbar ‘Synchronize’ buttons.

Compare Just the SubView
SubViews are great. They allow you to break your model into one or more ‘mini’ models. If you do a data dictionary import from multiple schemas, SQL Developer will automatically generate a SubView for each schema. If you only wanted to synchronize/compare just one schema in your model, then the SubView compare would be the easiest way to go.

To kick off the synchronize, go into the subview, and right-click in the subivew diagram.

It's a big context menu, just skip down to the bottom!
It’s a big context menu, just skip down to the bottom!

Compare Just Selected Model Objects
Just what it sounds like, if you want a quick-and-dirty compare, just select or multi-select the object(s) in the diagram, right click and ‘Synchronize…’

Summary

Again, don’t forget to check, check, and check again your options. Then make sure to test your scripts before you just run them against your DEVELOPMENT or TEST databases. I’m not even going to mention PRODUCTION, because you already know better.

One last word about options – you can say in the preferences what pieces of the PHYSICAL model properties to apply in your compares. By default we only take in the tablespaces and sequences from that level. So if you’re wondering why your synonyms or PL/SQL objects aren’t being compared and ‘synched’, then go to the preferences and toggle them on.

What all do you want compared from the physical level?
What all do you want compared from the physical level?
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

27 Comments

  1. jule Reply

    Hello Jeff,

    When the DDL gets generated, it would be nice to have some separation between tables in the DDL. When needing to go through the DDL, it would make things much easier to read.

  2. Amory Hunnewell Reply

    So I am wondering why my triggers aren’t being compared and ‘synched’, and triggers are not available in the preferences to toggle. What am I missing? I want to use the Compare Models tool and grab trigger changes, along with say column changes which do show as alter statements to tables, for example. Thanks in advance for any help!! (This is my first question to you, thanks That Jeff Smith.)

    -Amory

    • thatjeffsmith Post Author

      Is your physical model open? If it’s not open, I don’t think we’ll pick up the triggers in the compare…to be honest, I’ve not used the modeler to compare triggers before.

    • Amory Hunnewell

      So that’s one of the things I notice…how to open the physical model. I notice I need to refresh when navigating to the physical model to get that dropdown list to show.

      Generally, I am trying to use data modeler to generate the DDL of all changes since the last release…so I save the model each release. If triggers change, I want to get those changes. Seems like the tool should do it, yes?

    • Amory Hunnewell

      AH! so I had checked that Don’t show this dialog when opening a model and the first time I didn’t have physical checked! Seems like there should be a way to open the physical model in another way.

      However, having the physical model open does not appear to help get the trigger changes.

    • thatjeffsmith Post Author

      Yeah, I think so. Can you submit a question to our forums, including a use case and the type of change you’re trying to detect?

  3. Tim Reply

    Jeff,

    I’m using 4.0.3.853 with the personal edition install. I’m doing small project at home to test for things at work where we have an enterprise version. When I compare my model with the database and try to use the “Sync New Object” button nothing happens on my database. What could the cause be? I tried this at work and thought I’d try here at home to see if maybe I don’t have the right permissions at work. Nothing is working.
    -Tim

    • thatjeffsmith Post Author

      That button doesn’t touch your database.

      What it does, is it updates your model if you have made any changes to objects you’ve imported from your data dictionary, or if you’ve added new objects so that the compare will treat those objects as if they came from your database.

      So, if you have done a data dictionary import, then changed a table name. Or added a new table. Or added a column – those objects, after touching that button, will be marked as having come from the database, so when you do the compare, they are compared to the corresponding schema from where the original import was performed.

      We’re going to rename that button label and add a tooltip so this is more obvious.

      If you want to actually sync your database, hit the ‘Merge’ button and take the generated DDL script and execute it against your database – AFTER you have reviewed and tested it of course.

  4. Scot B Reply

    Hi Jeff,

    I’m trying to get my model to synchronize with my oracle data dictionary. I imported the table into my model so the source information is in the table properties. I added a new field to the table and then I right clicked on the table and chose sync with data dictionary. The problem is that Sync new objects doesn’t do anything and Merge is disabled. My only option is DDL preview and run them manually. In a prior version (version 3), I was able to push all my model changes into oracle, however, since using the newer versions (4 or higher), this hasn’t been working. It’s not clear as to what step I’m missing to get the merge button enabled.

    • Gavin K

      Hello — this sounds like the same problem I’m having. When I attempt to sync my model with the MSSQL db, the ‘Merge’ and ‘DDL Preview’ buttons are both greyed out (even though SDDM correctly displays the differences/deltas in the upper portion of the dialog). Any thoughts on this?

      Thanks so much,
      Gavin

    • Leandro

      Almost two years passed since this your reply but the limit seems to be still there. Is it still not possible to have alter scripting for DB not named Oracle?

      Thanks for you posts.

  5. Stephane Vinette Reply

    Hi Jeff,

    I’m having issues/problems generating DDL scripts for synonyms. As you mentioned in this thread, I did change my Preferences (Preferences > Data Modeler > Model > Synchronization Physical > Oracle) and checked SYNONYM. But still, SDDM does not generate those DDL scripts for synonyms. I’m baffled!

    Using SQL Developer Data Modeler (or SDDM) v4.0.2.

    Thanks,

    Steph

    • thatjeffsmith Post Author

      Up at my in-laws for the Holidays and don’t have my work computer out, but make sure the physical model is actually open. If it’s not, the modeler doesn’t use the information there to generate the DDL scripts.

    • Ulrike Lembach

      Hi Jeff,
      unfortunately I have the same problem as Steph. Synchronizing the Data Dictionary with the model skip synonyms and also funciotns/procedures. Even though the preferences for physical synchronizations are set and the physical model is actually open.
      Using SQL Developer version 4.0.2.15 (with Oracle SQL Developer Data Modeler version 4.0.1.836)
      Thanks,
      Ulrike

    • julesdba

      Perhaps dashes or something else. Maybe a ‘header’ with table name?

    • thatjeffsmith Post Author

      You’re need to write a custom DDL transformation script (Tools – Design Rules & Transformations – Tabld DDL Transformations) …something like this

      1) after CREATE script (example):
var text = "\n\n\n-- end of table "+TABLE.getLongName()+"\n";
ddlStatementsList.add(NEW java.lang.String(text));
 
AND it can be combined WITH BEFORE CREATE script:
var text = "\n-- begin of table "+TABLE.getLongName()+"\n";
ddlStatementsList.add(NEW java.lang.String(text));
 
they can tune the NUMBER OF \n 
But there IS also OPTION "Prompt" command TO be included (FOR Oracle DDL).
here IS the RESULT FROM BOTH scripts:
-- end of table SH.COSTS
 
 
-- begin of table HR.COUNTRIES
CREATE TABLE HR.COUNTRIES 
    ( 
     COUNTRY_ID   CHAR (2) 
     CONSTRAINT COUNTRY_ID_NN NOT NULL , 
     COUNTRY_NAME VARCHAR2 (40) , 
     REGION_ID    NUMBER 
    ) 
;
 
COMMENT ON TABLE HR.COUNTRIES IS 'country table. Contains 25 rows. References with locations table.'
;
 
COMMENT ON COLUMN HR.COUNTRIES.COUNTRY_ID IS 'Primary key of countries table.' 
;
 
COMMENT ON COLUMN HR.COUNTRIES.COUNTRY_NAME IS 'Country name' 
;
 
COMMENT ON COLUMN HR.COUNTRIES.REGION_ID IS 'Region ID for the country. Foreign key to region_id column in the departments table.' 
;
 
ALTER TABLE HR.COUNTRIES 
    ADD CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY ( COUNTRY_ID ) ;
 
 
 
 
 
-- end of table HR.COUNTRIES
 
 
-- begin of table SH.CUSTOMERS
CREATE TABLE SH.CUSTOMERS 
    ( 
     CUST_ID                NUMBER  NOT
    • thatjeffsmith Post Author

      there’s also a DDL option in preferences to include a PROMPT, which adds a SQL*PLus doc style PROMPT command between each object creation.

  6. Efren Ramirez Reply

    Hello,

    I have a Q. I have Imported my DD to SQL Developer and I have my ER etc etc. I have SEQ’s for PK’s basically in all my tables, when I do DDL view for one table the SEQ create is NOT there ( I have enabled the generation trigger flag and set name for the SEQ and TGR). If I create a NEW table and set everything the SEQ create command is added to DDL view. Why? I would like to see the SEQ create command for the tables I have imported. Any clues?

    Thanks in advance,
    Efren

    • thatjeffsmith Post Author

      The sequence isn’t actually part of the table. The only thing linking the sequence to the column is the trigger. The data dictionary doesn’t know that the column value being generated is coming from INSIDE THE DATABASE 🙂

      We would have to parse the trigger source to know this.

      So, it’s a challenge.

      Until 12c, when you can create the column as IDENTITY.

    • Efren Ramirez

      Hello Jeff,

      I understand and I am not expecting to have the SEQ tied to the table when the Schyn is done, but after this, I have entered the SEQ name to the table imported and the DDL generated does not include the CREATE SEQ statement. is this a bug?

      Thanks,
      Efren

    • thatjeffsmith Post Author

      DDL preview on a table only shows the DDL for the table. A sequence isn’t defined as being part of a table. If you generate the DDL for the entire model, you’ll see the sequence then.

  7. Mouhamed Elmasry Reply

    Hello Jeff,

    I have a question regarding the SQL dev data modeler , Model synchronization to DB.
    If you bring your model from Oracle DEsigner, how can you tie it to a DB, so when you synchronize with DB, it knows to which DB comparing.

    So far it only way, is to import some objects from the data dictionnary such as schemas, users and tablespace, then I do an import from Oracle DEsigner, to finally merge them.

    Is this the only way?

    Thanks in advance.

    Mouhamed

Write A Comment