content top

How to Order Siblings in Hierarchical Queries

In a hierarchical query, if you specify either ORDER BY or GROUP BY, the hierarchical order will be destroyed. If we need to sort the rows of siblings of the the same level (while keeping the hierarchical order) we can to use ORDER SIBLINGS BY clause. Let’s create a sample table:

Let’s list these rows in a hierarchical order:

Read More

Update Joins in Oracle

We have an application running on Ms SQL Server, and we want to move it to Oracle. While I examine the custom queries, I see that “update joins”.

UPDATE table_name
SET colname = value
FROM source_table JOIN source_table2
WHERE condition

This syntax helps user to join tables and update together. Oracle does not support this syntax, so I searched to find an alternative way.

It’s suggested to use subqueries:

Let’s say we want to increase salaries of sales stuff:

Read More

DB_ULTRA_SAFE Parameter

DB_ULTRA_SAFE is a new parameter introduced in 11g. It provides an integrated mechanism to offer protection from various possible data corruptions. and provides critical high availability benefits for Oracle Database. Setting DB_ULTRA_SAFE initialization parameter will configure the appropriate data protection block checking level in the database. It will control DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT.

DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for database blocks.

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk.

DB_LOST_WRITE_PROTECT enables or disables lost write detection. A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.

Read More

How to Generate XML from the Oracle Database

Oracle provides plenty of XML generation methods, I’ll try to demonstrate two of them: SYS_XMLGEN and DBMS_XMLQUERY.

I’ll start with creating a simple table and fill it with sample data:

To be able to use SYS_XMLGEN, I’ll create an object to map our data:

Read More

Automated Maintenance Tasks in Oracle 11g

Oracle Database 11g comes with three predefined automatic maintenance tasks:

  • The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
  • The Automatic Segment Advisor task provides advice on which database segments have free space that can be reclaimed.
  • Automatic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommendations to improve the statements.

You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database. as shown in the following example:

These tasks are enabled by default.

Read More
Page 39 of 43« First...3738394041...Last »
content top