Oracle : Redo / Undo for nologging table + Append + archivelog I'm trying to replicate a table from an external Oracle DB to my system using a DB Link.

First i tried doing it using an mview but it caused alot of redo/undo(I did the same steps as below but it didn't work for some reason).

Next, I went on a different strategy and created a self implementation of an mview with a table.


Table was set as nologging when created. (verified it in dba_tables)
Index on table is set as nologging when created. (verified it in
dba_indexes).
Database log_mode is set to ARCHIVELOG. (verified in v$database).


process of replication is being done like:


Set index as unusable.
Truncate table.
Insert with append hint.(INSERT /*+append*/ into tbl_name SELECT *
FROM v_tbl)
Rebuild index nologging.


The code is written in a package which is being run with a scheduler_job on an interval.

v_tbl - a view that encapsulate the original external table structure(view is being built dynamically when compiled due to different sites external DB table names and columns) and give constant names for the columns(by the view columns aliases).

v_Tbl :

 SELECT column1 col1, 
        column1 col2 
 FROM ext_tbl@external_db;


When examining the archives using dbms_logmnr package I found a lot of redolog is being written on this table even though I'm using the append hint and no logging options for table and index.

In v$logmnr_contents view I see alot of rows for this table with operation = "DIRECT INSERT" and I can see all the data which was inserted in the redo/undo columns.

Is it related maybe to the DB link?

Am I doing anything else wrong?
It looks like you set your indexes to unusable.
I believe that just means the index won't be used if you query the table.

However, during your loading, the index will be maintained with inserted and updated values, thus generating redo even when nologging is used.

"nologging" for index will only work when used with create index statement.

There may be other reasons but this is one of them.

Try the following and see if it generates less or no redos:


Drop indexes
Load table with /*+APPEND*/(will be faster too without indexes on
the table)
Create index with "nologging" option