Distributed Transaction - Ensure Commit

Jun 26, 2013

Query in format Query@DB_Link is sometimes dropping transactions. Move it to a package on target dababase "B" called from DATABASE "A" via package.procedure(varaibles);

Is that all I need to do to get the transaction to always go? I tried a commit in the package body, with repeated attempts if it fails. Btu when I use commit, the package doesn't work. (Compiles fine) Why would the commit fail?

My goal is to make that the transaction succeeds.Code for what I tried below. the COMMIT is commented out - if I uncomment it, the transaction fails. I have done this before calling an oracle SP from SQLServer and it worked

Comments:

The loop is to avoid an endless cycle..The concept is to perform the update, commit it, if the commit fails, rollback and try again.I'm not sure if I even need a loop, but I don't know if the unresolved transaction would get fixed by the remote DB, or dropped.

PROCEDURE BTC_UpdatePart
(
vPart_ID varchar2,
vEngineering_Mstr varchar2,
vCommodity_code varchar2,
vDef_orig_country varchar2,
vDescription varchar2,
[code]...

View 1 Replies


ADVERTISEMENT

Locked Resources By Distributed Transaction?

Jun 15, 2012

db 10.2.0.4

select * from DBA_2PC_PENDING shows pending transactions in prepared state.

How can I know the resources (e.g. tables) locked by these transactions?

View 2 Replies View Related

SQL & PL/SQL :: ORA-02049 - Timeout - Distributed Transaction Waiting For Lock?

Mar 2, 2010

I am getting ORA-02049: timeout: distributed transaction waiting for lock , how to resolve this type error

View 3 Replies View Related

Server Administration :: Identifying Two Databases Involved In Distributed Transaction?

Oct 24, 2013

I am on Oracle 11.2.0.3 on Linux. In my production database, I am getting this alert, in the alert log:

--the below two lines are from alert log.
Error 604
trapped in 2PC on transaction 50.73.546578. Cleaning up.

--query from a data dictionary view
SQL> select count(*) from DBA_2PC_PENDING;
COUNT(*)
----------
1

When I query the production database I see that there is one row in the DBA_2PC_PENDING view. But I dont' know how to identify which are the databases that are involved in this distributed transaction. That is my first issue - how to identify which are the two databases that are involved in the distributed transaction?

View 10 Replies View Related

Server Administration :: Can Oracle Always Guarantee Data Consistent In Distributed Transaction

Mar 31, 2013

I have read almost all docs about distributed transaction on tahihi.oracle.com website,But I can find a statment about this:

Can Oracle always guarrantee the data consistent in a distributed transaction?

For example,there is a distributed transaction on node a,node b and nod c.node b and node c informed node a they were prepared,so node a committed,and inform node b and node c commit.then node b committed and feedback,but network on node c broken at this point,So node a can't not get feedback from node c,but node a and node b has been committed, so what will Oracle do in this condition?

If node c rollback the data on local node, consistent in this distributed transaction was failed ,yes?

View 5 Replies View Related

SQL & PL/SQL :: Commit By Changes In Database After Second Transaction?

Aug 26, 2012

I hv a situation where a webservice interacts with the database.

Here the webservice will first make a request to database for some operation but i dont want the database to commit changes in first request itself. A response will be sent to webservice further a second request will be sent to database for committing the changes. So can that be done?

View 11 Replies View Related

Oracle Trigger On Transaction Commit

Nov 29, 2007

I am working with an oracle table that is populated by a trigger on another table.. So Table A is an audit of table B. The trigger also uses sysdate to populate a modification Date column on the Audit table.

I was using this modification Date column in a query interface to get changes that happened on the main table after a certain date/time.

The problem is that there is an application that uses transactions to write to table B and sometime this transaction may not be committed for over a minute so the modification Date is not a reliable way to query the table for changes after a certain time.

Is there a way to update the trigger/create a new one where the sysdate that gets written to the audit table is from when the transaction is committed, not when the transaction starts?

View 2 Replies View Related

SQL & PL/SQL :: Insert Statement Which Will Commit Transaction By Itself

May 22, 2010

What is the syntax to write an insert statement which will commit the transaction by it self,not using an exclusive commit statement after wards.

View 2 Replies View Related

Utilize Cluster Key To Ensure Uniqueness / Primary Key Constraint

Oct 11, 2010

I am trying to add a primary key constraint to a cluster table on the cluster key columns whitout creating additional indexes.

Example:

CREATE CLUSTER CL1
(A NUMBER)
SIZE 100;

CREATE UNIQUE INDEX CICL1 ON CLUSTER CL1;
gives

ORA-01715: UNIQUE may not be used with a cluster index

CREATE INDEX CICL1 ON CLUSTER CL1;

CREATE TABLE CLT1 (A NUMBER,B NUMBER,primary key (A) using index cicl1) CLUSTER CL1 (A)
gives

ORA-14196: Specified index cannot be used to enforce the constraint.

CREATE TABLE CLT1 (A NUMBER,B NUMBER, primary key (A)) CLUSTER CL1 (A)

creates an additional system generated index SYS_C....

Is there any way to utilize the cluster key to ensure the uniqueness/primary key constraint of the cluster key columns of table CLT1 ?

View 3 Replies View Related

RMAN :: Optimization - Ensure Skip Those Files Already Taken Backup

Feb 28, 2013

I would like to know that if I enable backup Optimization on then incremental full backup skip any files which was earlier backup? Because we may know that backup Optimization on ensure skip those files which are already taken backup.

Database : oracle 10g 10.2.0.3 .

View 4 Replies View Related

PL/SQL :: Create Constraint That Checks To Ensure (Year) Entered Is Less Or Equal To Current?

Aug 6, 2013

, I'd like to create a constraint (when creating a table) that checks to ensure that the 'Year' entered is less than or equal to the current year (based off SYSDATE). Per the code below, I keep getting the same error, "missing right parenthesis". I've spent more than an hour trying different ways to get this to work, but I've been failing miserably. I am using Oracle 11g Express. 

CREATE TABLE TEST (Name VARCHAR2(7) PRIMARY KEY,  Year NUMBER(4),  CONSTRAINT TEST_YEAR_CK CHECK (Year <= (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')))FROM DUAL); 
*ERROR at line 4:ORA-00907: missing right parenthesis

View 7 Replies View Related

Distributed Operation Not Supported

Jun 12, 2013

I am using the dblink to merge the data. I am using the following merge statement.

merge into APP_USER.USR_NEW_RIGHTS@NEW_RIGHTS t
Using (select 'test' GRANTEE,'TESTxxx'ROLE from dual ) s
on (t.GRANTEE = s.GRANTEE and t.ROLE = s.ROLE)
when not matched then
insert (ID,GRANTEE,ROLE,XRIGHT,COMPANY,OWNER,TABLENAME)
values ('','test','TESTxxx',null, null, null, null);

I know that I have to set a commit and it's working when I insert information's with a normal insert statement via database link, but it seems that merging doesn't work.

View 1 Replies View Related

ORA-24756 In Distributed Transactions

Jan 31, 2013

We found out an error from alert log of our Oracle 10.2.0.5 DB :
====================================
..
Wed Jan 30 16:45:01 EAT 2013
DISTRIB TRAN bea1.67AA54355C4A74ECDEE0
is local tran 6.42.332492 (hex=06.2a.512cc)
insert pending prepared tran, scn=8151148567799 (hex=769.d6509cf7)
Wed Jan 30 16:45:02 EAT 2013
Errors in file /oradata/sfapdb/bdump/sfapdb_reco_2739.trc:
ORA-24756: transaction does not exist
Wed Jan 30 16:45:02 EAT 2013
Errors in file /oradata/sfapdb/bdump/sfapdb_reco_2739.trc:
ORA-24756: transaction does not exist
..
====================================

There is no useful information from the trace log as shown below:
====================================
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /ap/oracle10
System name:HP-UX
Node name:scvap2
Release:B.11.23
Version:U
Machine:9000/800
Instance name: sfapdb
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 2739, image: oracle@scvap2 (RECO)

*** SERVICE NAME:(SYS$BACKGROUND) 2013-01-30 16:45:01.941
*** SESSION ID:(1749.1) 2013-01-30 16:45:01.941
*** 2013-01-30 16:45:01.940
ERROR, tran=6.42.332492, ose=0:
ORA-24756: transaction does not exist
*** 2013-01-30 16:45:02.059
ERROR, tran=6.42.332492, session#=1, ose=0:
ORA-24756: transaction does not exist
====================================

I also found out there are some records (trans_id = "6.42.332492") in SYS.PENDING_TRANS$/ SYS.PENDING_SESSION$/dba_2pc_pending with "prepare" status.

This transaction is launched from a Weblogic Server via JDBC. Since it is abnormal so I have no choice to force commit/purge this transaction. Is that a bug of Oracle DB ? or Weblogic coding problem ?

View 2 Replies View Related

Serialize Distributed Transactions

Mar 27, 2012

here

[URL].......

you wrote that

"The serialize transaction isolation level is not supported with distributed transactions."

what is not supported (or example on what is not working properly)?

View 2 Replies View Related

SQL & PL/SQL :: ORA-02064 - Distributed Operation Not Supported?

Apr 20, 2011

When I try to call a database procedure written in Oracle 8.1.7.4.0 with OUT parameter and COMMIT statement from my Oracle 10g environment, I am getting error like "ORA-02064: distributed operation not supported".

I cannot omit OUT/COMMIT statement from the procedure because it is also updating another table from called procedure. I have tried some solutions from my end, but it is not working and same error generating. Like:

1) Moved the update statement with COMMIT statement to another procedure and calling that procedure from main called procedure
2) Creating a job to run the newly created procedure and submit the job from called procedureetc.

View -1 Replies View Related

Precompilers, OCI & OCCI :: Using Distributed Transactions With ProC

Jul 7, 2011

I found this thread, and recently i want to embedded oci in pro*c for distributed transaction reason.

can pro*c deal with distributed transaction? i want to use mts in pro*c .

View 14 Replies View Related

Queueing :: How To Configure Oracle AQ For Distributed Environment

Sep 23, 2013

I have a scenario In which I have say 4 AQ in which I will post the message.Also i have say 2 database.I am planning to create an MDB which will poll on these AQ's,  so whenever I post message the MDB will read it and perform a specific action.I believe I can create only one MDB per queue, if it is so then I have to create 8 MDB.

As there 2 datasource and 4 MDB. Is there any other way to handle this I mean without creating 8 MDB as the Data sources can increase to 10 to 20 so the number of MDB will be 20 to 40.I guess this will affect the application performance. Can I make some changes in application so that only few MDB's are required? 

View 0 Replies View Related

PL/SQL :: Yearly Quantity Distributed Evenly Across 12 Months

Aug 15, 2012

getting the yearly qty distributed evenly across 12 months. I can do this in PL/SQL but want to know is it possible in SQL.

Database version 10g

create table test_tbl( qty number(22),
yr number,
key_val varchar(10)
);

insert into test_tbl values( 500000, 2013, '1');
insert into test_tbl values (56789, 2014, '2');

commit;

This is the output I need to get from a SQL select query

Key     Month     Year     qty
--------------------------------------------------------------
1     1     2013     41667
1     2     2013     41667
1     3     2013     41667
1     4     2013     41667
1     5     2013     41667
1     6     2013     41667
1     7     2013     41667
[code]....

View 4 Replies View Related

Performance Tuning :: NESTED LOOPS JOIN And Distributed Operations?

Oct 30, 2012

I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).

What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?

I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).

