The data dictionary describes objects; the V$ views describe the running database: sessions, parameters, the instance, SQL statements in memory, waits, and much more. Developers use a handful of them to answer everyday questions such as "which session am I", "what is this parameter set to", and "which version is this database".
Code for This Guide
The main examples are in the examples/dictionary folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Useful V$ Views
| View | Shows |
|---|---|
| V$SESSION | Every session: user, program, status, current SQL, what it waits for |
| V$PARAMETER | Initialization parameters and their current values |
| V$INSTANCE, V$VERSION | The instance and the database version |
| V$SQL | SQL statements in the shared pool, with execution statistics |
| V$PDBS | The pluggable databases of a container database |
| V$SESSION_LONGOPS | Progress of long-running operations |
Reading V$ views needs privileges, such as the SELECT_CATALOG_ROLE that the NIMBUS user has.
Your Session and Parameters
Example:
select sid, serial#, username, program, status
from v$session
where sid = sys_context('USERENV', 'SID');
select name, value from v$parameter
where name in ('db_name', 'compatible', 'undo_retention', 'max_string_size')
order by name;Output:
SID SERIAL# USERNAME PROGRAM STATUS
______ __________ ___________ __________ _________
70 45431 NIMBUS SQLcl ACTIVE
NAME VALUE
__________________ ___________
compatible 23.6.0
db_name FREE
max_string_size STANDARD
undo_retention 900sys_context('USERENV', 'SID') identifies your own session in V$SESSION. V$PARAMETER shows, for example, that MAX_STRING_SIZE is STANDARD, which limits VARCHAR2 to 4,000 bytes.
Instance and Version
Example:
select instance_name, version_full, status, startup_time is not null as started from v$instance; select banner_full from v$version;
Output:
INSTANCE_NAME VERSION_FULL STATUS STARTED ________________ _______________ _________ __________ FREE 23.26.3.0.0 OPEN true BANNER_FULL ______________________________________________________________________________________ Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free Version 23.26.3.0.0
VERSION_FULL and BANNER_FULL give the exact release, here Oracle AI Database 26ai Free 23.26.3.
Pluggable Databases
Connected to the root as SYS, V$PDBS lists the pluggable databases and whether they are open.
Example:
select con_id, name, open_mode from v$pdbs order by con_id;
Output:
CON_ID NAME OPEN_MODE
_________ ___________ _____________
3 FREEPDB1 READ WRITEThings to Know
- V$ views are synonyms for V_$ views owned by SYS; grants are made on the V_$ names.
- GV$ views show the same data for every instance of a RAC database.
- Values reflect the current state and change from second to second; save snapshots if you need history.
Related Guides
Conclusion
V$ views expose the live state of the database: sessions, parameters, the instance, SQL in memory, and PDBs. Find your own session with SYS_CONTEXT, read parameters from V$PARAMETER, and check the exact version in V$INSTANCE and V$VERSION.
