The tables in the data dictionary are named in a certain pattern. The normally start with the prefixes USER, ALL, or DBA. For example, USER_TABLES is all tables owned by the current user. ALL_TABLES is all tables the current user has access to. And DBA_TABLE is all the table in the database.
There are V_$ views which have a public synonym which starts with V$. An example is V$SESSION. These views are dynamic performance views. There are also GV_$ views, with public synonyms that start with GV$. These are global dynamic performance views. If you want to join some of these views together, you should first copy their data to temp tables. Then you can query the temp tables. This is because of the dynamic nature of these views based on what is going on in the system.
USER_SYNOYNMS contains private synonyms for the current user. PUBLIC synonyms are in ALL_SYNONYMS and DBA_SYNONYMS. USER_CATALOG has a bit of information on tables, views, synonyms and sequences owned by the current user. USER_OBJECTS has more information on objects owned by the current user.
Some of the most important data dictionary views are USER_TABLES and USER_TAB_COLUMNS. These have corresponding synonyms TABS and COLS. You can find out about your tables and columns in those tables with these views.
USER_SYS_PRIVS has the system privileges granted to the current yser. USER_ROLE_PRIVS has the roles granted to the current user. And USER_TAB_PRIVS has privileges granted on objects that the current user is involved with. "Involved" means is the owner, is granted privilege to, or has granted privilege for.
Finally the USER_CONSTRAINTS view has info on constraints owned by the current user. They are divided up by constraint type:
- P for primary key
- R for foreign key
- U for unique
- C for check and NOT NULL
