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)?
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 ?
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 .
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.
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]...
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.
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?
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?
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]....
I am getting ORA-02049: timeout: distributed transaction waiting for lock , how to resolve this type error
View 3 Replies View RelatedI 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).
SELECT A.POLICY ,
F.MIN_MEMBER_ID,
MIN(A.EFF_DATE) EFF_DATE,
A.EXP_DATE ,
G.DESCRIPTION PROGRAM_NAME,
[code]...
how to see for todays transactions in a database's Schema for example, in scott/tiger user.
To See All DML Operations carried out for current day with a single query, Insert,Update, Delete for the tables affected today.
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?
I have the following PL/SQL program.
declare
v_amount NUMBER;
v_paymentno INTEGER := &sv_paymentno;
v_playerno INTEGER;
v_payment_date DATE:= SYSDATE;
begin
select 500 into v_amount from dual;
select 44 into v_playerno from dual;
insert into penalties values (v_paymentno, v_playerno, v_payment_date, v_amount);
end;
I forgot to add the commit statement and now I have hung transaction with dirty data with v_paymentno 27. Is there a way to commit or rollback that transaction?
I have a problem, we have some datas in a table for example 7500 rows in a table name called table1 upto 11:am today. but after 11:25 am i have only 5500 rows. in that table.
the table can be accessed by many users here. we dont know when the delete happended in that table. is there any query to find the transaction log of this particular table.
the deletion should be happended between 11:00 am to 11:30 am. but we have retrieved the data using timestamp query. but we need to know when the query issued and by which user the query has been issued.
Oracle database 11g Expression Edition 11.2.0.2
I want to change the the number of processes to 150 (default is 100) I did this by issuing the below command.
alter system set processes=150 scope=spfile;But why that modified value is not being reflected?
select name, value from v$parameter where name in ('sessions','processes','transactions');
NAME VALUE
-------------------- -----
processes 100
sessions 172
transactions 189
How a DB client session establishes a connection and how it performs any transaction.
View 1 Replies View RelatedWhen a user clicks on Save Button on a form, can we know from sql plus (Oracle) all the related queries (insert/Update etc) for that transaction ??
View 1 Replies View RelatedI need to create a report that will show all activity for the day that the report is run. The report will be run or auto-refreshed throughout the day. So, at 10am, 11am, 2pm, 4pm etc. I just need to display quantities received, shipped etc. for that same day. Since the report will not be run at a fixed time, I can't use sysdate - .5 for example.
View 6 Replies View RelatedIm trying to create a trigger to prevent transactions like insert, update and delete based on two conditions:
1) Friday
2) Between 5pm to 8am
I need to generate a report by showing the select, insert, update transactions count per day.
I have been use V$SYSSTAT veiw but there is not include my requested data exactly!
[URL]
I have problem with my Replication using oracle 9i.It does not push transactions automatically when refresh time comes but it works fine when pushed manually.I have two sites:
1- Master
a-Master group
2- Materialized view site
a- Materialized view group (Asynchronous)
b- Materialized view with refresh occur automatically on future date every 5 minutes
c- refresh type FORCE.
I'm able to use Oracle 11g Standard/Enterprise Edition with Atomikos for XA transactions.(XADMS).
My question is Can I Use Oracle DB 11g Express Edition with Atomikos for XA Transactions.
I'm running 11.2.0I am looking at tuning a sql statement, and the question was brought up as to the max inserts per transactions in 11g, and if it exceeds 1000.I haven't found a solid answer yet, but I thought that 10g was higher than 1000.
My first thought was to implement a commit loop on every 1000 rows, as that is how things were handled in the past.But I found an article that talks about redo logs and performance and how it's a horrible practice to do the commit loop.
What I haven't found is what is the better methodology in doing this?My scenario could encounter inserts as much as 20,000 at a time.
I have some transactions in my table with date and time.
i want to pass from date, to date and from time , to time as parameter.
when i pass one date and two time parameters, it works fine. but when i try to pass from date and to date (two date parameters) and two time parameters then it does not work accurately.
e.g. i want to pass 05-Aug-2010 and 06-Aug-2010 and time from 08:00:00 and 14:00:00 then it only retrieves data of both dates having only this time range. however i need to get transaction of 05-aug-2010 from 08:00:00 to 06-aug-2010 14:00:00.
There are 2 people on separate machines each executing a transaction through the same form, processed through a when-button-pressed trigger.The first session processed correctly. For the second user, the session seems to have picked up the non-PACKAGE variables of the first session in what was passed through to the data base. Values associated with the 2nd session's PACKAGE-based types appear to have passed through correctly.
Hence, the second user's transaction processed with a combination of values from the two sessions, with the second user's PACKAGE-based variables merged with the first user's non-PACKAGE variables. There is no use of context variables. There are some global values, but none of them are used in this trigger.The values in question, that appear to have passed from the first session to the second, are based on contextual LOV selection: after selecting a transaction type, users are prompted to select from a LoV specific to that type. Value property set "Validate from List=>Yes".
The 2nd session's PACKAGE-based values do not correlate to the non-PACKAGE values, leading us to conclude that the latter values somehow came through from the first session. We are running IAS 10g R2 on Oracle 10gR2 (10.2.0.3).Each user session is created as user logs into the application and hence logically there should not be any overlap between sessions of different users concurrently.
I did have a look at the code and predict nothin wrong with the source code since the system has been in use for a few years now and only occurred a couple of times in last few months. Also one more noticeable thing is that the issue is not reproducible. I would believe somethin goin wrong in the middle tear or with the session management. Are there any known issues in session management in the Forms server Or something?
I have a table that cannot be changed with a field called transaction_reference in the transactions table. This field contains any number of some values in a look-up table called codes.
The table codes contains 'AA', 'BB', 'CC'.
A typical transaction_reference field may look like 'CC BB' or 'AA' or 'AA CC' or 'AA CC BB' - any number, any order.My goal is to get a count of records grouped by another field from the transactions table.
Transactions table example:
transaction_id | transaction_reference | family
---------------------------------------------
1 | AA BB | foo
2 | BB CC | bar
3 | BB | hello
4 | AA CC BB | foo
5 | BB AA | bar
So the results should look like:
family | code | count
foo | AA | 2
foo | BB | 2
foo | CC | 1
bar | AA | 1
bar | BB | 2
bar | CC | 1
hello | AA | 0
hello | BB | 1
hello | CC | 0
If the counts of 0 (like the third to last and last line above) don't show up I'm ok with that.I put together an explode function like this one here but I'm really not sure where to go from here. I can split the transaction_reference, but I'm not sure what to compare it to or how.
I realize that a field in the transactions table for AA, BB, and CC would be ideal, but I can't do that... the powers that be won't let me change the table.
for each exploded segment from transaction_reference
look for it in the codes table
if it exists, add 1 to the count
I have one question on MVIEWS.....
Where the logs will be logged for all the materialized views transactions (I mean a particular table)?
Is there any Data Dictionary Table were i can find this .
I am trying to find some way how to tune and optimize the server performance in following situation. There are 100s of sessions inserting records to one table. Sessions are communication threads in java application, each thread is receiving messages that are to be stored in the table. Each message must be commited and then is ACK sent to remove client. Two problems are raising of course - much of ITLs on the table and lots of very small transactions. I can adjust the java application, but cant do much about the design.
I was thinking about some "caching" - if the messages are stored in memory and bulk-inserted to database by single thread the performance would be much higher. However, there would be possible loss of data - the message could be lost from memory cache and client already received ACK.