Q

Any way to turn off journaling during program run?

This Content Component encountered an error

Is there a way I can turn off journaling for a program's run? I have a table of 10 million rows where every row must be updated at year-end. There isn't enough rollback space available to handle it, so instead I "create table (clone) as select...", and then have the DBA delete the real one, and rename the clone.

I need to do the same updating on this table throughout the year, but only updating a fraction of the rows. I can simplify it into one update statement, if I can get past the rollback issue.

Years ago on a different database platform, a program could place an exclusive lock on a table, and then designate 'no journaling' when working with large volumes of updates in a batch mode (a technique we carried over from our single-thread days when we would back up the database before running updates). Is such functionality still available? If so, please tell me where I can read up on it. THANKS!!!!


Oracle's journaling mechanism is called "redo generation". You cannot turn off redo generation. If you are inserting data, you can perform a direct path insert above the table's high water mark with your table set to NOLOGGING to generate minimal redo. But you cannot turn off redo logging. It is not possible in an Oracle database.

Personally, I would solve your problem by creating a large enough rollback segment. Many DBAs have a rollback segment called RBSBIG (or something like that). It is typically offline. They bring this rollback segment online before starting their batch process. The start of the batch process includes a line similar to the following:

EXEC DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('RBSBIG');

Your transaction will now use this bigger rollback segment.

If you are using Oracle 9i or 10g, then consider using automated UNDO and as long as your UNDO tablespace is large enough, Oracle will automatically give you a big enough undo (rollback) segment.


This was first published in October 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close