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;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.