Importing Multiple Schemas to a Model in Oracle SQL Developer Data Modeler

thatjeffsmith SQL Developer 22 Comments

Tell Others About This Story:

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:

  1. HR
  2. SCOTT
  3. 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!

Tell Others About This Story:

Comments 22

  1. 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.

    1. thatjeffsmith Post
      Author
  2. 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?

  3. 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?

    1. thatjeffsmith Post
      Author

      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?

      1. 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?

        1. thatjeffsmith Post
          Author
  4. 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?

    1. thatjeffsmith Post
      Author
  5. 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

    1. thatjeffsmith Post
      Author
  6. 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.

    1. thatjeffsmith Post
      Author
  7. 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

  8. 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

    1. thatjeffsmith Post
      Author
  9. 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!

  10. 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!

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *