Prevent Rollback on Certain Tables

Hi,

At our office we have an application that runs many aspects of the business.  This software runs on Oracle 9i - to report on this data we use Crystal Reports to execute a Stored Procedure that returns data for display.

The stored procedure works but delteing data from a table and then populating the table with data.  Manipulation is done as data is added to the table etc..  My problem is that this casues a lot of disk thrashing on the server.   This morning we had an error that we had filled our ROLLBACK space on the server.  Clearly what Oracle is doing is providing/storing rollback information for all the tables in the databse including the temporary ones that are used for the reports.

Therefore I want to disable Oracle from storing rollback either on that table or a way in which I can disable it on the following SQL statements.  We just don't need the rollback on these tables.

Any help on this greatly appreciated.

mike
hydevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
If you don't care whether the DELETE-INSERT goes thru' as a single transaction, then u can use TRUNCATE TABLE instead of DELETE FROM table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crxiCommented:
I believe you can drop your table and re-create it with no-logging.  (I haven't tested this)
0
schwertnerCommented:
NOLOGGING will only prevent logging of DDL. The other DMLs will be recorded in the online (and archved) redo logs.

Also you can not avoid usage of UNDO (Rollback) segments. It is internal principle of Oracle that can not be
avoided.
You can decrease the usage of UNDO (former Rollback) segments performing delete operations using PL/SQL procedures that will issue COMMIT statement in small periods of time or fixed small amount of deleted rows.
After 'commit' the UNDO segments data will be cleared automatically.

After Oracle 9i you can also ask Oracle not to kep Rollback data using UNDO_RETENTION SPFILE parameter.
Set it to very low value, e.g. 10.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sventhanCommented:
However, here's how you can do large updates without running out of rollback space, by committing every 1000 records:

declare
 commit_counter number := 0;
 cursor c is
   select col1,col2,col3,rowid from mytable;
begin
 for rec in c loop
   update mytable
   set col3 = col3 + 1
   where rowid = rec.rowid;
 
   commit_counter := commit_counter + 1;
   if (commit_counter >= 1000) then
     commit;
     commit_counter := 0;
   end if;
 end loop;
end;
/
0
schwertnerCommented:
Yes, sventhan is right.
Same method can be used for DELETE and INSERT operations.

But keep in mind that UNDO_RETENTION can influence the purge of the undo:

Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries.

This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time
0
SmilingPixieCommented:
If I'm reading your email correctly, it appears that you delete all the rows from a table only to populate it with new data just for a report.  I'm assuming that the once the report is complete, you would no longer require the data in the table.

You may be able to use temporary tables (CREATE GLOBAL TEMPORARY TABLE) for these reports rather than permanent tables.  Temporary tables generally do not generate the same amount of redo and undo logging.
0
hydevAuthor Commented:
SmilingPixie - you are correct thats exactly what I'm doing.  So do temporary tables not undergo the same logging? If so then there would be less disk thrashing..
0
SmilingPixieCommented:
Temporary tables do hit the TEMP tablespace (which is where the data gets stored) and that may be causing some of the thrashing, they shouldn't have the same impact on the rollback/undo......but it still seems that there are some permament tables (or indexes maybe) that are being pretty badly affected by your changes.

When you defined the temporary tables - do you do an on commit preserve rows or on commit delete rows?  And, is your temporary tablespace a true temporary tablespace?
0
jwittenmCommented:
Global temporary tables generate undo.  The only redo generated is for the undo.  In your case, use 'on commit delete'.  You can truncate them, index them, etc.  Check the documentation at technet.oracle.com for your version.  Also, to expand on SmilingPixie's question about your temporary tablespace, check that it is type 'temporary'.
0
hydevAuthor Commented:
I think you guys are right I need to check some things.  From memory I think the temp tables are set up with "on commit preserve rows" and I need to check they are true temporary.  Though not sure what you mean, they are declared as CREATE GLOBAL TEMPORARY TABLE's.  I think you are right about the permament tables being pretty badly affected.

mike
0
jwittenmCommented:
It's the TEMP tablespace that we are referencing when we ask 'is it truly temporary'.  Look at 'content' in dba_tablespaces.  Be sure that it says 'TEMPORARY'  for the TEMP(?) tablespace.
0
schwertnerCommented:
If the rollback(undo) and archve logs are really problems for you you can try a radically step to avoid the logs.
Use UTIL_FILE package and create the temporary table you need as file. After that use External File object (you can select only from external tables) to do your Report.
The speed will be the same or even faster because of lack of logs.

Anothor advice. Check if the Undo (rollback) logs and Online Archive Logs are on RAID 5 device. They are used sequentially and have to be on independent (not RAID) device.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.