Создание er диаграммы oracle
Я хочу использовать Oracle SQL Developer для создания диаграммы ER для моих таблиц БД, но я новичок в Oracle и в этом инструменте.
Каков процесс создания ER-диаграммы в SQL Developer?
Вы также можете сделать это с помощью ER Diagram Tool в dbForge Studio for Oracle. Попробуйте бесплатное экспресс-издание.
Создайте диаграмму для существующей схемы базы данных или ее подмножества следующим образом:
- Нажмите Файл → Data Modeler → Импорт → Словарь данных .
- Выберите соединение с БД (добавьте одно, если оно отсутствует).
- Нажмите Далее .
- Проверьте одно или несколько имен схем.
- Нажмите Далее .
- Отметьте один или несколько объектов для импорта.
- Нажмите Далее .
- Нажмите Готово .
Экспортируйте диаграмму следующим образом:
- Нажмите Файл → Data Modeler → Распечатать диаграмму → В файл изображения .
- Найдите и выберите расположение файла экспорта.
- Нажмите Сохранить .
Диаграмма экспортируется. Чтобы экспортировать в векторный формат, используйте To PDF File . Это позволяет упростить редактирование с использованием Inkscape (или другого редактора векторных изображений).
Эти инструкции могут работать для разработчиков SQL с 3.2.09.23 по 4.1.3.20.
Не работает для меня Разработчик SQL 3.2.20.09 отображает не все таблицы, но отображает другие объекты, такие как представления и т. Д. Есть идеи?
@sataniccrow: согласен, инструмент для разработки sql в целом не прост в использовании и глючит, делает любую работу болезненной. но имеет много функций, с другой стороны.
У меня была проблема при создании нового соединения с базой данных при создании модели ER. При выборе TNS в качестве Типа соединения в раскрывающемся списке Псевдоним сети не было ожидаемых записей в моем файле tnsnames.ora. Чтобы обойти это, я выбрал тип подключения «Базовый» и ввел нужные значения «Имя хоста», «Порт» и «Имя службы» из файла tnsnames.ora, который работает иначе. Может быть, есть место, где мы можем указать файл tnsnames.ora для SQL Modeler, но я не смог найти такого места через SQL Developer (Инструменты-> Настройки-> Data Modeler). После этого все было просто.
Примечание: вы должны быть внутри инструмента Data Modeler, иначе «Data Modeler» не будет доступен в меню «Файл».
Предположим, я сгенерировал диаграмму, а затем обновил одну из исходных таблиц. Как мне «обновить» диаграмму, чтобы показать изменения, которые я внес в исходную таблицу?
Начиная с SQL Developer 3, это довольно просто (они могли бы сделать это проще).
- Перейдите к «View → Data Modeler → Browser» . Браузер будет отображаться как одна из вкладок с левой стороны.
- Нажмите на вкладку «Браузер» , разверните дизайн (возможно, называется Untitled_1 ), щелкните правой кнопкой мыши «Реляционные модели» и выберите «Новая реляционная модель» .
- Щелкните правой кнопкой мыши по вновь созданной реляционной модели (возможно Relational_1 ) и выберите «Показать» .
- Затем просто перетащите нужные таблицы (например, из вкладки «Соединения» ) на модель. Обратите внимание, что при нажатии на первую таблицу на вкладке «Подключения» SQLDeveloper открывает эту таблицу справа: выберите все таблицы слева, а затем убедитесь, что Relational_1 вкладка (или любое другое имя) является активной в правой части окна, прежде чем перетаскивать их потому что он, вероятно, переключился на одну из таблиц, которые вы щелкнули в lhs.
Это звучит просто - но так грустно - перетаскивание не работает для меня (WinXP / SQLDesigner 3.2.09). Предложения?
Просто оставив здесь комментарий, потому что я потратил полчаса, чтобы понять, как экспортировать диаграмму в png или pdf. И я не хотел бы делать это снова в следующий раз, когда мне это понадобится (. это не первый раз). В любом случае, процедура такая же, как и в ответе @ Sergei выше: Нажмите «Файл» → «Моделировщик данных» → «Распечатать диаграмму» → «В файл изображения (или в файл PDF)»
Процесс создания диаграммы Entity-Relationship в Oracle SQL Developer был описан в Oracle Magazine Джеффом Смитом ( ссылка ).
Диаграмма отношений сущностей
4. Add missing relationships
As you probably noticed, diagram includes relationships between tables imported from database foreign key constraints. Not all FKs are defined in most databases. To fix diagram you can add relationship in you model (this will not impact your database).
To add relationship select New Foreign Key from top menu.
Then click on referencing table (in my case countries table) and drag line and click on referenced table (in my case continents table).
Now you need to provide FK and PK columns in Associated Columns section.
New relationship is shown on a 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
7. Remove entities from diagram
There is no easy way to simply hide element from your diagram. You can only delete objects from your design but this means that you will not see it in documentation or you will not be able to add describe it.
Also another why to achieve hiding object is to create subview but it is something for another tutorial.
Импорт словаря данных
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?
5. Hide specific columns, indexes etc.
By the default, diagram includes all table details - columns, indexes and keys). If you'd like to hide some of this elements, right click on table or view and click on Show / Hide Elements.
Now you can select what you want to hide.
For example on my diagram I don’t want to show indexes.
If you will hide elements in the object you will see unnecessary empty space in entity box. You can resize box manually or by choosing Resize to Visible from menu under right click.
9 Answers 9
Try out Oracle SQL Developer. It is from oracle and is going to be part of actual oracle installation from 11g onwards. It has a separate build modeler tool to engineer or re-engineer an ER model.
Other tool that may be useful that will help you more than just genereate ER diagram is SchemaSpy
To use Oracle SQL Developer you need to connect to the database in question, export the DDL and then import the DDL with file -> data modeler -> import -> DDL File
@Jazzepi This is not correct. You open the Data Modeler tool, you go to File then to Import then to Data Dictionary and then from there you can connect to a database and have the tool generate the ERD for you.
SQL Developer is awesome. Supports all the databases and perfect for reverse engineering. It free and Java.
Its Free unlike other solutions listed above and it works, I just used it to reverse engineer a Postgres db.
I few years ago, I used Data Architect, part of Power Designer from Sybase. It's a little pricy, but it's excellent. And it makes a fine distinction between the physical (SQL) model of data and the ER model of data. It keeps both models side by side.
If you are not too concerned about the difference between SQL and ER, and you just want a diagram, here's something I did once when I needed a diagram in a big hurry.
Crank up MS Access with a new empty database. Set up table links to all the tables in the schema, or just the ones you want to diagram. Use the "Relationships" tool in Access to create lines between the foreign keys and the primary keys they refer to. Classify these relationships as to many-to-many or many-to-one.
In the Access relationships view, move the boxes around until the diagram is pretty. Print.
Article for: Oracle SQL Developer ▾ SQL Server Management Studio (SSMS) MySQL Workbench DataGrip Toad for Oracle Toad for SQL Server IBM Data Studio
In this tutorial I want to show you how to create Database Relational Diagram (or ERD) for your existing Oracle database with Oracle SQL Developer Data Modeler, a free data modeling tool from Oracle shipped with Oracle SQL Developer - a database console.
3. Automatic diagram
When import is finished you see a new model called Relational_1 with automatically generated diagram.
Pros & cons of diagrams in SSMS
Pros
- Ability to add multiple diagrams into a database
- Keeping diagrams with database schema
- Schema changes are automatically reflected on the diagram
- Ability to add annotations
- Ability to customize table display
- In already tool that is used by DBAs and developers
Cons
- Limited formatting capabilities
- Unable to add views into diagram
- Unable to show relationships that are not defined in as foreign key constraints
- Requires access to the database
8. Add notes
Sometimes you would like to add some information about object, relation or anything else on diagram. You can do that using notes. To add a note click New note from the top menu and click on diagram in a location where you want to place it.
To change text of a note just double click on note or right click and select Properties.
Window to change text of a note is a simple text-area field. I have added some note without nothing special in it.
7. Exporting diagram
Management Studio enables you to export diagram to image. To convert it to the image, right click on the diagram pane and choose Copy Diagram to Clipboard. You can now paste it into graphic software or into a document.
3. Adding related tables
SSMS editor comes with one useful function. You can automatically add all the tables related to a particular table (with a foreign key relationship) to the diagram. To do it, right click on the table and choose Add Related Tables.
4. Adding relation labels
SSMS enables you to add labels to the relationships. This is always a name of the foreign key constraint. Nothing particularly useful, if you ask me.
To add labels right click on diagram pane and choose Show Relationship Labels.
Начиная
Для работы с примером вам понадобится экземпляр Oracle Database с образцом схемы HR, который доступен при установке базы данных по умолчанию. Вам также нужна версия 4.0 Oracle SQL Developer, в которой вы получаете доступ к Oracle SQL Developer Data Modeler через подменю Data Modeler [. ]. В качестве альтернативы вы можете использовать автономный Oracle SQL Developer Data Modeler. Функциональные возможности моделирования идентичны в двух реализациях, и обе доступны для бесплатной загрузки из Oracle Technology Network.
В Oracle SQL Developer выберите View -> Data Modeler -> Browser. На панели «Браузер» выберите узел «Реляционные модели», щелкните правой кнопкой мыши и выберите «Новая реляционная модель», чтобы открыть пустую панель диаграммы модели. Теперь вы начинаете с того же места, что и тот, кто использует автономный Oracle SQL Developer Data Modeler. Импорт словаря данных
1. Creating new diagram
To create the new database diagram, you will need to right click on Database Diagrams folder and click on New Database Diagram.
If you crate diagram for the first time you may get the following message:
SSMS requires some system procedures and a table that are not created with the database. You need to confirm to create them. This will create following system procedures in your database:
- dbo.sp_alterdiagram
- dbo.sp_dropdiagram
- dbo.sp_creatediagram
- dbo.sp_renamediagram
- dbo.sp_helpdiagramdefinition
- dbo.sp_helpdigarms
- dbo.sp_upgradediagrams
If the above message appears, you need to right click on Database Diagrams folder again and choose New Database Diagram option.
A window will appear with list of all the tables in your database. To add the tables to the diagram select them (use Control or Shift keys to select multiple at once) and click Add button or double click on them. When you add all required tables click Close button.
You can add tables later on. Just right click on diagram pane and choose Add Table.
This will create a diagram with the tables columns, primary keys, and relationships that were read from the schema.
9 Answers 9
Create a diagram for existing database schema or its subset as follows:
- Click File → Data Modeler → Import → Data Dictionary.
- Select a DB connection (add one if none).
- Click Next.
- Check one or more schema names.
- Click Next.
- Check one or more objects to import.
- Click Next.
- Click Finish.
The ERD is displayed.
Export the diagram as follows:
- Click File → Data Modeler → Print Diagram → To Image File.
- Browse to and select the export file location.
- 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.
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 ?
@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.
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.
Side note: You must be inside the Data Modeler tool otherwise "Data Modeler" will not be available on the "File" menu.
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?
Since SQL Developer 3, it's pretty straightforward (they could've made it easier).
- Go to «View → Data Modeler → Browser». The browser will show up as one of the tabs along the left-hand side.
- Click on the «Browser» tab, expand the design (probably called Untitled_1 ), right-click «Relational Models» and select «New Relational Model».
- Right click on the newly created relational model (probably Relational_1 ) and select «Show».
- 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.
That sound easy - but so sad - drag and drop does not work for me (WinXP/SQLDesigner 3.2.09). Suggestions?
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)
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 .
The process of generating Entity-Relationship diagram in Oracle SQL Developer has been described in Oracle Magazine by Jeff Smith (link).
2. Import schema
Now you need to import database schema (tables, columns etc.) from your existing database to your new relational model. To import schema select File -> Import -> Data Dictionary (or press Ctrl + Shift + B).
or provide all the details with Add button:
When you successfully connect to your database you need to choose schemas you want to include in your diagrams. Select schemas and confirm with Next.
Now you can choose objects you want to import to the model. You probably want to import all tables and views. Select Table tab and choose tables or select all of them with icon showed below.
If you will have more Relational Models in your design you can choose where you want to import your objects. When ready continue with Next.
Now you see a summary screen before actual import. To start import click Finish button.
2. Tuning diagram
For now you have diagram with all the tables but it might not look like the way you want it. SSMS has a very useful function - Autosize. Select all tables (Ctrl + A), right click on one of them and click Autosize Selected Tables.
Tables will be arranged on the diagram. If you are not happy with the result, you can drag & drop them for better alignment.
You can also decide how to display the tables. Right click on the table and choose one of the options in Table View.
Column names
This option shows column names and primary key only.
Standard
This option will include basic column attributes.
Keys
This option will include only columns that are part of a primary, unique or foreign key.
Name only
This option will show table names only.
Custom
You can also create your custom view, where you can choose which column attributes you want to include.
After small updated my diagram looks like below:
6. Saving diagram
You can save your diagram in the database (it will be saved in the dbo.sysdiagrams table you created earlier). To save diagram go to File -> Save Diagram_0 (this is default name for first diagram) or close the editor. You will be prompted with the diagram name. Provide a name and press OK.
Another way: Dataedo
There is a better way to create and share diagrams of existing databases - Dataedo. Here is a sample export of complete database documentation:
6. Choose visible details
You can also define details shown on diagram globally. Right click blank diagram space, chose View Details and then choose one of the options in submenu.
All Details
This option shows all elements of an object, so you can see columns, Keys, Datatype, Indexes.
Names Only
This option will show table names only.
PK, FK, UK Columns Only
This will show only columns which are PK, FK or UK.
Columns
This option shows columns name and PK, FK or UK flags.
Additional options
You can choose which extra elements you want to show on diagram. In example below I have chosen Comments, Datatypes, Keys and Indexes.
I cleansed my diagram and ended up with this:
5. Adding annotations
One useful option is the ability to add annotations to your diagram. To add the annotation right click on the pane and choose New Text Annotation.
This will create a blank text field where you can provide your notes and comments. This will always be visible and export with your diagram.
1. Create a design
To create a diagram you will need to create a design in Data Modeler. The easiest way to create the new design is simply open Data Modeler. There will be a default new design named Untitled 1.
Before you start creating a diagram save design on your computer with Open File -> Save As… and provide filename and location where you want to keep your design.
Now you need to create a Relational Model to hold a diagram. Right click Relational Models elements of your design and choose New Relational Model.
Now when you have new empty model in your design you can import tables and views into it.
9. Export diagram
Finally, when your diagram is ready, you might want to share with others. You can export diagram to a number of formats:
To export diagram go to File -> Print Diagram in menu or right click diagram blank space and then choose Print Diagram and choose export format.
Article for: SQL Server Management Studio (SSMS) ▾ MySQL Workbench Oracle SQL Developer DataGrip Toad for Oracle Toad for SQL Server IBM Data Studio
Please note that SSMS 18 no longer supports diagrams.
Check out an alternative.
In this tutorial, I will show you how to create an ER diagram with Microsoft SQL Server Management Studio (SSMS) 16.
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.
Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.
Want to improve this question? Update the question so it's on-topic for Stack Overflow.
Closed 7 years ago .
Looking for tools (windows platform) to genereate ER diagram (or similar) from an existing Oracle database.
Any good tools out there that are free to use or low cost?
There is a built-in Oracle database designer in dbForge Studio. Check out a free 30-day trial for now.
8. Opening diagram
All the diagrams saved in the database are visible under Database Diagrams folder. To open a diagram, double click it or right click on it and choose Modify option.
Читайте также: