Oracle comment添加注释
Oracle 的 COMMENT 语句可以给一个列、表、视图或快照添加一个最多 2K 字节的注释。注释被存储在数据字典中,并且可以通过数据字典视图 DBA_COL_COMMENTS (列的注释)和 DBA_TAB_COMMENTS (表的注释)查看 COMMENTS 列。 COMMENT 语句的语法:
COMMENT ON TABLE tb | COLUMN tb.cols IS 'text';
其中, tb 是表的名字, cols 是表中列的名字, text 是注释的文本。可以用设置注释为空串( '' )的办法从数据库中删除一个注释,例如:
COMMENT ON TABLE employees IS '';
COMMENT ON TABLE HR.employees IS 'Employee Information';
COMMENT ON COLUMN HR.employees.FIRST_NAME IS 'Employee FIRST_NAME';
对于 SYS 用户下的表,只有拥有 ALTER 权限的普通用户才可以对其进行 COMMENT 操作。对于普通用户下的表,拥有“ COMMENT ANY TABLE ”或 ALTER 权限的普通用户都可以执行 COMMENT 操作。示例如下:
SYS@test18c> create table t as select * from dual;
Table created.
SYS@test18c> create user lhr33 identified by lhr;
User created.
SYS@test18c> grant create session to lhr33;
Grant succeeded.
SYS@test18c> GRANT SELECT ON sys.t to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c>
LHR33@test18c> select * from sys.t;
DU
--
X
LHR33@test18c> comment on column sys.t.dummy is 'aa';
comment on column sys.t.dummy is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> comment on table sys.t is 'aa';
comment on table sys.t is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> grant alter on sys.t to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c>
LHR33@test18c> comment on table sys.t is 'aa';
Comment created.
LHR33@test18c> comment on column sys.t.dummy is 'aa';
Comment created.
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> revoke alter on sys.t from lhr33;
Revoke succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on column sys.t.dummy is 'aa';
comment on column sys.t.dummy is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> grant COMMENT ANY TABLE to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on column sys.t.dummy is 'aa';
comment on column sys.t.dummy is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> comment on table sys.t is 'aa';
comment on table sys.t is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> grant alter on sys.t to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on table sys.t is 'aa';
Comment created.
对于普通用户下的表,拥有 “ COMMENT ANY TABLE ”或 ALTER 权限的用户都可以执行 COMMENT 操作: