Custom Search . . .

Thursday, November 13, 2008

ORA-06502: PL/SQL: numeric or value error

Hello,

Database Version: 10.2.0.3
Os: Sun Solaris 5.9

Recently we upgrated our production database 9.2 to 10.2.0.3 & I got below error in my production environment.

On the Application builder page, when attempting to click details to view the applications, the following error occurs:

ORA-06502: PL/SQL: numeric or value error: NULL index table key value

Permanent solution:Upgrade database to 10.2.0.4 or above


OR


Download and apply database
Patch 5705795. If the version for your database is not available , create a new Service request from metalink under product RDBMS to request for a patch

Monday, November 10, 2008

Let me analyze something about partition:

Let me analyze something about partition:

Partitioning: Very large tables & indexes by letting you decompose them into smaller more manageable pieces called Partition.

Partition very useful for many different type of application particularly to handle very large volume data & OLTP environment.

Advantages:

  1. Partition enables data management operation such as data loads, index creation, and rebuilding & Backup/recovery at the partition level rather then entire table.
  2. Partition improves query performance.
  3. Partition increases the availability of mission critical database.

Method of Partition:

  1. Range Partition.
  2. List Partition.
  3. Hash Partition.
  4. Composite Partition.


What is Rage Partition?

Range partition maps data to the partition based on ranges of partition key values that you establish based on partition. It’s most common type of we are using DATE column.

Ex:

….

..

PARTITION by RANGE (hiredate)

(

Partition jan2000 values less then (TO_DATE('02/01/2000','MM/DD/YYYY')),
Partition feb2000 values less then (TO_DATE('03/01/2000','MM/DD/YYYY')),

)

What is List Partition?

List partition enable explicitly control how rows map to the partition.

Example:

..

Partition by LIST (sales)

(

Partition sales_in values (‘INDIA’),

Partition sales_us values (‘USA’)

)

What is Hash Partition?

Hash partition enable easy partition of data that does not lend itself LIST & RANGE partition.

Composite Partition:

Composite partitioning partitions data using the range method, and within each partition, sub partitions it using the hash or list method

When To Partition Table:

Here are some pre-request to implement partition table.

  1. Table size should be more than 2 Gb.
  2. Tables contain Historical data’s, in which new data’s added in new partition.