Forcing a specific rollback segment on a transaction does not seem to work

Advertisement
October 11

Hi!
We're using Oracle 9.2.0.5.0 on Sun Solaris and we're still configured to use Rollback Segments.
We have an issue with Snapshot too old due to RBS too small on a long query I attach below for reference:
set heading off
set pagesize 0
set feedback off
set linesize 200
<<<<< SET TRANSACTION USE ROLLBACK SEGMENT UMF_RBS_LARGE_TRAN; <<<<<
SELECT ucms_cards.msisdn
|| ';;' || to_char(to_date(substr(ucms_cards.notes,14+length(ucms_cards.msisdn),19),'MM-DD-YYYY.HH24-MI-SS'),'DD/MM/YYYY HH24:MI:SS')
|| ';;' || to_char(ucms_batches.expiry_date,'dd/mm/yyyy')
|| ';;' || ucms_cards.serial_no
|| ';;' || ucms_cards.serial_no
|| ';;' || ucms_cards.batch_serial_no
|| ';;' || ' '
|| ';;' || CASE ucms_cards.card_status
WHEN 'used' THEN '1'
ELSE '0'
END
|| ';;' || CASE WHEN date_booked_in is null THEN '01/01/1970 00:00:00' ELSE to_char(date_booked_in,'DD/MM/YYYY') || ' 00:00:00' END
|| ';;' || ' '
from ucms_batches,ucms_cards, UCMS_EVENT_LOG
WHERE ucms_cards.batch_serial_no = ucms_batches.serial_no
AND ucms_cards.serial_no = substr(UCMS_EVENT_LOG.ENTITY_ID,11,length(UCMS_EVENT_LOG.ENTITY_ID)-9)
AND UCMS_EVENT_LOG.PARTY_NO in (0)
AND UCMS_EVENT_LOG.TIMESTAMP>=TO_TIMESTAMP(TO_CHAR(SYSDATE-1, 'DD-MM-YYYY') || ' 00:00:01', 'DD-MM-YYYY HH24:MI:SS')
AND UCMS_EVENT_LOG.TIMESTAMP<=TO_TIMESTAMP(TO_CHAR(SYSDATE-1, 'DD-MM-YYYY') || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
AND UCMS_EVENT_LOG.USER_ID LIKE 'SCP-AGENT1%'
AND UCMS_EVENT_LOG.EVENT_TYPE_ID IN (1,2)
AND UCMS_EVENT_LOG.ENTITY_TYPE_ID LIKE 'ucms_cards%'
UNION
SELECT ucms_imported_cards.msisdn
|| ';;' || to_char(to_date(substr(ucms_imported_cards.notes,14+length(ucms_imported_cards.msisdn),19),'MM-DD-YYYY.HH24-MI-SS'),'DD/MM/YYYY H24:MI:SS')
|| ';;' || to_char(ucms_imported_cards.expiry_date,'dd/mm/yyyy')
|| ';;' || ucms_imported_cards.serial_no
|| ';;' || ucms_imported_cards.serial_no
|| ';;' || DBMS_UTILITY.GET_HASH_VALUE(ucms_imported_cards.card_type,1,65536)
|| ';;' || ' '
|| ';;' || CASE ucms_imported_cards.card_status
WHEN 'used' THEN '1'
ELSE '0'
END
|| ';;' || '01/01/1970 00:00:00'
|| ';;' || ' '
from ucms_imported_cards, UCMS_EVENT_LOG
where ucms_imported_cards.serial_no = substr(UCMS_EVENT_LOG.ENTITY_ID,11,length(UCMS_EVENT_LOG.ENTITY_ID)-9)
AND UCMS_EVENT_LOG.PARTY_NO in (0)
AND UCMS_EVENT_LOG.TIMESTAMP>=TO_TIMESTAMP(TO_CHAR(SYSDATE-1, 'DD-MM-YYYY') || ' 00:00:01', 'DD-MM-YYYY HH24:MI:SS')
AND UCMS_EVENT_LOG.TIMESTAMP<=TO_TIMESTAMP(TO_CHAR(SYSDATE-1, 'DD-MM-YYYY') || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
AND UCMS_EVENT_LOG.USER_ID LIKE 'SCP-AGENT1%'
AND UCMS_EVENT_LOG.EVENT_TYPE_ID LIKE '2%'
AND UCMS_EVENT_LOG.ENTITY_TYPE_ID LIKE 'ucms_imported_cards%';
As you see we forced the session to use a huge RBS created for the purpose, but strangely after a long while the query fails with a RBS too small failure due to another RBS, not the one specified.
Is there any chance the UNION or any other component of the query is implicitly opening a new transaction with a different RBS associated?
Any chance to force the same RBS specified explicitly?
Thanks!
Mike

Advertisement

Replay

albertone wrote:
but strangely after a long while the query fails with a RBS too small failure due to another RBS, not the one specified.You misunderstand snapshot too old. It can be caused by other sessions same as by your session. Assume AFTER your session issued select some other session modified one (or more) tables ucms_batches, ucms_cards, UCMS_EVENT_LOG and committed changes. By the time your select reaches to fetch rows modified by that other session rollback extents in rollback segment used by that other session were reused. You will get snapshot too old. Bottom line - all sessions modifying table(s) used by your select must use rollback segments large enough so they are not overwritten before corresponding rows are needed by your select.
SY.

View 4 Replies

The transaction does not die after the kill and I have to restart Oracle

October 11

Hi, I have this very hard issue with my production environment. A transaction started from java hangs after the user has closed the browser before the transaction was completed. The transaction remains in wait for "latch free" in v $ transaction

View All Replies

Transaction does not commit

October 11

I have a JDBC transaction that is not committing to the database, but is also not throwing an error. The scenario is this: JMS message arrives on transactional queue Message processed by MDB MDB calls several Sybase ASE stored procedures JDBC transac

View All Replies

FB05 Batch Input / Call Transaction - Does not select amount for payment

November 30

Hi, When I launch the FBWD transaction (returned bills of exchange), the call transaction for G/L account postings is in error. So the FBWD automatically creates a batch input, but when I tried to launch the batch input, it remains unsuccessful. So I

View All Replies

Why Oracle10g uses System Rollback Segment....????

November 30

Hi , Just a wonder....!!!! Why Oracle10g uses System Rollback Segment....and not System undo tablespace... since rollback segment as a method to rollback transactions has been depreciated.....????? Thanks..... SimSystem Rollback Segment is reserved f

View All Replies

Pb with rollback segment

October 11

Hi guys. Am using oracle 8i and am having problems with rollbackup segment. My rbs tablespace is 35GB and will like to shrink it! Its a production db, so my question is shoudl I fire the below query? alter rollback segment SYSTEM shrink; alter rollba

View All Replies

Record does not get locked on update in a transaction

November 30

Hi ... Lets say I have a session bean method with "Required" Transaction as follows: public void method1() entityBean1.setFieldA("something"); //record1 entityBean1.setFieldB("something"); //record1 entityBean2.setFieldZ(&quo

View All Replies

Apply does not apply transactions but LCR goes through

October 11

Hi all, I have seen a strange problem happening in various test environments lately. I have no idea how to troubleshoot the issue, so new ideas are taken here. Oracle 10.2.0.3 for both source and destination databases on Linux RHEL 4.0 and AIX 5. I o

View All Replies

Is "SET TRANSACTION USE ROLLBACK SEGMENT" only a hint

October 11

I have two users, one makes some inserts in a table. The other makes a select which visits many rows. I have a big rollback-segment. To ensure, both users use this rollback-segment I created a logon-trigger: CREATE OR REPLACE TRIGGER a_logon AFTER LO

View All Replies

Transaction table in rollback segments

November 30

hi guys, I have read the following, but am still having a little difficulty conceptualising. Would someone be kind enough to give me a very brief example? thanks For each rollback segment, Oracle maintains a transaction table--a list of all transacti

View All Replies

Disable Rollback Segments

November 30

Hello friends, 1) can I say to Oracle not to use the Rollback segments since I am updating huge Number of records. 2) Is it possible to cache a huge table (say with 10 million records) using dbms_...keep. Please let me know your response as soon as p

