Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Omar.

Asked: May 21, 2000 - 1:15 am UTC

Last updated: April 16, 2014 - 4:21 pm UTC

Version: 8.0.5

Viewed 100K+ times! This question is

You Asked

Please Sir , I need to Know how can I get and the comments on tables or columns from the data dict.

with respect

and Tom said...

This information is kept in the dictionary views:

o <USER|ALL|DBA>_TAB_COMMENTS for tables.
o <USER|ALL|DBA>_COL_COMMENTS for columns.


This example shows how to put a comment on a table or column and then retrieve that information. I'm using the USER_ views since the owner of the table is the same as the currently logged in user. If the tables exist in another schema, you would use the ALL_ views and add "where owner = THAT_SCHEMA" to the predicate as well. DBA's, who can see all objects, may use the DBA_ views.

ops$tkyte@8i> create table t ( x int );
Table created.

ops$tkyte@8i> comment on table t
2 is 'This is table T';
Comment created.

ops$tkyte@8i> comment on column t.x
2 is 'This is column X of table T';
Comment created.

ops$tkyte@8i> desc user_tab_comments
Name Null? Type
----------------------- -------- ----------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
COMMENTS VARCHAR2(4000)

ops$tkyte@8i> desc user_col_comments
Name Null? Type
----------------------- -------- ----------------
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
COMMENTS VARCHAR2(4000)

ops$tkyte@8i> colum comments format a50 word_wrap
ops$tkyte@8i> select comments
2 from user_tab_comments
3 where table_name = 'T';

COMMENTS
--------------------------------------------------
This is table T

ops$tkyte@8i> select comments
2 from user_col_comments
3 where table_name = 'T'
4 and column_name = 'X';

COMMENTS
--------------------------------------------------
This is column X of table T


Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Use of comments

David, December 09, 2004 - 5:32 am UTC

Tom,

Do you advocate the use of these table/column comments to document the data model ? Our DBAs frown on them due to the fact they (DBAs) like to do frequent export/import or drop/create maintenance exercises. They say this will remove the comments unless they are preserved and re-created. Is this true ?

If so, what's your preferred method of keeping data model documentation, like what the table / columns are, possible values, relationships with other tables etc. I would like this to be stored in the data dictionary along with the table to make sure its always up-to-date, and thought table/column comments were an appropriate place. Am I wrong ? I couldn't find any existing threads with this sort of question, so hope this is an applicable thread to raise it.

Thanks for a wonderful site/books/advice ....

Regards,
David.

Tom Kyte
December 09, 2004 - 1:18 pm UTC

why do they like to do frequent export/import/drop/create.

but anyway....

exp/imp will preserve the comments.

drop/create is something they should not be using, alter table t MOVE alter index i REBUILD is what they would use.

RE: Use of comments

David, December 10, 2004 - 4:04 am UTC

Hi Tom,

Thanks, but you didn't answer my second point. Ie what is your (or anyone else out there) preferred method of keeping data model documentation, and ensuring it's up-to-date. I was wondering if you use the table/column comments for this purpose? I was thinking of using them, and allowing dynamic HTML pages to be generated from the data dictionary. This will ensure that the docs are always current (being generated at runtime). Could provide this service from a "pre-production" database or copy the comments into another DB using snapshots , or something else. Either way, the docs are (IMHO) placed where they should be - right beside the objects they are documenting. What are the methods employed / endorsed by Oracle ? Please don't say "Use Oracle Designer!" Not an option for us.

Kind regards,
David.

Tom Kyte
December 10, 2004 - 10:54 am UTC

use any tool then - I have personally never used the comment command myself.

the oracle data dictionary does on the other hand.

It would not be a bad idea, i just don't do it myself.


We use tools external from the data dictionary itself to "document" like that.

Comments on Table

Paul Pasquel, October 03, 2006 - 1:21 pm UTC

Hi,

Please I need to know some alternative for getting (recording) comments into our tables and their columns.

We need to keep carriage return and special character on them because our comments will become as our initial user's manuals.

Is there some alternative instead of using 'comment on..' instruction for keeping comments ?

With Respect

Tom Kyte
October 03, 2006 - 4:50 pm UTC

nope.

How to get the column-comment with OCI ?

essy pribadi, July 16, 2008 - 5:10 pm UTC

Hi Tom,

I looked a lot, also in your forum, but I still can't find it... but I think the question is right in here

is there an OCI-Command to get the comment of an column ? I know the commands to get the column-name, the datatype and so on, but I can't find it for the COMMENT on the columns...

pls help me... thanks
Tom Kyte
July 17, 2008 - 11:36 am UTC

you just query it out of the dictionary.

thanks for the answer...

essy pribadi, July 17, 2008 - 2:03 pm UTC

