Databases Reference
In-Depth Information
Of course, if you want to get away from both configuration and all other ongoing main‐
tenance tasks, you may want to consider the Oracle Database Cloud, a public cloud
offering described in more detail in
Chapter 15
.
The Data Dictionary
Each Oracle Database includes a set of
metadata
that describes the data structure in‐
cluding table definitions and integrity constraints. The tables and views that hold this
metadata are referred to as the Oracle
data dictionary
. All of the components discussed
in this chapter have corresponding system tables and views in the data dictionary that
fully describe the characteristics of the component. You can query these tables and views
using standard SQL statements.
Table 2-1
shows where you can find some of the infor‐
mation available about each of the components in the data dictionary.
The SYSTEM tablespace always contains the data dictionary tables. Data dictionary
tables that are preceded by the V$ or GV$ prefixes are dynamic tables, which are con‐
tinually updated to reflect the current state of the Oracle Database. Static data dictionary
tables can have a prefix such as DBA_, ALL_, or USER_ to indicate the scope of the
objects listed in the table.
With the introduction of the multitenant architecture in Oracle 12
c
, a new level has
been introduced to the Oracle Database dictionary. DBA_, ALL_ and USER_ views exist
within the context of a pluggable database, while a new set of views with the prefix CDB_
are available in the root container, which aggregates the DBA_ views from all pluggable
databases associated with that container. Common users defined within the container
database will be able to see information for all PDBs for which they have privileges when
querying CDB_ views. Users within a PDB will see data dictionary views just as if they
were users within a standard non-CDB instance.
Table 2-1. Partial list of database components and their related data dictionary views
Component
Data dictionary tables and views
Database
Pluggable databases
V$DATABASE, V$VERSION, V$INSTANCE
V$CONTAINERS, V$PDBS
Shared server
V$QUEUE, V$DISPATCHER, V$SHARED_SERVER
Connection pooling
DBA_CPOOL_INFO, V$CPOOL_STATS, V$CPOOL_CC_STATS
Tablespaces
USER_FREE_SPACE, DBA_FREE_SPACE, V$TEMPFILE, DBA_USERS, DBA_TS_QUOTAS
Control files
V$CONTROLFILE, V$PARAMETER, V$CONTROLFILE_RECORD_SECTION
Datafiles
V$DATAFILE, V$DATAFILE_HEADER, DBA_DATA_FILES, DBA_EXTENTS, USER_EXTENTS
Segments
DBA_SEGMENTS, USER_SEGMENTS
Extents
DBA_EXTENTS, USER_EXTENTS
Redo logs
V$THREAD, V$LOG, V$LOGFILE, V$LOG_HISTORY
Undo
V$UNDOSTAT, V$ROLLSTAT, V$TRANSACTION