View All Replies

Convert from Rollback segment(RBS)  to undotablespace

November 30

i have a database 9i and it using rollback segment and i would like to convert to undotablespace. question is there is anyway i can find it out what size undo i need to created base on the database right now i have it. the database is very critical i

View All Replies

Can rollback segments span one datafile like data segments

November 30

1.Can rollback segments span one datafile like data segments? For e.g we have undo tablespace with 2 data files We have one active transaction in rbs01,now when rollback segment grows and extents are allocated to it ,if all the extents will be alloca

View All Replies

Rollback segments - database logging

November 30

Got a problem... We are trying to move data from one database to another using: INSERT INTO table1 (SELECT a,b,c FROM [email protected]). The problem we are having is that the amount of data being moved with this in sert command is too great - we are gettin

View All Replies

Oracle error: Ora-01555 : snapshot too old: rollback segment number 1......

November 30

System Error: Unknown Database error (type qqdb_ResourceException) on WMS_WH1: Execute failed for SQL statement ............... error from database is: ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small... Any

View All Replies

Different rollback segment used. why?

November 30

Hi. I am trying to use rollback segment RB6 in a delete process in a stored procedure using SET TRANSACTION USE ROLLBACK SEGMENT RB6; However, when the procedure is called and performed, an error is thrown: ORA-01562: failed to extend rollback segmen

View All Replies

ORA01555"snapshot too old:rollback segment num %s with name "%s" to small

November 30

Hi All i have and 11gR1(11.1.0.6) DB source instance in wich i have i a partitioned table of 1TB size. I want to IMPDP this table using NETWORK_LINK in a 11gR2(11.2.0.3) destination RAC instance. when a try to import a get the following error message

View All Replies

Sql to find check Percent of Rollback Segment Used?

November 30

I have the following queries to find information about rollback segments. I want the sql to find % of rollback segment used. --rollsegs.sql select SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, status from dba_rollback_segs order by segment_name;

View All Replies

Rollback segment corruption on oracle 7.3

November 30

We have an oracle 7.3 database on solaris(sparc) 2.6. In the midddle of the transactions, the following error message occured: ORA 1578 Data block corrupted(file #2, block#8274) ORA-1110 Datafile 2:'/user1/oradb/rbstbl.dbf' (this datafile corresponds

View All Replies

ROLLBACK SEGMENT VERSION 7.3

November 30

We have an oracle 7.3 database on solaris(sparc) 2.6. In the midddle of the transactions, the following error message occured: ORA 1578 Data block corrupted(file #2, block#8274) ORA-1110 Datafile 2:'/user1/oradb/rbstbl.dbf' (this datafile corresponds

View All Replies

Tags:

  1. LinkedIn messages who dents them
  2. msi neo4 platinum probleme demarrage
  3. apple tv hulu no signal
  4. v7 things that we dont know about coc
  5. lenovo a6000 upgrade ing problem
  6. mobilink ssl port chacking apps
  7. oracle r12 payable aging report gl date
  8. claiming primary bt id status
  9. PWKI
  10. swimmingxgn
Copyrights 2019 Fcffair BigData Resource, All rights reserved