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.

Optimum Size of The Online Redo Log Files

The Oracle Database Performance Tuning Guide recommends switching logs at most once every twenty minutes. If our online redo logs switch once every 10 minus during peak hours, our redo logs should be increased to 2 times larger then their current size.

Here’s a simple query for calculating recommended size of redo log files:

How to Develop Java Stored Procedures

I’ll try to demonstrate how we can use loadjava to load our Java class into Oracle Database and use its methods as stored procedures. Before we begin to write the Java codes, I’ll create a simple table. When I call my java stored procedure, it will insert a record to this table.

I created this table in HR schema because I’ll load my java object in HR schema. If you’ll use another schema, then do not forget to load your java code in same schema or set required permissions. This is the main method I’ll use as my stored procedure: