Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can't select dictionary user view USER_TAB_COMMENT ?

D.VegaMar 11 2013 — edited Mar 11 2013
Hi.

I'm having this weird problem in 11.2.0.3

I cant select the dictionary view USER_TAB_COMMENT from a user, but can from others.
If I try to describe it I get ORA-00942 and ORA-04043 messages.

Here is my attempt. I create a comment on a table, try to desc or select the dictionary view. Then connect with SYS, and select the comment in the 'dba' view to check if the comment has been created:
SQL> conn RODOLFO
Introduzca la contraseña:
Conectado.
SQL> show user
USER es "RODOLFO"
SQL> comment on table DOCUMENTILLOS is 'This is a comment';

Comentario creado.

SQL> select table_name, comments from user_tab_comments;
select table_name, comments from user_tab_comments
                                 *
ERROR en línea 1:
ORA-00942: la tabla o vista no existe

SQL> desc user_tab_comments;
ERROR:
ORA-04043: el objeto "SYS"."DBA_TAB_COMMENTS" no existe


SQL> desc all_tab_comments;
ERROR:
ORA-04043: el objeto "SYS"."DBA_TAB_COMMENTS" no existe


SQL> conn / as sysdba
Conectado.
SQL> show user
USER es "SYS"
SQL> select owner, table_name, comments from dba_tab_comments
  2  where owner = 'RODOLFO' and table_name = 'DOCUMENTILLOS'
  3  ;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COMMENTS
--------------------------------------------------------------------------------
RODOLFO                       DOCUMENTILLOS
This is a comment


SQL>
From other users, I don't have this problem
SQL> conn MANOLO
Introduzca la contraseña:
Conectado.
SQL> show user
USER es "MANOLO"
SQL> comment on table DOCS is 'This is a comment';

Comentario creado.

SQL> select table_name, comments from user_tab_comments where table_name = 'DOCS';

TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
DOCS
This is a comment
Any clues why this is happening?

Regards.
This post has been answered by Peter Gjelstrup on Mar 11 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 8 2013
Added on Mar 11 2013
7 comments
474 views