I want to create a cheat sheet for PL/SQL data dictionary views. It will serve as a quick reference to easily explore and query database metadata.
In RDMS, Data dictionary is a collection of READ-ONLY table and views that contain metadata about the database. It consists data about all the usrers in the system, thier roles, database objects (Table, View, Sequence, Index, and Synonym), storage details, auditing information, and many more.
How to list all Dictionary Views
Using below query you can view the dictionary views avaiable to you.
SELECT table_name
FROM dictionary
ORDER BY table_name;
In the dictionary table you can find the below categories of data dictionary views.
| Prefix | Description |
|---|---|
USER_ | Info about objects owned by the current user |
ALL_ | Info about objects accessible to the user (granted or owned) |
DBA_ | Info about all objects in the database (DBA only) |
CDB_ | For container databases (multitenant architecture) |
V$ | Dynamic performance views (memory, sessions, I/O, etc.) |
GV$ | Global dynamic views (across all instances in RAC) |
NLS_ | Info about National Language Support settings |
What you can find in the Dictionary Views
As discussed, you can find various tables and views which is heLpful to get metadata of database objects.
| Area | Examples |
|---|---|
| All objects | ALL_SOURCE |
| Tables, & Columns | ALL_TABLES, ALL_TAB_COLUMNS |
| Indexes | ALL_INDEXES |
| Sequences | ALL_SEQUENCES |
| Constraints & Keys | ALL_CONSTRAINTS |
| Triggers | ALL_TRIGGERS |
| Views and Synonyms | ALL_VIEWS |
| Procedures & Functions | ALL_PROCEDURES |
| Users and Roles | ALL_USERS, DBA_ROLES, ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS |
| Grants and Privileges | USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS, SESSION_PRIVS, ALL_OBJECTS |
| Tablespaces & Files | DBA_TABLESPACES, USER_TABLESPACES, CDB_TABLESPACES, V$TABLESPACE, DBA_DATA_FILES, V$DATAFILE DBA_TEMP_FILES |
| Storage | DBA_FREE_SPACE, DBA_SEGMENTS, DBA_EXTENTS, DBA_TEMP_FREE_SPACE, DBA_TABLESPACE_USAGE_METRICS |
| Sessions and Locks | V$SESSION, DBA_LOCK |
| Dependencies | ALL_DEPENDENCIES |
You can see the above tables which is available in the dictionary and you can add the prefixes such as USER, ALL and DBA to the same to access the different tables as show below:
Tables
ALL_TABLES describes the relational tables accessible to the current user. To gather statistics for this view, use the DBMS_STATS package.
SELECT table_name FROM all_tables
WHERE tablespace_name = 'EXAMPLE' ORDER BY table_name;
DBA_TABLES describes all relational tables in the database.
SELECT * from DBA_TABLES;
USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.
SELECT * from USER_TABLES;
To check the table size in the current schema.
SELECT SEGMENT_NAME,SEGMENT_TYPE, SUM(BYTES/1024/1024/1024) GB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME='&TableName'
GROUP BY SEGMENT_NAME,SEGMENT_TYPE;
I hope from the above you will get an idea of the metadata tables in the Pl/SQL which can use for monitoring the database.
What are your thoughts on this post?
I’d love to hear from you! Click this link to email me, I reply to every message!
Also use the share button below if you liked this post. It makes me smile, when I see it.