当迁移到测试数据库后发现注释信息均为乱码后可以使用这个方法快速的恢复。
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博客 ” ，链接：https://blog.itpub.net/70034820/viewspace-2994147/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 中国OCM联盟隆重落户ITPUB社区，百位OCM已入驻，欢迎更多的OCM加入我们哦！
- OCMU联盟-一个有温度的联盟
- 丢掉盔甲，我们依然是骄傲的骑士——Secooler“三问四看”话认证培训
- 【数据安全】一次惊心动魄的ASM磁盘头损坏故障处理过程带来的深思
- 【参数】恢复db_recovery_file_dest_size参数为默认值“0”方法
- 【如何做快乐的DBA】ITPUB社区第一期博客专家专题“如何做快乐的DBA”
- 【Bitmap Index】B-Tree索引与Bitmap位图索引的锁代价比较研究
- 【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理
- 【CUBE】Oracle分组函数之CUBE魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
