Skip to Main Content
  • Questions

  • Stop archieve log generatio for date menupulation inside a transaction.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rohit.

Asked: September 08, 2016 - 8:31 am UTC

Last updated: September 10, 2016 - 5:13 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

we are facing problem with this archieve log generation, there is a SP where a lot of insert and update is performed inside a loop and this causes a high amount of archieve log (>300gb one sp cycle) to be generated. Is there a we can stop/disable archive generation while inside this procedure and later start/enable it again ? We don't need this data as its a part of a calculation and can always be regenerated.

Many thanks.
Rohit

and Connor said...

Not really becauase at any given moment during that procedure's execution, the power could be lost, and you *need* that redo information just to get the database back to consistent state on start up.

However, you could look at changing the logic so that you dont insert/update, you only insert.

For example, if I need to update 100million rows out of (say) 200million, it may be easier to simply copy the entire 200million rows making the appropriate changes as I do the copy, ie

create table NEW_VERSION as
select ...
from OLD_TABLE;

This opens up possibilities of doing nologging operations.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Question

Rohit Gupta, September 09, 2016 - 8:36 am UTC

Hi,

Thanks for the promt reply. I checked with my DBA for this no logging option and got to know that logging is turned on on DB level. I altered the table and indexes to nologging mode and changed the query to use "append" hits but that too is not working.

Also, inside the procedure we are not performing data movement between 2 tables but we are using multiple tables and performing calculation on data with functions then inserting it
to the table. And rewritting the SP will take time and that is the last thing we want to do, please suggest.


Connor McDonald
September 10, 2016 - 5:13 am UTC

If your dba has done "force logging" then *all* operations will be logged.

You can reduce logging *slightly* by changing index statuses before large operations and by using direct mode operations where possible, but these will give small percentage gains not the "order of magnitude" you might be seeking.

Logging is part and parcel of the database.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library