Importing Multiple Schemas to a Model in Oracle SQL Developer Data Modeler
- by thatjeffsmith
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.
The Browser allows you to navigate your open designs/models
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.’
This is a fancy way of saying, ‘suck objects out of the database into my model’
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
Select one or more schemas you want to import
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.
The list of tables in my relational model
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!
The schemas are automatically used to create default SubViews
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.
The SubView diagram only contains the objects from that SubView
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!
