OraFAQ Forum: SQL & PL/SQL » unconditional table level supplemental logging

Home » SQL & PL/SQL » SQL & PL/SQL » unconditional table level supplemental logging (10.2.0.3 windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

unconditional table level supplemental logging [message #505998]

Fri, 06 May 2011 00:40

sekar52
Messages: 141
Registered: May 2010
Location: Mumbai

Senior Member

Hi I want to enable unconditional table level supplemental logging on unique index columns.

But Alter table tablename add supplemental log data (unique index) columns always;

gives error.If i omit always it is accepting but while it unconditionally log the unique index columns.

Report message to a moderator

Re: unconditional table level supplemental logging [message #505999 is a reply to message #505998]

Fri, 06 May 2011 00:47

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

gives error

Which one?
Use SQL*Plus and copy and paste your session, the WHOLE session like this:

SQL> Alter table tablename add supplemental log data (unique index) columns always;
Alter table tablename add supplemental log data (unique index) columns always
                                                                       *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Regards
Michel

[Updated on: Fri, 06 May 2011 00:48]

Report message to a moderator

Re: unconditional table level supplemental logging [message #506001 is a reply to message #505999]

Fri, 06 May 2011 00:49

sekar52
Messages: 141
Registered: May 2010
Location: Mumbai

Senior Member

SQL> alter table ts2.emp add supplemental log data (unique index) columns always;
alter table ts2.emp add supplemental log data (unique index) columns always
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

*************
When we specify without always it takes as conditional logging

SQL> alter table ts2.emp add supplemental log data (unique index) columns;

Table altered.

SQL> select * from dba_log_groups where TABLE_NAME='EMP';

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
------------------- --------------
TS2 SYS_C0011004 EMP UNIQUE KEY LOGGING CONDITIONAL GENERATED NAME

Now my problem is after i enable supplemental logging if I update a column that is not present in unique index columns then the unique index column wont be loged because the log group is conditional..

Edited by: Sekar_BLUE4EVER on May 5, 2011 10:30 PM

Report message to a moderator

Re: unconditional table level supplemental logging [message #506004 is a reply to message #506001]

Fri, 06 May 2011 01:07

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You cannot specify ALWAYS on an "supplement id key" only on a "supplemental log group".
For a "supplement id key", documentation stated:

Quote:

If you specify UNIQUE COLUMNS, then for all tables with a unique key or a bitmap index, if any of the unique key or bitmap index columns are modified, the database places into the redo log all other columns belonging to the unique key or bitmap index. Such a log group is a system-generated conditional log group.

Instead create a "supplemental log group" with all the columns of your key:
add supplemental log group (<list of unique columns>) always.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: unconditional table level supplemental logging [message #506006 is a reply to message #506004]

Fri, 06 May 2011 01:11

sekar52
Messages: 141
Registered: May 2010
Location: Mumbai

Senior Member

Thanks
So in this case if someone doesnt modify the unique key columns and modified only other columns then the unique key indexes wont be logged.Am I right?

Report message to a moderator

Re: unconditional table level supplemental logging [message #506008 is a reply to message #506006]

Fri, 06 May 2011 01:14

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think so but the best way to be sure is to test it.

Regards
Michel

Report message to a moderator

Previous Topic:	(2 Merged) please help me with dis error Error at line 76: PL/SQL: ORA-00923: FROM keyword not found
Next Topic:	Buffer String too small(2 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 08:31:58 CDT 2025