whenever you run this procedure to upload data in staging area tables you put the tables in nologing mode inside procedure using h execute immediate. When loading is completed then put the tables in loging mode using same way as before.
I think this is the most hassle free way not to generate redo log for a prticular table. In that way neither you have to put the database in noarchivelog mode nor you have to use sql loader.
Another issue in this regard, user who executes the procedure must have alter table privilege.
good luck.
Main Topics
Browse All Topics
by: mudumbePosted on 2004-02-15 at 14:31:59ID: 10367588
First of all the tables must be created with 'NOLOGGING' option. You can use this option for INDEX as well.
When you use SQL*Loader, you must use "Direct-Path" loading by specifying the 'DIRECT' parameter.
Inserts that move data to live tables can also be done via "Direct-Path" loading using the APPEND hint.
For eg. insert /*+append*/ into live_table as select * from staging_table.
Important: It IS the Direct-Path loading combined with NOLOGGING option that generate very very small REDO. Bascially, the redo log will contain "SQL" that is needed to reconstruct the table rather than table data.
If I remember it right, the insert must be of the form as stated in my example.