After the post on models for storing XML data in Oracle and examples of binary XML storage, here’s a detailed look at the rigorous and performant structured XML storage or object-relational storage.
{ 1 comment }
DBA
The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema.
This post shows working examples of binary XML storage
1.1. without XML Schema
1.2 with XML Schema
{ 0 comments }
Oracle partitioning features in 11G brought with it a beautiful new twist to range partitioning — the ability to create partitions on-the-fly.
Hello range interval partitioning.
{ 2 comments }
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.
{ 0 comments }
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.
{ 1 comment }
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.
{ 4 comments }
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.
{ 4 comments }
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.
{ 0 comments }
How will you find out the number of rows in each table in a schema in Oracle?
Here are two approaches with their pros and cons.
{ 0 comments }
