How to generate an entity-relationship (ER) diagram using Oracle SQL Developer

Question

I want to use Oracle SQL Developer to generate an ER diagram for my DB tables but I am new to Oracle and this tool.

What is the process for creating an ER diagram in SQL Developer?

Reganregard

You also can do it using ER Diagram Tool in dbForge Studio for Oracle. Try free Express edition. – Buckden

Answer 1

Create a diagram for existing database schema or its subset as follows:

1. Click File → Data Modeler → Import → Data Dictionary.
2. Select a DB connection (add one if none).
3. Click Next.
4. Check one or more schema names.
5. Click Next.
6. Check one or more objects to import.
7. Click Next.
8. Click Finish.

The ERD is displayed.

Export the diagram as follows:

1. Click File → Data Modeler → Print Diagram → To Image File.
2. Browse to and select the export file location.
3. Click Save.

The diagram is exported. To export in a vector format, use To PDF File, instead. This allows for simplified editing using Inkscape (or other vector image editor).

These instructions may work for SQL Developer 3.2.09.23 to 4.1.3.20.

Ferwerda

Doesn't work for me. SQL developer 3.2.20.09 doesn't display all the tables, but it displays other objects such as views etc. Any ideas ? – Jokjakarta

@sataniccrow : agree, sql developer tool in general is not easy to use and buggy, makes any job painful. but has many functions, in the other hand. – Sparing

I had a problem creating a new database connection while creating the ER model. On selecting TNS to be the Connection Type, the Network Alias drop down did not show the expected entries in my tnsnames.ora file. To get around, I selected the Connection Type to be Basic, and entered the desired Hostname, Port and Service Name values from the tnsnames.ora file that works otherwise. May be there is a place where we can specify the tnsnames.ora file for the SQL Modeler, but I could not find any such place through SQL Developer (Tools->Preferences-> Data Modeler). Things were simple afterwards. – Longdrawnout

Side note: You must be inside the Data Modeler tool otherwise "Data Modeler" will not be available on the "File" menu. – Kulturkampf

Suppose I generate the diagram and then update one of the source tables. How do I "refresh" the diagram to show the change I made to the source table? – Malapropos

Thorough solution. Works for me on Oracle SQL developer version 18.3.0.277. Thank you! – Tournai

These steps worked perfectly for me using Version 19.1.0.094, thanks for the details! – Acknowledge

Answer 2

Since SQL Developer 3, it's pretty straightforward (they could've made it easier).

1. Go to «View → Data Modeler → Browser». The browser will show up as one of the tabs along the left-hand side.
2. Click on the «Browser» tab, expand the design (probably called Untitled_1), right-click «Relational Models» and select «New Relational Model».
3. Right click on the newly created relational model (probably Relational_1) and select «Show».
4. Then just drag the tables you want (from e.g. the «Connections» tab) onto the model.  Note when you click on the first table in the Connections tab, SQLDeveloper opens that table in the right: select all the tables from the left, then ensure the Relational_1 tab (or whatever name) is the active one in the rhs before you drag them over, because it has probably switched to one of the tables you clicked in the lhs.

Xochitlxp

That sound easy - but so sad - drag and drop does not work for me (WinXP/SQLDesigner 3.2.09). Suggestions? – Planksheer

Are you using SQL Designer or SQL Developer? – Xochitlxp

I used Oracle SQL Developer sorry, that was a typo up above writing SQLDesigner. – Planksheer

Just dropping a comment here because I just spent half an hour figuring out how to export the diagram to png or pdf. And I'd hate to do that again the next time I need this (...this is not the first time). Anyhow the procedure is as stated in @Sergei's answer above: Click File → Data Modeler → Print Diagram → To Image File (or To PDF File) – Melantha

The problem you describe of clicking on a table shows it's defn requiring you to click on the model tab again, is a pain. But it can be solved in SQLDeveloper > Tools > Preferences > Database > Object Viewer > Open Object on Single Click. – Uhhuh

Didn't work for me until after I restarted SQL Developer. Because Oracle. – Manama

and how should one create visual relationships between the tables on added to the ERD? – Septi

This worked for me and the other method above worked, but not user friendly. – Strangury

Answer 3

The process of generating Entity-Relationship diagram in Oracle SQL Developer has been described in Oracle Magazine by Jeff Smith (link).

Excerpt:

Entity relationship diagram

Getting Started

To work through the example, you need an Oracle Database instance with the sample HR schema that’s available in the default database installation. You also need version 4.0 of Oracle SQL Developer, in which you access Oracle SQL Developer Data Modeler through the Data Modeler submenu [...] Alternatively, you can use the standalone Oracle SQL Developer Data Modeler. The modeling functionality is identical in the two implementations, and both are available as free downloads from Oracle Technology Network.

In Oracle SQL Developer, select View -> Data Modeler –> Browser. In the Browser panel, select the Relational Models node, right-click, and select New Relational Model to open a blank model diagram panel. You’re now starting at the same place as someone who’s using the standalone Oracle SQL Developer Data Modeler. Importing Your Data Dictionary

Importing Your Data Dictionary

A design in Oracle SQL Developer Data Modeler consists of one logical model and one or more relational and physical models. To begin the process of creating your design, you must import the schema information from your existing database. Select File -> Data Modeler -> Import -> Data Dictionary to open the Data Dictionary Import wizard.

Click Add to open the New -> Select Database Connection dialog box, and connect as the HR user. (For detailed information on creating a connection from Oracle SQL Developer, see “Making Database Connections,” in the May/June 2008 issue of Oracle Magazine.)

Select your connection, and click Next. You see a list of schemas from which you can import. Type HR in the Filter box to narrow the selection list. Select the checkbox next to HR, and click Next.

Article link:

• Blog post at the Oracle Magazine

Sanalda

link to OracleMag article is dead :-( – Torin

Answer 4

For a class diagram using Oracle database, use the following steps:

File → Data Modeler → Import → Data Dictionary → select DB connection → Next → select database->select tabels -> Finish

Pyrethrin

Answer 5

There is a companion tool called Oracle Data Modeler that you could take a look at. There are online demos available at the site that will get you started. It used to be an added cost item, but I noticed that once again it's free.

From the Data Modeler overview page:

SQL Developer Data Modeler is a free data modeling and design tool, proving a full spectrum of data and database modeling tools and utilities, including modeling for Entity Relationship Diagrams (ERD), Relational (database design), Data Type and Multi-dimensional modeling, with forward and reverse engineering and DDL code generation. The Data Modeler imports from and exports to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of design rules.

Fujio

SQL Developer version 3 includes Data Modeler but the standalone version is easier to use as you don't have to dig down into the menus to find the items relevant to the modeler – Luannaluanne

link to demos is dead :-( – Torin

Answer 6

For a Entity Relationship Diagram using Oracle SQL Developer:

File → Data Modeler → Import → Data Dictionary → Select database connection → Next → Select schema/database (Check All Selected) → Next → Select object to import(if All Selected was not checked) → Next → Finish

Pennyroyal

Answer 7

Oracle used to have a component in SQL Developer called Data Modeler. It no longer exists in the product since at least 3.2.20.10.

It's now a separate download that you can find here:

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

Squier

As of 2019 (SQL Developer 19.2) the feature is definitively builtin, though it's possible to enable and disable it at "Manage Features and Updates" (Tools/ Features). – Calliecalligraphy

Yah, I wish Oracle would make up it's mind on this one. Every time I go looking for it in a new version, it's moved somewhere "better" – Squier

Answer 8

I'm running SQL Developer 17.2.0.188 build 188.1159 which does indeed contain data modeling capability. I just created a relational model diagram via the menu: File->Data Modeler->Import->Data Dictionary....

I also have the stand-alone Data Modeler, which does the same thing.

As the Data Modeler tutorial states:

Figure 4: Relational model and diagram for HR

The diagram you’ve generated is not an ERD. Logical models are higher abstractions. An ERD represents entities and their attributes and relations, whereas a relational or physical model represents tables, columns, and foreign keys."

Hepner

Answer 9

Its easy go to File - Data Modeler - Import - Data Dictionary - DB connection - OK

Choose