Your physical data model might stretch across multiple Oracle schemas. Or maybe you just want a single diagram containing tables, views, etc. spanning more than a single user in the database.
The process for importing a data dictionary is the same, regardless if you want to suck in objects from one schema, or many schemas. Let’s take a quick look at how to get started with a data dictionary import.
I’m using Oracle SQL Developer in this example. The process is nearly identical in Oracle SQL Developer Data Modeler – the only difference being you’ll use the ‘File’ menu to get started versus the ‘File – Data Modeler’ menu in SQL Developer. Remember, the functionality is exactly the same whether you use SQL Developer or SQL Developer Data Modeler when it comes to the data modeling features – you’ll just have a cleaner user interface in SQL Developer Data Modeler.
Importing a Data Dictionary to a Model
You’ll want to open or create your model first. You can import objects to an existing or new model. The easiest way to get started is to simply open the ‘Browser’ under the View menu.
You’ll see an ‘Untitled_1’ model by default. I’ve renamed mine to ‘hr_sh_scott_demo.’
Now go back to the File menu, and expand the ‘Data Modeler’ section, and select ‘Import – Data Dictionary.’
Connect!
If you haven’t already defined a connection to the database you want to reverse engineer, you’ll need to do that now. I’m going to assume you already have that connection – so select it, and hit the ‘Next’ button.
Select the Schema(s) to be imported
The schemas selected on this page of the wizard will dictate the lists of tables, views, synonyms, and everything else you can choose from in the next wizard step to import. For brevity, I have selected ALL tables, views, and synonyms from 3 different schemas:
- HR
- SCOTT
- SH
Once I hit the ‘Finish’ button in the wizard, SQL Developer will interrogate the database and add the objects to our model.
The Big Model and the 3 Little Models
I can now see ALL of the objects I just imported in the ‘hr_sh_scott_demo’ relational model in my design tree, and in my relational diagram.
Quick Tip: Oracle SQL Developer calls what most folks think of as a ‘Physical Model’ the ‘Relational Model.’ Same difference, mostly. In SQL Developer, a Physical model allows you to define partitioning schemes, advanced storage parameters, and add your PL/SQL code. You can have multiple physical models per relational models. For example I might have a 4 Node RAC in Production that uses partitioning, but in test/dev, only have a single instance with no partitioning. I can have models for both of those physical implementations.
Wouldn’t it be nice if I could segregate the objects based on their schema?
Good news, you can! And it’s done by default 🙂
Several of you might already know where I’m going with this – SUBVIEWS.
You can easily create a ‘SubView’ by selecting one or more objects in your model or diagram and add them to a new SubView. SubViews are just mini-models. They contain a subset of objects from the main model. This is very handy when you want to break your model into smaller, more digestible parts. The model information is identical across the model and subviews, so you don’t have to worry about making a change in one place and not having it propagate across your design.
SubViews can be used as filters when you create reports and exports as well. So instead of generating a PDF for everything, just show me what’s in my ‘ABC’ subview.
But, I don’t want to do any work! Remember, I’m really lazy.
More good news – it’s already done by default!
Auto-Navigate to the Object in the Diagram
In the subview tree node, right-click on the object you want to navigate to. You can ask to be taken to the main model view or to the SubView location. If you haven’t already opened the SubView in the diagram, it will be automatically opened for you.
Your SubView might still be pretty big, many dozens of objects, so don’t forget about the ‘Navigator‘ either!
In summary, use the ‘Import’ feature to add existing database objects to your model. If you import from multiple schemas, take advantage of the default schema based SubViews to help you manage your models! Sometimes less is more!
22 Comments
How do i provide grant/privileges to the objects created in this data modeler? I have a requirement to generate DDL along with SELECT grant/privileges of a table to other users.
Create a physical model, you can see users and grant object privileges there.
Hi,
I have designed the schema in data modeler. Now I need to pass this schema to colleague’s machine, so that he can load it in his machine.
I have tried copying .dmd file and the associated folder. But when I open in the new system, the file opens with empty entities.
Could you please help me on this?
I’m not sure if this is the correct place to ask this question, but I’m having trouble with subviews and comments in RDBMS. I managed to create two subviews; the first has tables A, B and C and the second has B, D and E. When I go to put a comment in RDBMS on B, in subview one, I have no problems, but when I go to open up subview two to put a different RDBMS comment, the old one shows up and anything I add to it shows up in the first subview as well. Is there a way to add to the second subview and not have it alter the first subview?
SubViews are organizational/navigation aids. The objects are one in the same across the main model view and ANY of it’s subviews. If you want to make changes w/o it showing up somewhere else, you need to COPY the object and paste it. You’ll get a copy, but then it won’t be connected to the original/base object anymore.
Does that help?
So by copy and paste, do you mean copy and paste subviews and alter the new one with any additional tables or comments that I need? Or by copy and paste do you mean copy the table that I need to update and paste it into the new subview?
copy and paste the table, and you’ll have to do that in the main diagram, and then add it to your diagram – you’ll have TWO tables at that point.
Thank you. That worked perfectly.
Hi Jeff,
When I tried to add a new schema using the import Data dictionary , After I click on the finish button I am redirecting a page called ‘Compare models’ . When I close that window , I can’t see the newly added schema in the list of relational model. Could you please reply me as soon as possible?
i’m guessing you imported data dictionary objects into an existing design, and you need to do a merge on the compare dialog
Hi Jeff
Thanks for your informative blog.
I wonder if you can direct me to where I can find out about how to fix so that when I import a model which has a domain file associated, the attributes don’t lose their association with the domains.
I’m finding that unless the domain file is named defaultdomains.xml the association is lost. I’m using DM 4.1.3
Thanks
I would start by asking on the Modeler forum on OTN
I need to import tables from more than 2 schemas in SQL Developer Data modeler. I am importing using Menu option File-> Import-> Data Dictionary. I am able to select more than 2 schemas but in Select objects to import window, I am able to see only objects from 2 schemas only at the most. why is it so? Also, if I want to import objects from multiple databases how do I do that? Thanks for your help in advance.
i’m able to import as many as I want, weird
are you able to run the import 2x, grabbing 2 schemas at a time?
hi Jeff,
In my project, I have several schema instances who have same tables. But when I want to apply updates I made from instance 1, Data modeler wants to create all tables on instance 2.
Have you an idea ?
Thanks
Hi Jeff,
I am using this to document a SQL database, but when I import objects into the model, it prefixes the schema name with the database name. This in general is fine since I just go in and rename the schema name to its actual name. The problem comes when I go to compare the model with the database to bring in updates. Since the schema name in the model no longer matches the import, everything shows up as different. Any way to not have the database name prefixed to the schema name during the import?
Thanks
Is this for SQL Server?
Yes. MS SQL Server.
I am trying to Import from Data Dictionary – and it works exactly as you describe for one particular database that I use (Oracle 11g), I see all the schemas and objects in them. However, there is a second database (also Oracle 11g) that I routinely connect to but I don’t see any schemas when I try to Import from Data Dictionary. If I connect in SQL Developer I can see tables in the object browser and I have SELECT, INSERT, UPDATE and DELETE permissions granted, and in Data Modeler I can also connect to it no problem (“Test Connection” succeeds), but the Select Schema/Database list is empty in the wizard. Is there some other permission that our DBA has to grant me to see the schemas listed in that database? Thanks!
Thaks!!! was very useful for me.
greetings from Colombia 🙂
Hi Jeff!
I have a very particular case. In my project, I have my regular tables in a specific schema. However, some tables are also used in another schema for simulations. I cannot assign two schemas for the same table. The only way I can work this around is by creating two relational models with the specific set of tables for each of them. Is there any other way to do this?
Thanks!
Copy and paste the table in your diagram, then go into the properties, assign the new schema and rename the copied_v1 to the original name.