Oracle2PolarDB:正确获取Oracle表和列注释信息脚本

适用于 

  • PolarDB-O纯软版、混合云版和公共云版。
  • Oracle 10g、11g、12c、18c、19c。

目的

如何快速获取Oracle特定用户的表注释和列注释信息,并在PolarDB-O数据库进行复制。

解决办法

通常注释的内容包含特殊字符(例如:单引号(')),因此需要使用chr(39)进行特殊处理。

通过dba_tab_comments视图获取表注释

依次执行以下SQL语句,通过dba_tab_comments视图获取表注释。

set lines 200 pages 1000

col tab_comments for a80

SELECT 'comment on table ' || owner || '.' || table_name || ' is ''' || comments || ''';' AS TAB_COMMENTS FROM (SELECT owner, table_name, CASE WHEN comments LIKE ('%' || chr(39) || '%') THEN replace(comments, chr(39), chr(39) || chr(39)) ELSE comments END AS comments FROM dba_tab_comments WHERE comments IS NOT NULL AND owner IN ('[$User1]', '[$User2]', '[$User3]'));

说明:代码中的[$User1]、[$User2]和[$User3]等都为真实的业务用户,且都是大写字母。

表注释如下所示。

通过dba_col_comments视图获取列注释

依次执行以下SQL语句,通过dba_col_comments视图获取列注释。

set lines 200 pages 1000

col tab_col_comments for a80

SELECT 'comment on column ' || owner || '.' || table_name || '.' || column_name || ' is ''' || comments || ''';' AS TAB_COL_COMMENTS FROM (SELECT owner, table_name, column_name, CASE WHEN comments LIKE ('%' || chr(39) || '%') THEN replace(comments, chr(39), chr(39) || chr(39)) ELSE comments END AS comments FROM dba_col_comments WHERE comments IS NOT NULL AND owner IN ('[$User1]', '[$User2]', '[$User3]'));

列注释如下所示。

参考文档