SELECT A.POLICY ,
F.MIN_MEMBER_ID,
MIN(A.EFF_DATE) EFF_DATE,
A.EXP_DATE ,
G.DESCRIPTION PROGRAM_NAME,

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Set Transaction Use Rollback?

Aug 31, 2013

Having PL/SQL procedure and calling sys dba procedure to alter rollback segment to online and after that to set the transaction to this rollback. The rollback altered to online but transaction still running on available rollback and not the one I was meant to. The user have executing grant on the sys dba procedure.

View 21 Replies View Related

SQL & PL/SQL :: What Is Autonomous Transaction

Jul 28, 2010

what is autonomous transaction

View 8 Replies View Related

Client IP Of Previous Transaction

Mar 11, 2013

Is it possible for me as a DBA to find IP address of the client who ran a specific transaction or query in past?

Oracle server version I'm using is 11g.

View 4 Replies View Related

SQL & PL/SQL :: DBA Pending Transaction In Database

Jun 16, 2011

application was not starting due to some pending transaction in database.Pending transactions were rolled back by DBA team.To avoid such situations what I thought was, having a job that will call a procedure that will monitor table status everyday and will send the mail. Now that job is working fine for no pending transactions in DBA_PENDING_TRANSACTIONS.

But now I am in doubt if someday there are PENDING TRANSACTIONs in the table DBA_PENDING_TRANSACTIONS, will SELECT * FROM DBA_ PENDING_ TRANSACTIONs query will work as normal or this whole process of monitoring table and sending mail will work fine?

