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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jinesh KamdarConnect With a Mentor Commented:
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
SmilingPixieConnect With a Mentor Commented:
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
 
jwittenmConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.