A Script to Add Supplemental Logging at Table Level

Here is a script which may be used to generate script which enables supplemental logging at table level for all tables.

declare
out varchar2(2000);
cflag number;
begin
-- You may change this line, i am choosing tables with only 50 to 250 MBs and owner OWNR
for l in ( select segment_name from dba_segments where bytes/(1024*1024) between 50 and 250 and owner in ('OWNR') and SEGMENT_TYPE = 'TABLE' and segment_name not like 'BIN%' order by segment_name)
loop
cflag := 0;
out :='alter table OWNR.'||l.segment_name||' ADD SUPPLEMENTAL LOG GROUP "'||substr(l.segment_name,1,25)||'_SLOG" (';


-- If there is a PK then add its columns
for k in (select COLUMN_NAME from DBA_IND_COLUMNS where INDEX_NAME in (select index_name from dba_constraints where CONSTRAINT_TYPE = 'P' and table_name=l.segment_name) order by COLUMN_POSITION)

loop
if (cflag = 0) then out:=out||k.column_name;
else out:=out||','||k.column_name;
end if;
cflag := 1;
end loop;

-- If there is no PK but there is a UI, then add its columns.
-- Normally there should not be more then one UI on any table. If there is, then is a problem. You may check it with this sql:
-- select TABLE_NAME,count(*) from dba_indexes where INDEX_TYPE='NORMAL' and UNIQUENESS='UNIQUE' and owner='OWNR' and
-- TABLE_NAME not in (select TABLE_NAME from dba_constraints where CONSTRAINT_TYPE = 'P') group by TABLE_NAME having count(*) > 1;

if (cflag = 0) then
for m in (select COLUMN_NAME from DBA_IND_COLUMNS where INDEX_NAME in (select index_name from dba_indexes where INDEX_TYPE='NORMAL' and UNIQUENESS='UNIQUE' and owner='OWNR' and table_name=l.segment_name) order by COLUMN_POSITION)

loop
if (cflag = 0) then out:=out||m.column_name;
else out:=out||','||m.column_name;
end if;
cflag := 1;
end loop;
end if;
-- If there is no PK and UI, then add all columns to supplemental logging
if (cflag = 0) then
for n in (select COLUMN_NAME from dba_tab_columns where table_name=l.segment_name order by COLUMN_ID)
loop
if (cflag = 0) then out:=out||n.column_name;
else out:=out||','||n.column_name;
end if;
cflag := 1;
end loop;
end if;

out := out||') ALWAYS;';
dbms_output.put_line(out);
end loop;
END;
/