Ora 02021

Posted on by

  1. Ora-02021
  2. Ora 02019 Error
  3. Ora-02021 Dblink
  4. Ora 00214
  5. Ora 04021 Timeout Occurred Waiting To Unlock
  6. Ora 02051
Ora

Ora-02021

Distributed transaction in Oracle ( Over Oracle DBLink)

Ora 02019 Error

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

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. ORA-02011: duplicate database link name Cause: The database link name specified in a CREATE DATABASE LINK statement already exists. Action: Either specify a new name or drop the existing link. The ORA-04021 timeout error is most likely caused by another session locking/using a package that you are attempting to execute. Personally, I came across this error whilst trying to recompile a PLSQL package in the database. 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. For example, “CREATE TABLE tablename@remotedbname ”. Action: To alter the remote database structure, you must connect to the remote database with the appropriate privileges.

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]
“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 00214

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

Ora 04021 Timeout Occurred Waiting To Unlock

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

Ora 02051

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