Ora 02021

Distributed transaction in Oracle ( Over Oracle DBLink)

Ora 02051
Ora 02019 Error
Ora-02021 Dblink

ORA-20000: ORA-01403: no data found: This procedure cannot continue because. ORA-20005: ORA-02021: DDL operations are not allowed on a remote database.
ORA-02011: duplicate database link name Cause You tried to create a database link (using the CREATE DATABASE LINK command), but a link with that name already exists.
ORA-00942: table or view does not exist SQL grant select on emp@scott.testlink to test; grant select on emp@scott.testlink to test. ERROR at line 1: ORA-02021: DDL operations are not allowed on.

ORA-02021: DDL operations are not allowed on a remote database Message: Have been requested to create a user who can select only a few tables on another database. Has different users but the important is ESCHER. Question: I am getting this ORA-02021 error: ORA-02021: DDL operations are not allowed on a remote database. Answer: The oerr utility says this about the ORA-02021 error: ORA-02021: DDL operations are not allowed on a remote database. Cause: An attempt was made to use a DDL operation on a remote database.

To fetch the data from one server to and other Oracle server over DBLink, I experienced the following facts of Oracle Distributed transactions:

Security issue:

–We cannot create Public synonym for the remote object accessed over Private DBLink of other Database.

–It’s allowed to create private synonym for remote object, but you cannot grant the access over this synonym to any other schema.

If you try to provide the grants to other schema Oracle raises an error:

[ORA-02021: DDL operations are not allowed on a remote database]

Ora 02051

“In an all you can access remote objects over private DBLink in the same schema where DBLink is created”.

Fetching the Ref Cursor at Remote site:

Let’s say we have two sites involved in Distributed transaction, Server1 and Server2. The Ref Cursor opened on Server1 procedure, cannot be fetched at Server2 site. If we try to fetch this cursor oracle raises an exception:

[ORA-02055: distributed update operation failed; rollback required

Solutions:

2.Provide select grant and use select command over DBLink from initiator site instead of opening the Cursor.

Transaction issue:

If remotely called procedure/Function has Out or In-Out argument, we cannot use commit in remote procedure.

ORA-06512: at “DBA.PR_DATATRANFER”, line 332

ORA-06512: at line 8]

Solutions:

1.Use PRAGMA AUTONOMOUS_TRANSACTION (if possible).

2.Simplify your transaction and check if the transaction initiator site can take care of commitrollback of transaction.

Use of Global temporary table in Distributed transaction :

In your distributed transaction, if remote server procedure/Function is using the GTT (on Commit DeletePreserve rows). Even after using commit or rollback Oracle does not release the locks on temporary table (Ref: http://www.dbasupport.com/forums/showthread.php?t=58263). And when you run the same procedure again oracle raises an exception:

[ORA-14450 : attempt to access a transactional temp table already in use

ORA-06512: at “LIVE.PR_DOWNLOADEDDATA”, line 74

Only alternative to release the existing locks is Disconnect and re-connect the session.

Oracle Documentation says that you cannot use GTT in Distributed transactions: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm

Learn the cause and how to resolve the ORA-02291 error message in Oracle.

Description

When you encounter an ORA-02291 error, the following error message will appear:

ORA-02291: integrity constraint <constraint name> violated - parent key not found

Cause

You tried to reference a table using a unique or primary key, but the columns that you listed did not match the primary key, or a primary key does not exist for this table.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error commonly occurs when you have a parent-child relationship established between two tables through a foreign key. You then have tried to insert a value into the child table, but the corresponding value does not exist in the parent table.

To correct this problem, you need to insert the value into the parent table first and then you can insert the corresponding value into the child table.

For example, if you had created the following foreign key (parent-child relationship).

Then you try inserting into the products table as follows:

Ora 02019 Error

You would receive the following error message:

Since the supplier_id value of 5000 does not yet exist in the supplier table, you need to first insert a record into the supplier table as follows:

Ora-02021 Dblink

Then you can insert into the products table: