It’s that time of year again, OOW is getting closer… This year I will have two presentations:

  • Introduction to the Big Data World [UGF2116]
    Sunday, Sep 18, 11:45 a.m. – 12:30 p.m. | Moscone South—305
  • Design Your Databases Using Oracle SQL Developer Data Modeler [CON2120]
    Monday, Sep 19, 4:15 p.m. – 5:00 p.m. | Park Central—Concordia

I hope to see you there!

Before OOW we will have the Oracle ACE Director briefing at the Oracle HQ. That is one of the highlights of my year🙂

Oh, and this will be a bit special OOW too because my new book just came out:



I wrote it together with my good friends Arup, Brendan, Martin and Alex. Chet, our own Oraclenerd, was our tech editor. He also wrote the foreword together with my very own Oracle-dad Tim Hall. I hope you like the book! And if you want it signed OOW is a great place for that because we will all be there🙂


See you soon in San Francisco!

— Heli


Delete Object/Delete View

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

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:

Name   VARCHAR2 (100) NOT NULL

you get a DDL like this:

Name   VARCHAR2 (100) NOT NULL



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🙂

Propagate Properties

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:




Design Rules

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.




Go to Diagram

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.



Busy times

At the moment I am on vacation. I have started a healthier life: I exercise every day and I eat healthy. That’s what I have been mainly doing on my vacation🙂

And it is very good because the rest of the year will be extremely busy! I still have my daily job at Miracle, my studies, research and teaching at the University and some travelling to speak in various events. I try to cut the travelling part down a bit to be able to work more but… First I got an invitation to come and speak at the POUG event in Poland, then 2 talks were accepted to DOAG2016 and on the content catalog of Oracle Openworld 2016 I found these:


But three trips is much less than it has been lately🙂

This Fall/Autumn will be busy but extremely fun and interesting!

Enjoy Summer!




Get every new post delivered to your Inbox.

Join 27 other followers