Oracle Database

Pivot and Unpivot

These keyword was introduced in Oracle 11g. The UNPIVOT operator transforms the data arranged on a column into separate rows. With this utility it is possible to convert the display of the data to have them displayed on columns to have them on lines. In this example we talk about the prices associated with a ticket: UNPIVOT : SELECT * FROM tickets ; Applying the unpivot: pivoting on the ticket column it is possible to see the opening and closing price on separate lines. SELECT ticket,to_char(ticket_date,’YYYYMMDD’)ticket_date,price_type,price FROM tickets UNPIVOT ( price FOR price_type IN ( opening_price AS ‘OPEN’, closing_price AS Read More

ORACLE – enq: TX row lock contention

Enqueues are locks that coordinate access to database resources. enq: wait eventindicates that the session is waiting for a lock that is held by another session. The amount of wait time associated with this wait event is excessive and can be responsible for performance issues observed in the application. TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.  Some situations of TX enqueue: – Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another Read More

ORACLE 12C: Dataguard Far Sync

Oracle Dataguard Far Sync instance is a new feature introduced in Oracle Database 12c, which simply works like a archive/redo repeater.. Far sync instance is something different than the Oracle Database instance, as its mission is accepting redo from the primary database and then shipping that redo to other members of the Oracle Data Guard configuration.. Far Sync instance is an instance without a database.. Although; it has controlfile, and capability to read from standby redolog files and write them into its local archive log files, Far Sync instance does not map any database file, in other words, does not Read More


When you work with xml file you should be solve the scenery that have an node with its properly sub node. In our case the following nodes () ; () . It is quite delicate find exactly approach that gives to you the requirement expected. The problems that I have got they were two different: – Duplicate row – ORA-19025 EXTRACTVALUE returns value of only one node or ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence The following image shows the scenery described: Duplicate row means the query extract the values that are not Read More