1 Information on Oracle Dictionary

You must be aware the information available on Oracle dictionary, because no matter how good you are, you can’t remember everything, and you must know how to find it on oracle.

1.A Types of views

USER_, are views showing information related to user
ALL_, are views showing information related to all user
DBA_, are views showing information related to all user, and sometimes has more columns than ALL_ views, But I didn’t found a dba view having more columns than a all view, in the few test I did like this.
SQL> select TABLE_NAME,count(*) from dba_tab_columns where table_name like '%_TAB_COLUMNS' GROUP BY TABLE_NAME; The additional column is owner, user views don’t need them, because shows only user information.

1.B Finding where to find information

You want to know where is information about lob segments, because you found that when moving tables to another tablespace, you must move lob segments too, where will you search the name of the lobs?
SQL> select view_name from all_views where view_name like '%LOB%'; 1.B.i Hunting views Finding some views can be tricky, in some situations you’ll find usefull to include like 'ALL\_%TAB%' ESCAPE '\' and 'DBA\_%TAB%' ESCAPE '\';
To avoid other kind of views, if you pay attention there are some views appearing with ALL_ and not with DBA_ and vice versa.
SQL> select VIEW_name from all_views where view_name like 'ALL\_%TAB%' ESCAPE '\'; SQL> select VIEW_name from all_views where view_name like 'DBA\_%TAB%' ESCAPE '\';

1.C Looking inside views

If you select the text column of the all_views view, you will have the description of the view, this helps you some times to understand some memory tables, for example for dba_lobs the query is the following But sometimes you will find references like this
For V_$SESSION view is
select "SADDR","SID","SERIAL#","AUDSID","PADDR","USER#","USERNAME","COMMAND","OWNERID"…
from v$session
Then you want to see the content of v$session, but there are not information
SELECT * FROM ALL_VIEWS WHERE VIEW_NAME LIKE '%V$SESSION%';
So you try to find the object through the synonym, and you find a “short-circuit” Then the question is: Where I can know the source query of the fixed views
The answer is V$FIXED_VIEW_DEFINITION
SQL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME ='V$SESSION'; ------------------------------------------------------------------------------------------------------------------------ If you are not satisfied with this answer, you can go further investigating how is GV$SESSION build
SQL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME ='GV$SESSION'; Some dbas sometimes modifies the queries like the previous, at this level to improve performance and create their own views, to get just the information they need.
You must keep in mind this can change from release to release,
so you will have to re-analyze your “low-level” queries after you apply some important patch,
if you decided to to them.

1.D Comments

Some views contains interesting comments about views
SQL> SELECT VIEW_NAME FROM ALL_VIEWS WHERE VIEW_NAME LIKE '%COMMENT%' ORDER BY 1; For example
select distinct table_name,column_name,comments from dba_col_comments where comments is not null
order by 2