Tag Archives: alter table hr logging;

Script to enable logging and nologging mode of table in Oracle

Script to enable logging and nologging mode of table in Oracle

It will help in Dataguard System when you want to spike any table from logging so that you have actual data replicated in your DR system
Example:
I have database which have table in GB size only has information about ETL Jobs running which will generate log in that table. I want to reduce network load to take that table in DR System. So we use that table with NO logging option so that it will generate less log.

Script for alter table in logging mode:

begin
for r in ( select table_name from dba_tables where owner='HR' and logging='NO')
loop
execute immediate 'alter table hr.'|| r.table_name ||' LOGGING';
end loop;
end;
/

Script for alter table in NOlogging mode:

begin
for r in ( select table_name from dba_tables where owner='HR' and logging='YES')
loop
execute immediate 'alter table hr.'|| r.table_name ||' NOLOGGING';
end loop;
end;
/