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.
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.
For Extra Credit
- Did you notice we got a SubView for each Schema we imported from?
- Make the diagram look ‘nice’ – rearrange objects, change color schemes, etc.
- Add your design to Subversion.
Can you please explain the other way to make a data model?
I have a 100+ posts plus many videos covering data modeler.
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.
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:
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 –
select dbms_metadata.get_ddl(‘TABLE’,'<>’,'<>’) from dual ; this able to give the ddl script with foreign key constraints included.
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.
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.
[ fails to get the foreign key constraints loaded with the sql data modeler ]
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 ]
Right click on the relation E-R Diagram and try via Synchronize with Data Dictionary; [ Unable to load data dictionary using this option ]
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?
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.
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.
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
Version of Data Modeler?
Did you check the TNS preference? ‘TNS Names Directory’ on the Data Modeler page of Preferences.
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.
I’m afraid it’s because your databases at work don’t have any foreign keys.
Thanks for the quick reply, I really appreciate it.
I do have some fields with the same name in the tables and that I usually use to do a join. Is there anyother way i can make a data model for this ?
Try this – DISCOVER FOREIGN KEYS.
Thanks alot man 🙂
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?