Hi.. There is only 1 transaction running in the entire database (A brand new dev db) and have 10 RBS, with 1 RBS a big one designed for batches.. We set the transaction for this RBS, and it accepted, but I cannot find the RBS being used.
I checked the v$rollstat for xacts > 0, but everytine it shows different USN (RBS id).. My questions :
How can I check to see which RBS my transaction is using
and secondly, when i query everytime there is a diff USN..
I was hoping that a RBS can span multiple transactions and 1 transaction cannot span multiple RBS..
Yes.. and my optimal is set at 10 extents approximately and everytinme the transaction is having a diff RBS (which in theory is not possible), the HWM on the prior RBS is having the size of the optimal..
What pando is trying to point out to you is that when you perform a COMMIT, you are starting a new transaction. You don't have one transaction for your batch job, you have *lot* of them, one after another after each COMMIT or ROLLBACK.
SET TRANSACTION USE ROLLBACK SEGMENT... is valid only for the duration of transaction. So in your case, only the first transaction (up to the first commit) is forced to use the RB segment of your choice, after that it is entirely up to Oracle which RB segment to use for each new transaction.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
create a test table and insert a row and do not commit the transaction.. go to v$transaction table and select data from that you can see that transaction there. but, the moment transaction is committed the related data vanishes from v$transaction..
Thanks for the responses.. It makes sense since I am starting a transaction and when commited, it is not using the RBS anymore. So, If I want to use the same RBS for my entire processing which includes close to 15 programs/scripts, then I should literally edit all the scripts to include this RBS at the beginning and after every commit..
Secondly, My largest RBS is 2GB and is optimal is 10 extents or 500MB. What is the use of this optimal and how is my optimal impacted, if my transaction does not cross my optimal and does cross my optimal..
Thirdly, If I have frequent commits, what happens to optimal if I continue with the same RBS and if I dont continue with the RBS??
Fourth, How should the 2GB RBS be configured if the table would get more than 2GB data. I mean how should optimal be configured and what should be the frequency of commits for the success of the transaction??
well it seems that you dont understand very well the concept of RBS so the only thing I can suggest you is read the Concepts documentations in Oracle documentation, all the question you are asking are detailed there