redo logs

[Petr Simik_1]

I perform select from linked database

create temp_table as
(
select xxx
from tab1@dblinkA, tab3linkA
union all
select yyyy
from tab2@dblinkA, tab3dblinkA)

And I got error message ora 01555
snapshot too old: rollback segment number 7 with name rbs2 too small

Rollback segment on remore server is overloaded. Is there any possibility to specify which redo log is going to be used. Or different sql query with same result without impact to redo logs?

[Fred Ruffet · Accepted solution]

Have you got a message specifying that this error comes from distant DB ?

You can not specify redo log, but you can first copy the two distant tables then do your create table localy :

create table local_tab1 as (
select * from tab1@dblinkA);
create table local_tab2 as (
select * from tab2@dblinkA);
create table temp_table as (
select xxx
from local_tab1, tab3linkA
union all
select yyyy
from local_tab2, tab3dblinkA);
drop table local_tab1;
drop table local_tab2;

It can do much traffic, but it will not generate distant log.

Regards,

Fred

[Nicolas Dumeige]

Hello,

As for the subject of you thread, redo log & rollback segments are differents. The err msg you receive is about rollback segments.

You can specify a rollback using this syntax :
set transaction use rollback segment RBS1 ;

The problem you face is likely to be that the table you mention in you create as select statement are utilised while you try to copy them. Oracle is trying to maintain the consistency using rollback segement to keep a copy of all lines modified.

=> solutions :
If this a one shot, export both remote table and import them locally, then issue your create table.
If you have to do it frequently, do this step by step, one table after on other, as you specified UNION ALL, you won't even need to clean up the result table.

Cheers

Nicolas

[Fred Ruffet]

Nicolas is right (I should not answer before 11AM :) It's about Rollback, not Redo.

anyway, it should be important to know wether the message comes from local DB or distant DB. If it comes from distant one, it is specified in a second message (something like "previous message from XXX").

my first solution should help, but first see on wich DB is the problem. If problem is on distant DB, you won't be able to specify your rollback.

Regards,

Fred

[Petr Simik_1]

Thank you .
I am going to try your advices.
Err message is coming from distant DB.
Your solutions sounds very helpfull.
I'll copy tables to local DB and perform query on that.

Thanks

[Yogeeraj_1]

hi,

please allow me to also add the following:

anything that READS is subject to read consistency.

just as select * from t can ora-1555, create table mytable as SELECT * FROM T@dblink can
ora-1555 for the same reason.

avoiding the ora-1555 is as simple as sizing rbs properly. use undo-retention and AUM in 9i, size right in 8i and before.

A query may run for hours. Oracle needs to have a consistent read on that query. Oracle needs rollback to do that consistent read. You may be reuseing the rollback at some point.

To solve this problem:
Increasing undo retention is one method.

Array fetching (making the query run for a short time) is another. (not applicable in your case)

regards
Yogeeraj

[R. Allan Hicks]

From Thomas Kyte's book Expert One-on-One Oracle Published by wrox ISBN 1-861004-82-6
Page 185

"The ORA-015555 is on of those errors that confound poeple. It is the foundation for many myths, inaccuraces, and suppositions. The error is actually straightforward and has only two real caouses, but since there is a special case of one of them that happens so frequently, I'll say taht there area three They are:

* The rollback segmnents are too small for the work you perform on your system.
* Your programs fetch across commits (actually a variation on the above).
*Block cleanout"

Since your query appears that it could be a long running query, I'll guess that you really do need a larger rollback segment. Tom Kyte who knows more about Oracle than I ever will, is a fan of making all of the rollback segments the same size. This makes sense because you can select the rollback segment to use, but you cannot (at least as far as I can tell) make it exclusive.

Quick review from the DBA course.....

When an extent in a rollback segment is fulled up, the Oracle server attepmts to reuse the next extent in the ring. Even if this new extent contains one active entry - that is, and entry that was written by a transaction that is still active-it cannot be used. In these cases, a rollback segment allocates and additional extent. The transaction cannot skip an extent in the ring and continue to write to a subsequent extent. A transaction that has made only a few changes, but has been idle for a long time could cause rollback segments to grow even though there are many free extents. In such situations there is a lot of space wasted and a database administrator may need to intervene to avoid excessive rollback segment growth.

