This article is about data dictionary views in Oracle 12c. It is little bit different one from the last one due to its USB which is the cloud. We all know what is Data Dictionary and its importance? In 12c we have four types of static views which are like this USER, ALL, DBA, CDB.
Object Categories:-
- X$:- It provides information of memory structures (arrays).
- V$:- It explains about Views based on X$ structures.
- GV%:-Same as V$ except including instance identifier as the first column.
- CDB_:- All objects in all databases within the CDB including CDB$ROOT and PDB$SEED.
- DBA_:- With the help of this one we can have information All objects in the current database.
- ALL_:-All objects owned by the user and on which the user has been granted privileges.
- USER_:-All objects owned by the user.
Let’s have brief on views:-
- v$fixed_view_definition:- the v$fixed_view_definition view is used to display the definition for any of the v$views. Use these definitions to optimize your queries by using indexed columns of the dynamic performance tables.
- v$containers:- with the help of this view any dba can get the information about PDBs and the root associated with the current instance.
- V$PDBS:- It displays information about PDBs associated with the current instance.
- v$backup_copy_details:- contains information about all available control file and datafile copies. we can have information about SESSION_KEY, SESSION_STAMP etc.
- v$backup_set:- displays information about backup sets from the control file. A backup set record is inserted after the backup set is successfully completed.
- v$flashback_database_log:- V$FLASHBACK_DATABASE_LOG displays information about the flashback data. Use this view to help estimate the amount of flashback space required for the current workload.
- v$flashback_database_logfile:- It displays information about the flashback log files.
- v$archive:-
- v$archived_log:-It plays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared).
- v$log:- It displays log file information from the control file.
- v$logfile:- This view contains information about redo log files. like GROUP#, STATUS, TYPE etc.
- v$controlfile:- This view lists the names of the control files. STATUS, NAME, IS_RECOVERY_DEST_FILE.
- Dba_data_files:- It describes database files.
- dba_free_space:-It describes the free extents in all tablespaces in the database.
- v$datafile:- This view contains datafile information from the control file.
- v$datafile_header:- This view displays datafile information from the datafile headers.
- v$tempfile: – This explains all information about tempfile.
- v$segstat :- It displays information about segment-level statistics
- v$recovery_file_dest:- It displays information about the disk quota and current disk usage in the flash recovery area.
- v$flash_recovery_area_usage:- It displays usage information about flashback recovery areas.
- v$diag_info:-It describes the state of Automatic Diagnostic Repository (ADR) functionality using NAME=VALUE pairs.
- v$pwfile_users:- This view lists users who have been granted SYSDBA and SYSOPER privileges as derived from the password file.
- v$database:-It displays information about the database from the control file.
- gv$*:- In Oracle Real Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of data type NUMBER.
- v$instance:- displays the state of the current instance.
- v$session:- This view lists session information for each current session.
- v$pgastat:- This provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.
- v$lock:- This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.
- v$mystat:- It displays user session statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), query the V$STATNAME view.
- v$process:- V$PROCESS displays information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.
- v$waitstat:- It displays block contention statistics. This table is only updated when timed statistics are enabled.
- v$bgprocess:– V$BGPROCESS displays information about the background processes.
- v$sga:- It displays summary information about the system global area (SGA).
- v$librarycache:- V$LIBRARYCACHE contains statistics about library cache performance and activity.
- v$sql:- V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution.
- v$sqlarea:- V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
- v$rowcache:-V$ROWCACHE displays statistics for data dictionary activity. Each row contains statistics for one data dictionary cache.
- v$sgastat:- V$SGASTAT displays detailed information on the system global area (SGA).
- v$sysstat:- V$SYSSTAT displays system statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), query the V$STATNAME view.
- v$sesstat:- This view lists user session statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), query the V$STATNAME view.
