ORA-01555 & ORA-02063 error

ERROR: Error fetching from cursor. ORACLE error is ORA-01555: snapshot too old: rollback segment

       number  with name "" too smallORA-02063: preceding line from "DataBase Path"

Link ORA-01555

Proc sql ;
  connect to oracle as ora (blah blah blah) ;
  create table gnu as
  select * from connection to ora
  (select day accnt_id record_type record_subtype
                    paid_mou bonus_mou cust_type COS
   from DW_CDR_ACCOUNT_DAY
   where day <= to_date(&orastartdate) and cust_type='Consumer' and record_type in ('VOICE','SMS','XTRAS') and COS in (10,20,30,40,60)
   ) ;
   disconnect from ora ;
Quit ;



Data usage2 ;
   set gnu ;
Yadda yadda yadda. ;





-Roy


********************************************************************************


Thanks for all, for your valuable suggestions, I have sent my code and the error to my DBA.



  I am sending the code to all of you, so that all who have spend time on this error can have a look at the code and let me know your comments in tuning the code.



  options obs=max errorabend compress=yes BUFNO=256  BUFSIZE=64;



  data _null_;
enddate=today();
startdate=enddate-90;
*startdate=intnx('month',date(),-4,'b');
*enddate=intnx('month',date(),-2,'e');
call symput ('startdate',startdate);
call symput ('enddate',enddate);
run;



  %let oraStartDate=%eval(&startdate*60*60*24);
%let oraEndDate=%eval((&enddate*60*60*24)+(86399));





  data usage2 (keep=accnt_id monthof duration total_mou);
set ora_dev.DW_CDR_ACCOUNT_DAY (keep=day accnt_id record_type record_subtype
                    paid_mou bonus_mou cust_type COS);
  where day <= &orastartdate. and cust_type='Consumer' and record_type in ('VOICE','SMS','XTRAS') and COS in (10,20,30,40,60);
  day_date=datepart(day);
  monthof = intnx('month',day_date,0);
  array fixnulls(3) $ accnt_id record_type record_subtype;
  do i=1 to 3;
       if fixnulls(i)='NULL' then fixnulls(i)=' ';
    end;
  format day_date date7.;
  duration=sum(paid_mou,bonus_mou);
if duration=. then duration=0;
  total_mou=duration;
  format monthof mmddyy8.;
  run;



  I am facing error due to this ora_dev.DW_CDR_ACCOUNT_DAY data set.



  ERROR: Error fetching from cursor. ORACLE error is ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small.





Link ORA-02063


The Oracle docs note this on the ora-02063 error:








ORA-02063: preceding stringstring from stringstring
 

Cause: an Oracle error was received from a remote database link.

 Action: refer to the preceding error message(s)






Oracle MOSC documents give an account of ORA-02063 as occurring when "You want to query or update a remote table and receive the following error":


ORA-02063: preceding line from <name of a database link>

This also happens when a select is issued over a database link, and not a 
distributed transaction


Solution Description
--------------------

The ORA-2063 indicates that this must be done at the remote database.


Explanation
-----------

When the error is generated at the remote database it is accompanied with an ORA-2063. In this case the parameter distributed_transactionsmust be increased at the remote database.

If there is no ORA-2063 the parameter distributed_transactions must be increased at the local database.

In a distributed environment, if a select is issued in a session without the 
SET TRANSACTION READ ONLY command, the select query is considered a distributed transaction.

If the parameter distributed_transactions is set too low, this too will result in an ORA-2042 and ORA-2063
 

It is important to note that ORA-02063 typically appears with ORA-02042