1、表字段的注释,到底是保存在哪里的?
USER_COL_COMMENTS,是个系统视图,可真实数据是保存在哪儿的?不会是跟着具体的表结构走的吧?2、最关心的问题,comment on命令修改注释时,会不会引发锁表、或者重建表结构等等的操作?
需要批量修改表注释和字段注释,而数据库又比较忙,担心影响到业务。没找到相关的文档说明,不敢轻易动手,请专家指导!谢谢!
USER_COL_COMMENTS,是个系统视图,可真实数据是保存在哪儿的?不会是跟着具体的表结构走的吧?2、最关心的问题,comment on命令修改注释时,会不会引发锁表、或者重建表结构等等的操作?
需要批量修改表注释和字段注释,而数据库又比较忙,担心影响到业务。没找到相关的文档说明,不敢轻易动手,请专家指导!谢谢!
SELECT t1.Table_Name AS "表名称",
t3.comments AS "表说明",
t1.Column_Name AS "字段名称",
t2.Comments AS "字段说明",
t1.Data_Type AS "数据类型",
t1.Data_Length AS "长度",
t1.NullAble AS "是否为空",
t1.Data_Default "默认值"
FROM cols t1 left join user_col_comments t2
on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join user_tab_comments t3
on t1.Table_name=t3.Table_name
WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
WHERE t4.Object_Type='TABLE'
AND t4.Temporary='Y'
AND t4.Object_Name=t1.Table_Name )
ORDER BY t1.Table_Name, t1.Column_ID;
create or replace view sys.user_col_comments
(table_name, column_name, comments)
as
select o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.com$ co
where o.owner# = userenv('SCHEMAID')
and o.type# in (2, 4)
and o.obj# = c.obj#
and c.obj# = co.obj#(+)
and c.intcol# = co.col#(+)
and bitand(c.property, 32) = 0 说白了注释是储存在sys.com$表中。
给表加comment不会锁表,你可以这么测试:
session 1: lock table t in exclusive mode
session 2: comment on column t.x is 'xxxx';