DBA

Tasks typically handled by a DBA, such as installation, patching, configuration, migration, security, troubleshooting, monitoring, backup, recovery.

Grant SELECT on V$ Views

Grant Select on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error:

SQL> select version
  2  from v$instance;
from v$instance
     *
ERROR at line 2:
ORA-00942: table or view does not exist

The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as: log in as sys and grant select on v$ views to the user.

Appearances are deceptive, they say. Try that and you will get an ORA-02030 error.

Read more

Access Control List (ACL) in Oracle 11G

Access Control Lists in Oracle

I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade.

The code failed with the error:

declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 47

Oracle 10G used to be happy as long as the user running network packages like UTL_HTTP had execute permission on the package. Oracle 11G and above are not so easy to please (and rightly so!) — they enforce extra security, which means you need more access control configuration to get this working.

Read more

ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix

Maximum Key Length

ORA-01450 might crop up when a table index is being created in the database.

ORA-01450: maximum key length (6398) exceeded

Oracle documentation has this to say about the error:

Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system.

The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns.

Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.

Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system.

The action suggests choosing index columns differently so as to remain within the index length limit.

Things are not always so simple though. When faced with this error while installing Oracle’s standard products such as FMW components or OBIEE, it is not in one’s hands to follow this advice and fiddle with the index columns.

How does one fix this error, then? This post suggests possible root causes and solution for ORA-01450 when changing the index itself is not a viable option.

Read more

What are CBO and RBO?

CBO and RBO Optimizer

In large applications that juggle tons of data, it isn’t enough to get a query to run without errors. The query must also perform well — get the results in fastest possible time, using the least possible resources.

This is where Oracle’s inbuilt optimizers come into play. Till Oracle 9i, Oracle server provided the options of CBO (cost-based optimizer) and RBO (rule-based optimizer). RBO has been desupported by Oracle 10G onwards, but it is still worth knowing about to understand the evolution of Oracle’s optimization techniques.

This post explores the key differences between CBO and RBO.

Read more

Replace Duplicates in ID Column with Unique Values

Replace Duplicate Ids with Unique Values

A common scenario: in the development environment, a table’s ID column is missing its unique constraint, and developers have been creating their own data for testing. Suddenly someone realizes that the ID column has duplicates. The DBA is summoned. "Enable the unique key", the DBA is told, "but do not delete our data."

Can the DBA escape this catch-22? For sure, says this post — with an approach of replacing *only* the duplicate IDs with unique values, and letting everything else stay untouched.

Read more