Hi Ari,

    First of all, Thank you very much for your help on Oracle database
    related questions.  Your suggestions are really excellent and
    informative.

Thanks!

    Since I'm working in Data warehouse environment, I have to deal with
    large data.  I have couple of problems and hope I could get clarify
    from you.

    1.  Buffer overflow problem in PL/SQL.
         When I use dbms_output.put_line in PL/SQL code, I get this
    `Buffer overflow ` problem when it is outputting more .  To avoid this
    problem. I used dbms_ouput.disable and dbms_output.enable(1000000)  at
    the starting of this code.  Even then, if output data is more (may be
    more than 1000000, obviously), I get the same error.  To avoid this
    problem,  I used dbms_ouput.disable and dbms_output.enable(1000000)
    inside the loop to flush out .  This doesn't seem to help and  I see
    the output at the end of the execution , but not after flushing it
    out.

Yes, there are problems with the buffer overflowing. What I personally do is use
the UTL_FILE package, which writes to an operating system file. You can view the
results AS it is executing, and you can have gigabytes of data.


    2. How to avoid writing into rollback segments.
         Is there any way in Oracle that we can bypass writing into
    Rollback segments ?
    Here, I'm not worrying about data loss. I can re-run if there is any
    problem.

In Oracle8 there are NOLOGGING attributes to objects where the INSERTs, UPDATEs,
and DELETEs, do not use rollback segments / redo logs.


    3.  In  PL/SQL,  when I have a join on big tables , it gives `Snapshot
    too old (rollback segment too small)' . To avoid this problem, I used
    different cursor on each table and pass first cursor information to
    other cursor.

    My question is , Why it uses rollback segment for select statement.
    I'm not doing any update, delete or insert in my statement.
    As per my knowledge, It uses temp segments for sorting and joining
    etc.  Please correct if I'm wrong.


The "snapshot too old" is misleading. Your SELECT statement does not use
rollback segments (you are correct). But what is happening is that other
processes are modifying data while your SELECT statement is executing. If there
are too many changes that occur during the SELECT statement, then Oracle cannot
keep a consistent view of the database and gives the "rollback too small". What
is happening is your SELECT relies on the rollback segment to keep its view of
the data consistent to the point that the SELECT statement started.

    This is my outstanding doubts since long time. Once again thanks a lot
    for your suggestions .

    Prasad
    email : p_gunda@hotmail.com (OR)
            prasada.gunda@hartfordlife.com



Hope this helps, and best of luck to you.

-Ari Kaplan
www.arikaplan.com

Back to Ari Kaplan's Home Page ight="18"> Back to Ari Kaplan's Home Page