当迁移到测试数据库后发现注释信息均为乱码后可以使用这个方法快速的恢复。
1.查询表级别的注释信息
select 'COMMENT ON TABLE '||table_name||' IS '''||comments||''';' from user_tab_comments;
2.查询表中列的注释信息
select 'COMMENT ON COLUMN '||table_name||'.'||COLUMN_NAME||' IS '''||COMMENTS||''';' from user_col_comments;
以上提到了两个视图,一个是user_tab_comments,另一个是user_col_comments,这两个视图分别记录了表一级别的和行一级别的注释信息。
oralce官方文档中是这样描述的:
ALL_TAB_COMMENTS
ALL_TAB_COMMENTS displays comments on the tables and views accessible to the current user.
Related Views
-
DBA_TAB_COMMENTS displays comments on all tables and views in the database.
-
USER_TAB_COMMENTS displays comments on the tables and views owned by the current user. This view does not display the OWNER column.
Column | Datatype | NULL | Description |
---|---|---|---|
OWNER | VARCHAR2(30) | NOT NULL | Owner of the object |
TABLE_NAME | VARCHAR2(30) | NOT NULL | Name of the object |
TABLE_TYPE | VARCHAR2(11) | Type of the object | |
COMMENTS | VARCHAR2(4000) | Comment on the object |
ALL_COL_COMMENTS
ALL_COL_COMMENTS displays comments on the columns of the tables and views accessible to the current user.
Related Views
-
DBA_COL_COMMENTS displays comments on the columns of all tables and views in the database.
-
USER_COL_COMMENTS displays comments on the columns of the tables and views owned by the current user. This view does not display the OWNER column.
Column | Datatype | NULL | Description |
---|---|---|---|
OWNER | VARCHAR2(30) | NOT NULL | Owner of the object |
TABLE_NAME | VARCHAR2(30) | NOT NULL | Name of the object |
COLUMN_NAME | VARCHAR2(30) | NOT NULL | Name of the column |
COMMENTS | VARCHAR2(4000) | Comment on the column |
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-586836/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-586836/