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.
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.
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 ?
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.
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?
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?
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).
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?
We are having a production database configured in oracle 10.2.0.4 ( standard edition) and its contains near to 50000 tables . The database is accumulated by more than 100 tables everyday, and my question is is there is any table limits in oracle database ? especially in standard edition ??
IS Oracle Forms/Reports 4.5 supported on database 9i?
My application is running on Form/Reprots 4.5 and 7.3.4 database in WIN2003 server. we have upgraded the database to 9i Release 2. After the upgrade terminate, i become unable to connect to my database from my application forms 4.5.
TNSNAME is OK. Listener is OK.
Can i connect from Forms/Reports 4.5 to 9i Release 2 Oracle database?
Currently i am using IE 6 for running my forms 10g, I like to know upto which IE Versions i can update so it must not affect my Oracle forms 10g running.
i like to update to IE 8, but it must not affect my forms runtime functionality!
I can see that some failover events etc aren't supported (Differences between the ODP.NET Managed Driver and Unmanaged Driver), but is failover supported at all? I don't need to get notified, just to get the failover (clustered node switching) working. Is it supported in the managed driver?
I was trying to generate AWR report, but the report which got generated consist most of the sections without data. Later i came to know that AWR report is not fully supported in 11g? Is that true?
There are two application servers we have, one is windows based with 10g and linux based 11gr2. Our main login form throwing error message in linux server 'ora-12703 this character set conversion is not supported'. The same coding form in 10g running without any problem. Both application servers accessing the same oracle db server 11gr2.
what version of java is supported by Oracle Database 10g and 11g. Actually i am writing a User Defined Function in Java and was asking this question to know which version of java i need to compile the source file before uploading the jar.
SQL Error: ORA-30353: expression not supported for query rewrite 30353. 00000 - "expression not supported for query rewrite" *Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE, CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable, correlation variable, a set result,a trigger return variable, a parallel table queue column, collection iterator, etc.
I am trying to use reference partitioning the parent table records moved using the row movement . but the child table records are not moving below is my sample .
CREATE TABLE ref_parent1 ( table_name VARCHAR2(30), order_date DATE, num_rows NUMBER); INSERT INTO ref_parent1 VALUES ('TEST',SYSDATE,100); INSERT INTO ref_parent1 VALUES ('TEST1',SYSDATE,1000); INSERT INTO ref_parent1 VALUES ('TEST2',SYSDATE,1000); COMMIT;
[code]....
ORA-14652: reference partitioning FOREIGN KEY IS NOT supported
