ORA-00001: unique constraint

Hello,

Today I got below error message in my replication env.

ORA-00001: unique constraint

As per my investication

1/ There is NO duplicate records in my base table & materialized view.

In my master & materialized view site having some database trigger to generate auto-increment values using trigger.

As per my trigger from mview site some values generated; the same values generated from master site.

That is the reason implemented DBMS_MVIEW.I_AM_A_REFRESH package from mview site.

For Example:

Master Site

SQL> create table tst_tri (a int);
Table created.

SQL> create or replace trigger tst_tri_after after insert on tst_tri
2 begin
3 delete from tst_tri;
4 end ;
5 /
Trigger created.

SQL> insert into tst_tri values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tst_tri;

no rows selected

From Materialized View site:

SQL> create or replace trigger tst_tri_after after insert on babu.tst_tri
2 begin
3 if dbms_mview.i_am_a_refresh then
4 delete from babu.tst_tri;
5 end if;
6 end;
7 /

Trigger created.


SQL> select * from babu.tst_tri;

no rows selected

SQL> insert into babu.tst_tri values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from babu.tst_tri;

A
----------
1

SQL> drop trigger sys.tst_tri_after;

Trigger dropped.

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

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.