Stop guessing and define it yourself!

Go to Preferences:

Select Environment, Log. Then select Save Logs to File and define the Log Directory.

What is the difference between Delet Object and Delete View?

If you right-click and object in a diagram there might be two options: Delete Object and Delete View. “Might” because the Delete View is only available if you have that object in more than one diagram.

Delete Object removes that object totally from the design: from this diagram and from all diagrams where it exists. The object is totally deleted from the design. If the object exists in more than one diagram, you will also see the Delete View option. That will delete the object BUT only from this diagram, not from the whole design.

Let’s see an example with an entity called Product.

This is the Logical Model:

This is a subview:

Now in the subview I select Delete View for the entity Product.

The Logical Model still has the entity Product:

But the subview does not:

Name Substitution can be used to change the name of an object (user, role, table, index, or tablespace) while generating the DDLs. These changes do NOT affect your design. The most common use case is that in test environment something is different from the production environment but not so much that it would be worth maintaining two separate physical models.

Let’s assume that everything is the same in both test and production except the schema owner of the objects: in production they are owned my PROD and in test by TEST. To be able to get the DDLs for creating tables for the test environment we double-click the design name (or right-click and select Properties) in Browser, then go to DDL | Migration.

Press the green plus and define the rule for Name Substitution and press Apply.

Now the rule for changing word “PROD” to “TEST” has been defined but this will not cause any action while generating the DDLs unless you specifically tell the tool to use this.

Let’s generate the DDL (File | Export | DDL File). Now go to Name Substitution tab and make sure the rule has been selected (Selected is enabled):

You can also create new rules in this screen. Just press the green plus and add the rule.

Now go to Object Types tab and select the object types you want this rule to be affecting:

Now you have defined the rule and which object it will be affecting but still if you now press OK (to generate the DDLs nothing will be changed). Next thing to do is to enable the Apply Name Substitution checkbox on the bottom of the screen:

Now press OK.

Instead of getting a DDL like this:

CREATE TABLE PROD.Customer
(
CustNO NUMBER (10) NOT NULL ,
Name   VARCHAR2 (100) NOT NULL
)
LOGGING ;
ALTER TABLE PROD.Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( CustNO ) ;

…

you get a DDL like this:

CREATE TABLE TEST.Customer
(
CustNO NUMBER (10) NOT NULL ,
Name   VARCHAR2 (100) NOT NULL
)
LOGGING ;
ALTER TABLE TEST.Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( CustNO ) ;

…

I know, I know. You can have the DDLs without the schema owner, connect in the database with the right credentials and problem solved without the schema owner added to the script. But I do make mistakes, like connect to a wrong database, so I prefer having the DDLs as human error free as possible. Besides this is just a simple example how Name Substitution can be used 🙂

Quite often we need to set the same properties for several objects in the physical model. It would be very time consuming if we do it one by one… Luckily the tool offers a way to do it much easier.

I would like to set the schema owner to all my tables to PROD. How do I do it?

First set the property for one of the tables:

Then press Propagate Properties.

Since we only want to copy the property User, press Deselect All (in the upper part of the screen, Properties section), then select User:

Select all the tables in the bottom of the screen by pressing Select All in the Object section of the screen:

Then press OK.

Now the schema owner for all the tables has been changed to PROD:

Oracle SQL Developer Data Modeler offers you a way to make sure your design is of good quality. Making sure of the quality can be done very easily: it is all based on Design Rules.

The rules are defined per category: General, Logical, Relational, Process Model and Physical. And under each category per element type of that category. For example for Logical: Entity, Attribute, Key and View. They are also defined as errors or warnings depending how serious they are.

There are pre-defined Design Rules that can be found from Tools|Design Rules and Transformations|Design Rules and by selecting Tools|Design Rules and Transformations|  Custom Rules you can define your own rules:

What does a Design Rule do? It checks that the rule defined has been obeyed. In pre-defined rules you will find rules like

• Identify entities without attributes
• Check for entity maximum name length
• Identify attributes without datatype
• etc

To check the Design Rules you can either go to Tools|Design Rules and Transformations| Design Rules or press the Design Rules button that can be seen in several places in Data Modeler while designing the database, then select Apply All. For example when generating the DDLs you can find the button on the lower left corner:

Let’s select Design Rules and Apply All:

Now you can see all the errors (red) and warnings (blue) that exists in your design compared to the Design Rules:

Double-click on one of the result rows and it will take you directly to the place the problem exists and you can fix it.

If you do not want to execute all the rules at a time you can define Rule Sets. Select the Rule Sets tab and press the green plus to add a new Rule Set. Define the name for it and press Save.

Then press the Properties icon (XYZ and a pen).Select the Design Rules wanted from the list and press the arrow pointing to right.

Press OK.

Now if you Press Apply Selected instead of Apply All and then select the Rule Set you want, only those Rules are checked.

I have about hundred entities/tables and I would like to navigate right to a particular entity/table in the diagram. Is there a way to do it?

Yes. In Browser right-click on the entity/table name you want to navigate to and select Go To Diagram.

If your entity/table is in several diagrams they all are shown to you. Select the diagram you want to see. Now you will have that diagram opened and the entity/table selected.

I am used to see the DDL and only after seeing that I realize I have forgotten to design something. I know File | Export | DDL File will give me the DDL for the table but it takes me many clicks to get that. Is there any easier way to see the DDL for a table?

Yes! Right-click on the table in the Relational Model canvas and select DDL Preview:

That will show you the DDL for that table.

TIP: if you keep the DDL Preview screen open and click on another table in that Relational Model, you will get the DDL for that table displayed in the screen.

Note: if the physical model is closed the DDLs will be very general without any physical properties. If the physical model is closed the DDL might look like this:

CREATE TABLE Customer
(
CustNO NUMBER (10) NOT NULL ,
Name   VARCHAR2 (100) NOT NULL
) ;
ALTER TABLE Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( CustNO ) ;

And if it is open it might look like this:

CREATE TABLE Customer
(
CustNO NUMBER (10) NOT NULL ,
Name   VARCHAR2 (100) NOT NULL
)
PCTFREE 10 PCTUSED 40 TABLESPACE TableSpace1 LOGGING STORAGE
  (
    PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  ) ;
ALTER TABLE Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( CustNO ) ;

I have defined the Classification Types for my entities but it does not show those types in the ER diagram?

Right-click on the ER diagram canvas (Logical Model canvas) and select View Details, Classification Types: