Re: Change Data Capture 10gR2

From: Mark Van de Wiel <mark.van.de.wiel_at_gmail.com>
Date: Wed, 04 Aug 2010 22:11:02 -0700
Message-ID: <4C5A47E6.1060301_at_gmail.com>

Michael,

You can write a bit of PL/SQL around the dbms_cdc_publish call and query dba_tab_columns to generate the list of columns with the data types.

E.g. something like:

select column_name
|| ' '
|| decode( data_type

          , 'NUMBER', decode( data_precision
                            , null, 'NUMBER'
                            , 'NUMBER('


                              || data_precision

                              || ','

                              || data_scale

                              || ')'

                            )
          , decode ( data_type
                   , 'DATE', data_type
                   , data_type
                     || '('
                     || data_length
                     || ')'
                   )
          ) value

from dba_tab_columns
where table_name = <your table>
and owner = <owner> ;

Extend that a little and you generate all statements for a set of tables by just running a simple script.

Mark.

On 08/04/2010 03:33 PM, Kellyn Pedersen wrote:

> It may not help you with the columns, but just a warning-  I use CDC a
> lot and found out if you have any unused columns in a table, it will
> cause you a mighty headache in failures. CDC does not like unused
> columns! :(
> Not that most people have this problem, but as a new DBA starting out
> here, I sure was unhappy to see them:
> select owner, table_name, count from dba_unused_col_tabs;
>
> Kellyn Pedersen
> Sr. Database Administrator
> I-Behavior Inc.
> http://www.linkedin.com/in/kellynpedersen
> www.dbakevlar.blogspot.com <http://www.dbakevlar.blogspot.com/>
> "Go away before I replace you with a very small and efficient shell
> script..."
>
>
> --- On *Wed, 8/4/10, Michael Dinh /<mdinh_at_XIFIN.Com>/* wrote:
>
>
>     From: Michael Dinh <mdinh_at_XIFIN.Com>
>     Subject: Change Data Capture 10gR2
>     To: "ORACLE-L" <oracle-l_at_freelists.org>
>     Date: Wednesday, August 4, 2010, 3:20 PM
>
>     Hello
>
>     I am going through the documentations on how to set up CDC with
>     Asynchronous HotLog.
>
>     Executing DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE requires listing all
>     column names and data types.
>
>     Is there an efficient way of getting all the columns in the table?
>
>     BEGIN
>
>     DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
>
>     owner => 'cdcpub',
>
>     change_table_name => 'products_ct',
>
>     change_set_name => 'CHICAGO_DAILY',
>
>     source_schema => 'SH',
>
>     source_table => 'PRODUCTS',
>
>     column_type_list => 'PROD_ID NUMBER(6), PROD_NAME VARCHAR2(50),
>
>     PROD_LIST_PRICE NUMBER(8,2)',
>
>     capture_values => 'both',
>
>     rs_id => 'y',
>
>     row_id => 'n',
>
>     user_id => 'n',
>
>     timestamp => 'n',
>
>     object_id => 'n',
>
>     source_colmap => 'n',
>
>     target_colmap => 'y',
>
>     options_string => 'TABLESPACE TS_CHICAGO_DAILY');
>
>     END;
>
>     /
>
>     TIA
>
>     Michael Dinh : XIFIN : 858.436.2929
>
>     NOTICE OF CONFIDENTIALITY - This material is intended for the use of
>     the individual or entity to which it is addressed, and may contain
>     information that is privileged, confidential and exempt from
>     disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL
>     MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS
>     MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU
>     AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN,
>     EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO
>     PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If
>     the reader of this email (and attachments) is not the intended
>     recipient, you are hereby notified that any dissemination,
>     distribution or copying of this communication is strictly
>     prohibited. Please notify the sender of the error and delete the
>     e-mail you received. Thank you.
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 05 2010 - 00:11:02 CDT