Tuesday 27 April 2010

Oracle: convert a varchar2 to a CLOB without dropping the table

Current defn of column in table NOTIFICATION

DETAIL VARCHAR2(4000),

Want it to be a CLOB.

create a temporary table 'NOTIFICATION2' with the required column defintions. This is then used as a template to convert the table column.

exec dbms_redefinition.start_redef_table('TSUSER','NOTIFICATION','NOTIFICATION2','ID, RECIPIENT, SUBJECT, TO_CLOB(DETAIL) DETAIL, FOOTER, ISSUE_DATE, SENT_DATE, SENT_STATUS, RETRY_COUNT, ROLE',dbms_redefinition.cons_use_pk);
exec dbms_redefinition.finish_redef_table('TSUSER','NOTIFICATION','NOTIFICATION2');

No comments: