Skip to content
Home » Oracle Database » How to Get Table Column Comment DDL

How to Get Table Column Comment DDL

Column Comment DDL

During metadata migration, I found all column comments with multi-byte characters are garbled. So what I was going to do next is to correct the garbled data and then feed all column comment into the target database.

Now where we can find column comment DDL? For exporting column comments, we can use DBA_COL_COMMENTS to retrieve the information we need.

For example, we'd like to export all non-empty column comments of user HR in the source database, we can compose column comment DDL like this:

select 'COMMENT ON COLUMN ' || owner || '.' || table_name || '.' || column_name || ' IS ' || '''' || COMMENTS ||  ''';' from dba_col_comments where owner = 'HR' and comments is not null;

We create a SQL file for composing DDL, this is our first SQL file for convenience.

[oracle@ora9i ~]$ vi spool_table_column_comment.sql
set echo off heading off feedback off pagesize 10000 trimspool on linesize 10000;
spool table_column_comment_for_hr.sql
select 'COMMENT ON COLUMN ' || owner || '.' || table_name || '.' || column_name || ' IS ' || '''' || COMMENTS ||  ''';' from dba_col_comments where owner = 'HR' and comments is not null;
spool off;
exit

In which, it will spool all DDL of column comments to the second SQL file. So next, we execute the first SQL file in the source database.

[oracle@ora9i ~]$ sqlplus -s / as sysdba @spool_table_column_comment.sql

COMMENT ON COLUMN HR.COUNTRIES.COUNTRY_ID IS 'Primary key of countries table.';
COMMENT ON COLUMN HR.COUNTRIES.COUNTRY_NAME IS 'Country name';
...

The output was spooled as the second SQL file which contains all column comment DDL. Then we copied the second SQL file to the target database for finishing metadata migration job.

[oracle@ora12c ~]$ echo "exit" >> table_column_comment_for_hr.sql
[oracle@ora12c ~]$ sqlplus -s / as sysdba @table_column_comment_for_hr.sql
...

Done! We have migrated all column comment to the target database.

In our case, I migrated a 9i database to a 12c database, and I think you might be interested in how to access 9i from 12c database through a database link and work around ORA-03134.

Leave a Reply

Your email address will not be published. Required fields are marked *