View 3 Replies View Related

SQL & PL/SQL :: Further Changes To Block By Transaction Not Allowed

Jun 6, 2011

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

We are running a code through informatica and are frequently getting the ORA-08007. The error and the code causing it are mentioned below. (The '?' implies a variable which is supplied by informatica). The source is a flat file.

ORA-08007: Further changes to this block by this transaction not allowed

UPDATE table_20
SET capxuser = ?,
capxtimestamp = ?,
capxaction = ?,

[code]...

1. Tried initially by reducing the commit interval to commit at 100 rows. But still getting the same error.
2. DBA has installed the patch # 6790768. But it still fails.

View 10 Replies View Related

SQL & PL/SQL :: How To Get First Transaction For Every Unique Client

Nov 2, 2010

I have a table called pf_stock_txns which just stores all of a clients transactions

If I do select distinct(client_id) from pf_stock_txns then I get back a unqiue list of all clients.

However, I need a query that will give me the first transaction for every unqiue client.

View 17 Replies View Related

SQL & PL/SQL :: Retrieve Records Of 2 Way Transaction

Apr 13, 2011

I want Query on this table. EXAMPLE TABLE:

SENDER RECEIVER AMOUNT

A B 1000
B C 2000
A D 3000
C A 3000
B A 4000
A B 5000
A B 1000
B C 2000
C A 3000
B A 4000
A B 5000
A C 10000

