Re: Asynchonous CDC in 10GR2

David,

Yes, I noticed that yesterday. I wish it was based on range partitioning on TIMESTAMP$, so I can have monthly partitions (that way I can do exchange partition). No luck:( ..... I probably can achieve this by defining multiple CHANGE SETs for a month using the begin_date, end_date - except that it will still be partitioned on CSCN$. Any comments on this?

I tried dropping the change table using 'drop table ..', so that I can re-create it as range partitioned on TIMESTAMP$. But Oracle would not allow that, as the table can be dropped only via DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE.

>>1. After letting dbms_cdc_publish.create_change_table create the change table, is it ok to (manually) add columns to it? I need to >>add a couple of snapshot columns that are not present in the base table.

>>2. Would it be possible to implement a database insert trigger on the change table to populate the snapshot columns of the change >>table?

I could add columns to the table. I also added a before insert trigger , the trigger compiled fine, but would not execute. I knew this is the behavior in Streams, same goes here as well. I believe the normal database triggers won't work in this situation ...if you have any ideas, that would be great.

Thanks,

Arul

On 6/8/06, David Lord <davidclord@fastmail.co.uk> wrote:

Arul,

The change tables are partitioned on a high value of the CSCN$.  As to
adding columns, everything I can see suggests that the change tables are
perfectly normal, there doesn't appear to be any 'magic' going on.  In
fact, when I logged a TAR for a performance problem with a query on a
change table, Oracle support suggested that it was okay to add indexes
to them (I ran into problems when I did because the purge job marked the
indexes as unusable).

Regards
David

On Wed, 7 Jun 2006 10:42:52 -0700, "Arul Ramachandran"
< contactarul@gmail.com> said:
> David,
>
> Thanks for your reply.
>
> I am aware of the ALTER_CHANGE_TABLE, but my intention is to add columns
> to
> the change table that are not present in the source table. Hence, I was
> wondering if I could manually alter the change table.
>
> Regarding, partitioning of change table, I see from your comment CDC does
> partition it (probably a max val partition?).
>
> I don't have my environment yet, otherwise I can check out.
>
> Regards,
> Arul
--
David Lord
davidclord@fastmail.co.uk

--
http://www.fastmail.fm - Or how I learned to stop worrying and
                         love email again

--
Arul

Re: Asynchonous CDC in 10GR2

Arul Ramachandran

2006-06-09