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:

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.

Enabling and Disabling Database Options

When you select the “Enterprise Edition” option during Oracle installation, all the components which are licensed under “Enterprise Edition” get installed by default. The idea is to install all these options and then disable those options that were not licensed.

If you need additional functionality such as partitioning some time after the initial installation, you can enable (or disable) the specific component functionality at the binary level:

1. Shutdown all database instance(s)/service(s) running from the Oracle Database Home
2. Run the following relink command to disable the option at the binary level:

Here is the list of database options and switches:

Database Option ON OFF
Data Mining dm_on dm_off
Data Mining Scoring Engine dmse_on dmse_off
Database Vault dv_on dv_off
Label Security lbac_on lbac_off
Partitioning part_on part_off
Real Application Clusters rac_on rac_off
Spatial sdo_on sdo_off
Real Application Testing rat_on rat_off
OLAP olap_on olap_off
Automatic Storage Management asm_on asm_off
Context Management Text ctx_on ctx_off

3. Startup the instance and check if the option is enabled:

Implicit Conversion And ORA-01722

I’ve an email from a reader that he gets ORA-01722 error from a query. he says that the query works well on same structured tables on different schemas. After a short conversation, I figured out that it’s about implicit conversion mechanism.

Let’s create a sample table to demonstrate the problem:

You may notice that we use “varchar” column to keep “number” values. That was the exact case my reader faced.

Let’s write a simple query:

Oracle 11g New Feature: RMAN Data Recovery Advisor

In Oracle 11g, RMAN provides a new advisor called Data Recovery Advisor (DRA). DRA has several new commands to help DBAs for performing recovery tasks.

To list of database failures, you can issue:

DRA will consolidate related failures into a single failure. You can list a failure individually by issuing “list failure X detail” command.