OraQA

Oracle provides some dictionary views to give information about the database object properties. Objects can be tables, indexes, procedures, … You can group these views in ALL_%, USER_%, DBA_% Views.

• USER_ prefixed Views give details about the objects of the current connected user; You “see” only your own objects ( Example USER_TABLES ).
• ALL_ prefixed Views give details about ALL objects you can access. This does not mean ALL objects of the database but the objects in the current connected user + the objects in other schemas where access is granted ( Example ALL_TABLES ). ALL_-Views include the OWNER column.
• DBA_ Views give details about all objects of all schemas. It’s accesible by User with DBA Role or User with SELECT_CATALOG_ROLE granted ( Example DBA_TABLES ). DBA_-Views include the OWNER column.

SQL> desc user_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------

 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 ...

SQL> desc all_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------

 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 ...

Hence the structure of the Views are almost identical.

Oracle Documentation describes only the ALL_ prefixed dictionary -Views.

HTH

Karl

Filed under Administration, SQL. Follow any responses to this entry through this RSS 2.0 feed.