So, if you have a long running transaction, or a transaction that blocks the lower number extent. You can try creating a huge rollback segment so you have more extents before you have to reuse a lower numbered extent and use the following statement in your PL/SQL

set transaction use rollback segment huge;

(provided you created a rollback segment called huge that is indeed huge)

Just remember once the rollback segment is online anyone can use it so it may not solve your problems if another transaction jumps in and grabs the segment and blocks. So, larger rollbacks do not guarantee that the problem will be solved.

Another consideration......

Can you reduce your query time? You appear to be doing a table scan on the remote tables. Rollback is used to maintain read consistancy among other things. What if you modified you select statements to pull "partitions" of data. That is select xxx from table@remote where key=key1. You get a portion of the table which would cause a shorter running query and therefore use less rollback at a time.

Technically and ideally, a transaction should be a complete unit of work. Just as shown in the account transfer problem so often used to illustrate the benefits of transactions ($100 is transfered from savings to checking and the computer goes down etc.....) However, sometimes, you are forced to make hardware and architectual concessions. If it makes sense, you might re-think the transction into smaller units of work.

-Good Luck

[Fred Ruffet]

Petr doesn't seem to be an Oracle DBA so creating rollbacks or searching better RBS sizing may not be his job.

Joining two tables through dblink may drive him to a longer job than selecting the whole distant table. So copying through DBlink the whole table to local DB may expose him less to ora-01555 than keeping his actual query.

If this doesn't work there may be query tuning to do before DB tuning : from his query, I see "full joins" and "union all".

If this is not sufficent, he will effectively have to resize RBS and tablespace. Solution may be a little bit more complex in this case.

Regards,

Fred

[Petr Simik_1]

than you guys. I finished my task by decreasing number of information getting from remore server and than it worked.
All information you gave me are right and now this matter is much more clear to me.

For ilustrating I am attaching real SQL than only the logical scheme which I put in my 1st message.
Resolution was that I removed service_name function from select. Error came because this is my slowest DB cluster.

Thank you all guys for your help.


thats it.
create table mhunting_service_in2 as(
SELECT
'SCP3' as SCP,
Obj.ObjectName as MSISDN,
Sos.ServiceId,
SUBSTR(sms_sh.Get_Servname@smp2(Sos.ServiceId), 1, 50) as service_name,
provisionid,
Sos.ProvisionDate,
DECODE(Sos.ActivationStatus, CHR(0), 'INACTIVE', CHR(1), 'ACTIVE', NULL, 'NO SERVICES', 'ERROR') as status,
decode(Sos.BasicServiceCode,1,'speech',2,'fax',3,'data',4,'all','???') BSERV,
Sos.ProviderId
FROM
sms_sh.sh_s_Object@smp2 Obj,
sms_sh.sh_1_ServicesOfSubscriber@smp2 Sos
WHERE (serviceid=1066317) and Sos.ProvisionId = Obj.ObjectId
UNION ALL
SELECT
'SCP4' as SCP,
Obj.ObjectName as MSISDN,
Sos.ServiceId,
SUBSTR(sms_sh.Get_Servname@smp2(Sos.ServiceId), 1, 50) as service_name,
provisionid,
Sos.ProvisionDate,
DECODE(Sos.ActivationStatus, CHR(0), 'INACTIVE', CHR(1), 'ACTIVE', NULL, 'NO SERVICES', 'ERROR') as status,
decode(Sos.BasicServiceCode,1,'speech',2,'fax',3,'data',4,'all','???') BSERV,
ProviderId
FROM
sms_sh.sh_s_Object@smp2 Obj,
sms_sh.sh_2_ServicesOfSubscriber@smp2 Sos
WHERE (serviceid=1066317) and Sos.ProvisionId = Obj.ObjectId
)