I just thought, there is another way, like we can get nearby every property with OCI... but if you say we can't, for me the problem is solved and as you say, I will query it out... (my first idea when I read this thread...)

thanks again !

Ram, May 15, 2013 - 12:16 pm UTC

How to delete comment from
tables and columns in oracle ?
Tom Kyte
May 16, 2013 - 8:04 am UTC

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4009.htm

... To drop a comment from the database, set it to the empty string ' '.
....

Column Comments Limitation

Diganta Goswami, June 06, 2013 - 3:55 pm UTC

What is the maximum characters we can have in column/table comments in oracle.
Tom Kyte
June 06, 2013 - 4:07 pm UTC 

ops$tkyte%ORA11GR2> desc user_tab_comments;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 TABLE_NAME                               NOT NULL VARCHAR2(30)
 TABLE_TYPE                                        VARCHAR2(11)
 COMMENTS                                          VARCHAR2(4000)


ops$tkyte%ORA11GR2> desc user_col_comments
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 TABLE_NAME                               NOT NULL VARCHAR2(30)
 COLUMN_NAME                              NOT NULL VARCHAR2(30)
 COMMENTS                                          VARCHAR2(4000)



also:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4009.htm

and string is a text literal.. and:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements003.htm#i42617

A text literal can have a maximum length of 4000 bytes.

Modify only comments on a table

A reader, July 22, 2013 - 3:43 pm UTC

Tom,
I have never heard of this but is there a built-in role or privilege that allows a user to only modify comments on a table?

Thanks.
Tom Kyte
July 31, 2013 - 4:27 pm UTC

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4009.htm

<quote>Prerequisites

The object about which you are adding a comment must be in your own schema or:

To add a comment to a table, view, or materialized view, you must have COMMENT ANY TABLE system privilege.

To add a comment to an indextype, you must have the CREATE ANY INDEXTYPE system privilege.

To add a comment to an operator, you must have the CREATE ANY OPERATOR system privilege.
</quote>


so, there is not, but what you can do is create a small procedure that does the comment on the table and grant execute on that small procedure to the user.



the only level of grants for this currently are:

o you can comment on anything you own.
o you can be granted the ability to comment on EVERYTHING


there is nothing in between.

comment on table

Raghav, March 06, 2014 - 7:44 am UTC

Hi Tom

We are planning to update a comment on every new table created in a database say '<table> is created on <date> by <user> '. And we are planning to have a database trigger which will fire when a new table is created and comments are updated on that table.
But, As I understand, we cannot have a DDL in a trigger as it has an implicit commit and it does not allow to update the comments on that table.
Any better idea for updating comments on the table when it was created?
Thanks in advance
Raghav

COMMENTS ON V$ TABLES

Swapnil, April 15, 2014 - 4:07 pm UTC

Hi Tom,

Is there any way (other than Oracle documents) to get column comments on v$ tables? Actually what I am looking for is column description as we get it in oracle database reference document.

Thanks,
Swapnil
Tom Kyte
April 16, 2014 - 4:20 pm UTC

not that I'm aware of

adding to my above question

Swapnil, April 15, 2014 - 4:17 pm UTC

We can find v_$ table corresponding to a v$ table in dba_col_comments but nothing comes up in comments column.
e.g v$session - v_$session

Can we get the column description for v_$ tables from inside the database?

Thanks,
Swapnil
Tom Kyte
April 16, 2014 - 4:21 pm UTC

see above

size of comments

jianhui, June 22, 2015 - 10:44 pm UTC 

Tom,

I did a small test on 11.2.0.1 to see if changing table comments invalidates SQLs or not. Surprisingly it did, why? Would it cause parsing storm for high concurrent SQLs?

SQL> select sql_text, hash_value, sql_id, executions, rows_processed, INVALIDATIONS from v$sqlarea
  2  where sql_text like 'select /* jz */%';

SQL_TEXT
--------------------------------------------------------------------------------
HASH_VALUE SQL_ID  EXECUTIONS ROWS_PROCESSED INVALIDATIONS
---------- ------------- ---------- -------------- -------------
select /* jz */ count(1) from foo
 222718672 75yb5646ncuqh   4   4        0


SQL> comment on table foo is 'c 2';

Comment created.

SQL> select sql_text, hash_value, sql_id, executions, rows_processed, INVALIDATIONS from v$sqlarea
  2  where sql_text like 'select /* jz */%';


SQL_TEXT
--------------------------------------------------------------------------------
HASH_VALUE SQL_ID  EXECUTIONS ROWS_PROCESSED INVALIDATIONS
---------- ------------- ---------- -------------- -------------
select /* jz */ count(1) from foo
 222718672 75yb5646ncuqh   4   4        1



Thanks

A reader, December 03, 2018 - 12:24 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.