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:
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