OOW16

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:

SQL-231x300

https://www.amazon.com/Real-World-SQL-PL-Experts/dp/1259640973/

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.

DeleteObject

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:

DeleteObject2

This is a subview:

DeleteObject3

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

The Logical Model still has the entity Product:

DeleteObject2

But the subview does not:

DeleteObject4

 

 

 

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.

NameSub1

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

NameSub2

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):

NameSub3

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:

NameSub6

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:

NameSub7

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🙂

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:

Prop1

Then press Propagate Properties.

Prop2

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

Prop3

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

Prop4

Then press OK.

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

Prop5

 

 

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.

DesignRules1

 

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.

DesignRules2

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:

DesignRules3

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:

DesignRules4

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:

DesignRules5

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.

DesignRules6

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

DesignRules7

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.

GoToDiagram

 

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:

OOW

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

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

Enjoy Summer!

Cheers,

Heli

Follow

Get every new post delivered to your Inbox.

Join 27 other followers