Dynamically I am giving user name A. I want to retrieve the data how much money user B taken from user A and how much money user B given to A lly for C and D.

CONDITION: I want to retrieve the records of 2 way transaction that means user A sent money to user B and user B must sent money to user A. I don't want one way transaction records i.e user A sent money to D but user A didn't get money from user D like this records.

OUTPUT LIKE:

USER MONEY_TAKEN_FROM_A MONEY_GIVEN_TO_A
B 12000 8000
C 10000 6000

View 14 Replies View Related

SQL & PL/SQL :: Can Use Autonomous Transaction In Procedure

Feb 9, 2011

procedure1

inser...
update....
delete...
insert...
update..
delete..
end procedure1;

i want save the transaction for specific stmt how do i do it

View 1 Replies View Related

Unexplainable Exclusive Transaction Locks?

Aug 23, 2010

I have a job which runs the procedure below concurrently in 4 different sessions; It updates two tables.

PROCEDURE UPDATE_TAB_1_AND_TAB_2( traceIdTab IN pl_sql table,
individualIdTab IN pl_sql table,
fileSeqIdTab IN pl_sql table,

[Code].....

Each session is passed a sequence generated file ID; so the records processed in each session are completely different.

However, everytime this job runs, without failure, ONLY TWO sessions process concurrently, while the other two sessions are blocked. Once the first two have finished, then the remaining two sessions start work.

I have being monitoring the sessions on each run daily, and realized that the first two sessions processing are each holding a transaction exclusive lock on objects; hence blocking the other two sessions.

When I tried to find the objects that the two running sessions have a lock on, I cant seem to find anything by searching DBA_OBJECTS.object_id. However, when I look for the objects that are being waited on, its either a partitioned table or a partitioned index on either table_1 or table_2.

At first, I thought it was an ITL lock and after much analysis, I did not find anything which definitely pointed to an ITL lock. However, just to make sure, I increased the INITTRANS value from 2 to 5, but the problem did not go away.

At this point, I have applied all I know to understand the origin of the TX lock and how I can work around this situation, and decided to ask higher powers in Oracle for advise.

View 4 Replies View Related

SQL & PL/SQL :: Retrieve IP Address For Client Who Do The Transaction

Apr 19, 2011

am using oracle 10g

and i want to retrieve IP address for client who do the transaction

and i try to using some selects like select terminal from v4session but it return the IP for application server

so i think to use commands ipconfig

how can i call windows commands by oracle

View 8 Replies View Related

SQL & PL/SQL :: Manage Transaction When Called From Java?

Jun 21, 2010

A Java program would call a sequence of 5 PLSQL procedures, and they all need to participates the Tranasction defined in Java.

What do I do for the transactions inside PLSQL to ensure all 5 pariticpate in the Java transaction? I suppose I just do not specify any COMMIT in the PLSQL, but that probably would not work as PLSQL would COMMIT by default after exiting the proc?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved