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

Importing Your Data Dictionary to a new Oracle SQL Developer Data Modeler Design

2 Mins Read

I’ve shown how to drag and drop database tables from Oracle SQL Developer into a design. But I’ve never done a start-to-finish, blow-by-blow post on how to import your existing database objects into a data model with SQL Developer.

So let’s do that now.

Warning: Lots of pictures coming up.

Start with a new design, make sure you have a Relational Model to import to.
Start with a new design, make sure you have a Relational Model to import to.
Now go to the file menu and start the import.
Now go to the file menu and start the import.
You can import existing connections from Oracle SQL Developer if you have them - note that you need v4.0.3 of the modeler in order to bring over the passwords.
You can import existing connections from Oracle SQL Developer if you have them – note that you need v4.0.3 of the modeler in order to bring over the passwords.
To create a new connection, put in your username, etc. You can test your connection before you hit 'Ok' if you want to save yourself some time in case you made a mistake.
To create a new connection, put in your username, etc. You can test your connection before you hit ‘Ok’ if you want to save yourself some time in case you made a mistake.

Want to import from SQL Server (or DB2) instead? Check out this great post from Oracle ACE Director @kentgraziano on how to get SQL Server connectivity going in SQL Developer Data Modeler.

You can set some import options here - if you have TONS of partitions - skip them and save a bunch of time.
You can set some import options here – if you have TONS of partitions – skip them and save a bunch of time.
Use the search filter to quickly find your objects and use the Check All box to save yourself some clicks - do this for all the types of database objects you want to import
Use the search filter to quickly find your objects and use the Check All box to save yourself some clicks – do this for all the types of database objects you want to import
Does this look right? If so, hit the 'Finish' button.
Does this look right? If so, hit the ‘Finish’ button.
Your model now has the database objects, and here's a log reminding you what was imported and when.
Your model now has the database objects, and here’s a log reminding you what was imported and when.
Don't forget to save your design!
Don’t forget to save your design!
Ok, it's saved. But now maybe you want to add it to your design repository for change tracking and source control?
Ok, it’s saved. But now maybe you want to add it to your design repository for change tracking and source control?

For Extra Credit

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

14 Comments

  2. Nishanth Reply

    Hi Jeff,

    Data Dictionary Import is Failing For Foreign Key Constraints; Gives Empty relationship ER Diagrams.

    Import failed – using the data dictionary built in feature I connected to the oracle instance and started loading table. However, after choosing the required schema and tables I see the below error in the log generated. In-fact the entity is generated in the physical relation and I see the count of table but, the constraints are failed to load. Thus, it gave me a E-R diagrams with out connecting relationship which is of no use. I also, look into engineer to logical design but no luck. Please help.

    Error:
    2021-05-19 10:49:40 INFO – Oracle SQL Developer Data Modeler 21.1.0.092.1221
    2021-05-19 10:54:49 SEVERE – Error in database import:
    java.lang.NullPointerException
    at oracle.dbtools.crest.imports.metadata.oracle.CheckConstraintsImport.processCheckConstr(CheckConstraintsImport.java:277)
    at oracle.dbtools.crest.imports.metadata.oracle.CheckConstraintsImport.initCHECK_Constraints(CheckConstraintsImport.java:256)
    at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(MOHTableOracle.java:466)
    at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(AbstractDBMExtractionHandler.java:303)
    at oracle.dbtools.crest.imports.metadata.oracle.DBMExtractionHandlerOracle.generateDesign(DBMExtractionHandlerOracle.java:283)
    at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(DBMExtractionController.java:514)
    at java.lang.Thread.run(Thread.java:748)

    I logged in with DBA privileged user and version for sql data modeler is 21+ and using the latest JDK 11+. I have verified below checklist as well –

    Check1:
    select dbms_metadata.get_ddl(‘TABLE’,'<>’,'<>’) from dual ; this able to give the ddl script with foreign key constraints included.

    Check2:
    In SQL Developer, right click the object that you want to generate a script for. i.e. the table name, this able to give the ddl script with foreign key constraints included.

    Check3:
    Generate in DDL: Controls whether the foreign key creation is included when DDL
    statements are generated to be used to create the database.
    Go to FILE -> DATA MODELLER -> EXPORT -> DDL FILE.
    New pop up window appear.
    Click on Generate button.
    New pop window appears.
    Now click on “Generate DDL scripts in Separate Files”, on screen at bottom right.
    Now go to tab “Include TABLE DDL scripts.
    Select table you want to have DDL scripts.
    click ok.
    [ fails to get the foreign key constraints loaded with the sql data modeler ]

    Check4:
    SELECT * FROM user_constraints where owner = <> AND TABLE_NAME=<> AND constraint_type = ‘R’; [ able to see the FK and the status is enabled ]

    SELECT * FROM all_constraints where <> AND TABLE_NAME=<> AND constraint_type = ‘R’; [ able to see the FK and the status is enabled ]

    Check5:
    Right click on the relation E-R Diagram and try via Synchronize with Data Dictionary; [ Unable to load data dictionary using this option ]

    • thatjeffsmith Post Author

      Our developer wonders if you have any NULL check constraints in your schema, that’s what the error message is complaining about. Also, what version database are you connected to?

  3. lila Reply

    Hi Jeff, I imported data modeler and save it. But whenever close Oracle data modeler and reopen it schema is empty and had to re import again. Is there a way to not re-importing every time when closing app. It takes very long time to import.
    Thanks,

    • thatjeffsmith Post Author

      After the import, SAVE your design.

      You’ll now have a .dmd file – that’s your model.

      Next time you start the tool, use the File – Open dialog, or look at Recent Designs.

  4. Dahei Reply

    Hi Jeff,
    i can start SQL Developer correctly with tns. But If I start data mdeler and try to connect to database with tns, the comobox network alias is empty, i can not get my db.
    I have also tried to set the path for tnsnames.ora in Sql Developer, but the comobox is still empty. What ist wrong? on the maschine ist an oracle client installed. but we use tns.
    Thanks in advance for your help
    Dahei

    • thatjeffsmith Post Author

      Version of Data Modeler?

      Did you check the TNS preference? ‘TNS Names Directory’ on the Data Modeler page of Preferences.

  5. Irfan Reply

    Hi Jeff,
    I tried this step by step. First with HR database and it was working fine and i got ERD and later i got the logical model.
    When i tried to apply the same steps on my one of databases at work. It generated just boxes with no relations.

    SOS

    Regards,
    Irfan

  6. Sabine Heimsath Reply

    Hi Jeff,

    I get an ORA-00942 Table not found error when I choose a connection and press ‘next’. (The connection test was passed successfully.)

    Again, I suspect that some grants/privileges are missing. Can you give me a hint which objects are accessed during this step?

    Thank you,
    Sabine

Reply To lila Cancel Reply