The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

martedì, agosto 22, 2017

Using XML in Oracle: Part 01

I don't know very much about XML. Anyway, there are some cases where like DBA, I can use it. For example, if I want to read the HIGH_VALUE and other LONG columns or if I want to read the hints used by a SQL profile. So, with the following series, I would like to understand better how to use XML. Which I write here are just a minimum knowledge on XML. I do not pretend to explain to you how to use XML in all its form.

What you read, in this first part is a collection of information taken from several links. You can find them in the References section.

Introduction to XML

Extensible Markup Language (XML) is a simple, very flexible text format derived from SGML (ISO 8879).

XML code is similar to Hypertext Markup Language (HTML). Both XML and HTML contain markup symbols to describe page or file contents. HTML code describes Web page content (mainly text and graphic images) only in terms of how it is to be displayed and interacted with.

Also, while HTML is not case-sensitive, XML it is.

The basic building block of an XML document is an element, defined by tags. An element has a beginning and an ending tag. All elements in an XML document are contained in an outermost element known as the root element. XML can also support nested elements or elements within elements. This ability allows XML to support hierarchical structures. Element names describe the content of the element, and the structure describes the relationship between the elements.

XML is much more accurate than HTML when it comes to closing tags: each one always has a closure.

Tags can also be defined as empty tags but their syntax is '<' TAG/ '>'

Elements can be better characterized by specifying a list of attributes.

So we have a "start-tag" (STag), an "end-tag" (ETag) and a "tags for for Empty Elements" (EmptyElemTag). We can define them as

STag ::= '<' Name (Attribute) '>'
ETag ::= '</' Name '>',
EmptyElemTag ::= '<' Name (Attribute) '/>

(In Oracle there isn't the EmptyElemTag, but I report it because it is a simple concept and it shows the difference against HTML).

There is always a prologue and is very important: it contains useful guidelines for programs that will use the document. Specifically, the XML document begins with '<' ?xml version=“1.0”? '>'

XML example:

The text: "Rex Stout was an American author of many yellow novels featuring famous Nero Wolfe and his aide and storyteller Archie Goodwin."


'<'?xml version=“1.0”?'>''<' author nationality="US" '>''<'name'>'Rex'</'name'>' '<'surname'>'Stout'</'surname'>''</' author '>' was an American author of many '<' genere '>'yellow novels'</' genere '>' featuring famous '<' protagonist '>''<'name'>'Nero'</'name'>' '<'surname'>'Wolfe'</'surname'>''</' protagonist '>' and his aide and storyteller '<' storyteller '>' '<' protagonist '>''<'name'>'Archie'</'name'>' '<'surname'>'Goodwin'</'surname'>''</' protagonist '>''</' storyteller '>'.

Graphic view of an XML tree

How to decide whether to represent an entity component as an attribute or as a separate entity? In general, it is difficult or even impossible to make an objective choice, as there is no clear advantage in either solution. In these cases, the programmer is free to use the form he or she prefers.


[4] (ITA)

lunedì, agosto 21, 2017

Deadlock: an example

Some days ago, I encountered an "ORA-00060: Deadlock Detected" exception. So I would like to summarize what I found. In order to do this, I simulated this exception.

First of all, I have following table

Figure 01 - Table NAMES

At this point, I started two sessions,: session 01, in red, session 02 in green. Both make an update on two different rows

Picture 02 - Session 01, update the row with ID=1

Picture 03 - Session 02, update the row with ID=10

Now, without committing the previous operation, the red guy (session 01) update the row with ID=10

Picture 04 - Session 01, update ID=10 and wait

Because the row with ID=10 is already updated by the green guy (session 02 didn't commit), the session 01 has to wait. After, the green guy, update the row with ID=1

Picture 05 - Session 02, update ID=1 and wait

So, as you can see session 01 and session 02, are locking each other. Because of this stalemate, Oracle resolves it for everyone. It rollback the last statement of who (the session 01, in this case) started the deadlock.

Picture 06 - Session 01 will rollback the last statement

and just the last statement (the "UPDATE") will be rollbacked, not the entire transaction (I mean the "SELECT FOR UPDATE, here). In fact, the session 02, is still waiting. This means that the session 01 is still locking the row with ID=1 row.

The session 01, the one that started the deadlock, is the victim.

Picture 07 - Extract of the trace file (1)

Picture 08 - Extract of the trace file (2)

What happens inside the db after rollback due to deadlock? I check V$SESSION and V$TRANSACTION, In order to see the active sessions. Also I check the V$SQL tho check the current statement.

Picture 09 - Query the data dictionary

Joining V$SESSION with V$SQL, you can see that the session 01 is just running the UPDATE with ID=1 (actually he run the "SELECT FOR UPDATE", that is, his first statement).

Picture 10 - V$LOCK

Interesting is querying the V$LOCK. Based on information of orafaq, I had following informations.

Both sessions make an exclusive lock (IMODE=6), but the green guy also requested an exclusive lock (REQUEST=6). As you can see, the REQUEST=6 of the session 02 is made on the same resource of the IMODE=6 made by session 01: ID1=262284 and ID2=3375 These values are the same in V$SESSION (P2 and P3).

In V$LOCK_TYPE view you can see the meaning of the LOCK TYPE.

Picture 11 - Explaining the lock

Anyway the Doc ID 29787.1 on MOS, can give you more information

Picture 12 - Doc ID 29787.1

Pictures 07 and 08, show us an extract from a trace file of the Oracle 12c. The output of the previous version is different

Picture 13 - The alert.log output in 11c is different than 12c

In the alert log, you can read explicitally the "Deadlock detection". In order to understand the old output (Picture 12), you can read the Doc ID 62365.1 on MOS.

Picture 14 - Extract from Doc ID 62365.1

mercoledì, agosto 16, 2017

Dataedo software, Part 06: First impression (until now)

There is still something to say on Dataedo5 software (in the next posts), but I can write my first impression.


  • The software is really smart
  • Good documentation for your support
  • A good help to organize your data
  • Price not so high
  • Great Blog (see below)
  • More databases are supported


  • Some security stuff should be implemented: I would like that a scheme with the minimum privileges could describe objects of other users
  • Some performance issue should be optimized: The more data you have to import in Dataedo, the more time you have to wait. This is a not scalable solution. The import flow should be divided into more steps: one for each type
  • The code of Triggers, Procedure and Functions need to be absolutely shown in the final doc.
  • The type of objects reported in the final document is too low: are missing, for example, Packages, Types, Jobs, Index etc.

Just to understand what I mean, the following picture is taken from Toad and show you what Dataedo report in it documentation (in red) and which are the types that Oracle use

Oracle type of object

So, in my opinion, the software is good but not enough mature. I think that Dataedo could become a really powerful software for documenting an Oracle schema, but a lot of work should be done.

Pay attention, please. Those impressions are based on what I saw until now. Dataedo5 has more than simply this. I'll try to explore them in the next months.

There is a thing I want to speak about, before the next post: the Dataedo Blog. You can find some really interesting post like "6 Useful Oracle Data Dictionary Queries Every DBA Should Have"

Dataedo software, Part 05: Reporting - 06 (EXCELL)

Object per sheet

In this case, each sheet describes an object:

This is why this file is bigger of the last one. Also in this case, there is the "Module" sheet with "Other" link: it goes to "(Module) Other" sheet.

The content of this sheet is empty

This is how the object summary appears:

Table sheet

View sheet

Procedure sheet

Function sheet

The last thing is to show the details about the objects

Table details

View details

Unfortunately also, in this case, no code is shown

Procedure details

Function details

lunedì, agosto 14, 2017

Dataedo software, Part 05: Reporting - 05 (EXCELL)

Type per sheet

As you can see, each sheet contains a type

The first sheet is "Module" that show "Other". This is newer: it is not present in the PDF and HTML format. In fact following two picture show how they appear

PDF index

HTML format

Is this a bug? I'll ask the Dataedo guys. Anyway, following pictures show how the EXCELL format appear.





Also the "Columns" description and "Relations" have a separate sheet




And there are other sheet for "Unique keys"

Unique keys

Unique Keys

In EXCEL, the "Triggers" have a separate sheet


And....this is really strange: a sheet for In/Out parameters of the Procedures and Functions

It's like "I can't show you the code of the code of Procedures and Functions, but I tell you which is the interface". Ok. I agree with this, but why Dataedo show this kind of information only in the EXCELL format?

(To be continued)

Dataedo software, Part 05: Reporting - 04 (EXCELL)

The last thing I have to see is the EXCELL format

How you can see, the "Object per sheet" template is bigger than the "Type per sheet". It will be clear when you see the pictures.

(To be continued)