Parm Patram

contents too big for a post see file http://oracle.3dub.com/vdollar.txt

The virtual tables are stored in memory and start with V$ and there are a bunch of them as follows:

select VIEW_NAME from ALL_VIEWS WHERE (view_name like ‘V_$%’ or view_name like ‘V$%’) order by view_name;

V$OBJECT_USAGE
V$XS_SESSION
V$XS_SESSION_ATTRIBUTE
V$XS_SESSION_ROLE
V$ACCESS
V$ACTIVE_INSTANCES
V$ACTIVE_SERVICES
V$ACTIVE_SESSION_HISTORY
V$ACTIVE_SESS_POOL_MTH
V$ADVISOR_PROGRESS
V$ALERT_TYPES
V$AQ
V$AQ1
V$ARCHIVE
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$ARCHIVE_DEST_STATUS
V$ARCHIVE_GAP
V$ARCHIVE_PROCESSES
V$ASM_ALIAS
V$ASM_ATTRIBUTE
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_FILE
V$ASM_OPERATION
V$ASM_TEMPLATE
V$AW_AGGREGATE_OP
V$AW_ALLOCATE_OP
V$AW_CALC
V$AW_LONGOPS
V$AW_OLAP
V$AW_SESSION_INFO
V$BACKUP
V$BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_ASYNC_IO
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_COPY_DETAILS
V$BACKUP_COPY_SUMMARY
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DATAFILE_DETAILS
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_DEVICE
V$BACKUP_FILES
V$BACKUP_PIECE
V$BACKUP_PIECE_DETAILS
V$BACKUP_REDOLOG
V$BACKUP_SET
V$BACKUP_SET_DETAILS
V$BACKUP_SET_SUMMARY
V$BACKUP_SPFILE
V$BACKUP_SPFILE_DETAILS
V$BACKUP_SPFILE_SUMMARY
V$BACKUP_SYNC_IO
V$BGPROCESS
V$BH
V$BLOCKING_QUIESCE
V$BLOCK_CHANGE_TRACKING
V$BSP
V$BUFFERED_PUBLISHERS
V$BUFFERED_QUEUES
V$BUFFERED_SUBSCRIBERS
V$BUFFER_POOL
V$BUFFER_POOL_STATISTICS
V$CALLTAG
V$CIRCUIT
V$CLASS_CACHE_TRANSFER
V$CLASS_PING
V$CLIENT_RESULT_CACHE_STATS
V$CLIENT_STATS
V$CLUSTER_INTERCONNECTS
V$CONFIGURED_INTERCONNECTS
V$CONTEXT
V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION
V$COPY_CORRUPTION
V$CORRUPT_XID_LIST
V$CPOOL_CC_INFO
V$CPOOL_CC_STATS
V$CPOOL_STATS
V$CR_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER
V$DATABASE
V$DATABASE_BLOCK_CORRUPTION
V$DATABASE_INCARNATION
V$DATAFILE
V$DATAFILE_COPY
V$DATAFILE_HEADER
V$DATAGUARD_CONFIG
V$DATAGUARD_STATS
V$DATAGUARD_STATUS
V$DATAPUMP_JOB
V$DATAPUMP_SESSION
V$DBFILE
V$DBLINK
V$DB_CACHE_ADVICE
V$DB_OBJECT_CACHE
V$DB_PIPES
V$DB_TRANSPORTABLE_PLATFORM
V$DELETED_OBJECT
V$DETACHED_SESSION
V$DIAG_INFO
V$DISPATCHER
V$DISPATCHER_CONFIG
V$DISPATCHER_RATE
V$DLM_ALL_LOCKS
V$DLM_CONVERT_LOCAL
V$DLM_CONVERT_REMOTE
V$DLM_LATCH
V$DLM_LOCKS
V$DLM_MISC
V$DLM_RESS
V$DLM_TRAFFIC_CONTROLLER
V$DNFS_CHANNELS
V$DNFS_FILES
V$DNFS_SERVERS
V$DNFS_STATS
V$DYNAMIC_REMASTER_STATS
V$ENABLEDPRIVS
V$ENCRYPTED_TABLESPACES
V$ENCRYPTION_WALLET
V$ENQUEUE_LOCK
V$ENQUEUE_STAT
V$ENQUEUE_STATISTICS
V$EVENTMETRIC
V$EVENT_HISTOGRAM
V$EVENT_NAME
V$EXECUTION
V$FAST_START_SERVERS
V$FAST_START_TRANSACTIONS
V$FILEMETRIC
V$FILEMETRIC_HISTORY
V$FILESPACE_USAGE
V$FILESTAT
V$FILE_CACHE_TRANSFER
V$FILE_HISTOGRAM
V$FILE_PING
V$FIXED_TABLE
V$FIXED_VIEW_DEFINITION
V$FLASHBACK_DATABASE_LOG
V$FLASHBACK_DATABASE_LOGFILE
V$FLASHBACK_DATABASE_STAT
V$FLASHBACK_TXN_GRAPH
V$FLASHBACK_TXN_MODS
V$FLASH_RECOVERY_AREA_USAGE
V$FOREIGN_ARCHIVED_LOG
V$FS_FAILOVER_HISTOGRAM
V$FS_FAILOVER_STATS
V$GCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GC_ELEMENT
V$GC_ELEMENTS_W_COLLISIONS
V$GES_BLOCKING_ENQUEUE
V$GES_ENQUEUE
V$GLOBALCONTEXT
V$GLOBAL_BLOCKED_LOCKS
V$GLOBAL_TRANSACTION
V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_FINDING
V$HM_INFO
V$HM_RECOMMENDATION
V$HM_RUN
V$HS_AGENT
V$HS_PARAMETER
V$HS_SESSION
V$HVMASTER_INFO
V$INCMETER_CONFIG
V$INCMETER_INFO
V$INCMETER_SUMMARY
V$INDEXED_FIXED_COLUMN
V$INSTANCE
V$INSTANCE_CACHE_TRANSFER
V$INSTANCE_LOG_GROUP
V$INSTANCE_RECOVERY
V$IOFUNCMETRIC
V$IOFUNCMETRIC_HISTORY
V$IOSTAT_CONSUMER_GROUP
V$IOSTAT_FILE
V$IOSTAT_FUNCTION
V$IOSTAT_NETWORK
V$IO_CALIBRATION_STATUS
V$IR_FAILURE
V$IR_FAILURE_SET
V$IR_MANUAL_CHECKLIST
V$IR_REPAIR
V$JAVAPOOL
V$JAVA_LIBRARY_CACHE_MEMORY
V$JAVA_POOL_ADVICE
V$KCCDI
V$KCCFE
V$LATCH
V$LATCHHOLDER
V$LATCHNAME
V$LATCH_CHILDREN
V$LATCH_MISSES
V$LATCH_PARENT
V$LIBRARYCACHE
V$LIBRARY_CACHE_MEMORY
V$LICENSE
V$LOADISTAT
V$LOADPSTAT
V$LOBSTAT
V$LOCK
V$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS
V$LOCK_ACTIVITY
V$LOCK_ELEMENT
V$LOCK_TYPE
V$LOG
V$LOGFILE
V$LOGHIST
V$LOGMNR_CALLBACK
V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_DICTIONARY_LOAD
V$LOGMNR_LATCH
V$LOGMNR_LOGFILE
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS
V$LOGMNR_REGION
V$LOGMNR_SESSION
V$LOGMNR_STATS
V$LOGMNR_TRANSACTION
V$LOGSTDBY
V$LOGSTDBY_PROCESS
V$LOGSTDBY_PROGRESS
V$LOGSTDBY_STATE
V$LOGSTDBY_STATS
V$LOGSTDBY_TRANSACTION
V$LOG_HISTORY
V$MANAGED_STANDBY
V$MAP_COMP_LIST
V$MAP_ELEMENT
V$MAP_EXT_ELEMENT
V$MAP_FILE
V$MAP_FILE_EXTENT
V$MAP_FILE_IO_STACK
V$MAP_LIBRARY
V$MAP_SUBELEMENT
V$MAX_ACTIVE_SESS_TARGET_MTH
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE
V$METRIC
V$METRICGROUP
V$METRICNAME
V$METRIC_HISTORY
V$MTTR_TARGET_ADVICE
V$MUTEX_SLEEP
V$MUTEX_SLEEP_HISTORY
V$MVREFRESH
V$MYSTAT
V$NFS_CLIENTS
V$NFS_LOCKS
V$NFS_OPEN_FILES
V$NLS_PARAMETERS
V$NLS_VALID_VALUES
V$OBJECT_DEPENDENCY
V$OBJECT_PRIVILEGE
V$OBSOLETE_PARAMETER
V$OFFLINE_RANGE
V$OPEN_CURSOR
V$OPTION
V$OSSTAT
V$PARALLEL_DEGREE_LIMIT_MTH
V$PARAMETER
V$PARAMETER2
V$PARAMETER_VALID_VALUES
V$PERSISTENT_PUBLISHERS
V$PERSISTENT_QUEUES
V$PERSISTENT_SUBSCRIBERS
V$PGASTAT
V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$PQ_SESSTAT
V$PQ_SLAVE
V$PQ_SYSSTAT
V$PQ_TQSTAT
V$PROCESS
V$PROCESS_GROUP
V$PROCESS_MEMORY
V$PROCESS_MEMORY_DETAIL
V$PROCESS_MEMORY_DETAIL_PROG
V$PROPAGATION_RECEIVER
V$PROPAGATION_SENDER
V$PROXY_ARCHIVEDLOG
V$PROXY_ARCHIVELOG_DETAILS
V$PROXY_ARCHIVELOG_SUMMARY
V$PROXY_COPY_DETAILS
V$PROXY_COPY_SUMMARY
V$PROXY_DATAFILE
V$PWFILE_USERS
V$PX_BUFFER_ADVICE
V$PX_INSTANCE_GROUP
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT
V$PX_SESSION
V$PX_SESSTAT
V$QUEUE
V$QUEUEING_MTH
V$RECOVERY_FILE_DEST
V$RECOVERY_FILE_STATUS
V$RECOVERY_LOG
V$RECOVERY_PROGRESS
V$RECOVERY_STATUS
V$RECOVER_FILE
V$REDO_DEST_RESP_HISTOGRAM
V$REPLPROP
V$REPLQUEUE
V$REQDIST
V$RESERVED_WORDS
V$RESOURCE
V$RESOURCE_LIMIT
V$RESTORE_POINT
V$RESULT_CACHE_DEPENDENCY
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_STATISTICS
V$RESUMABLE
V$RFS_THREAD
V$RMAN_BACKUP_JOB_DETAILS
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_TYPE
V$RMAN_COMPRESSION_ALGORITHM
V$RMAN_CONFIGURATION
V$RMAN_ENCRYPTION_ALGORITHMS
V$RMAN_OUTPUT
V$RMAN_STATUS
V$ROLLNAME
V$ROLLSTAT
V$ROWCACHE
V$ROWCACHE_PARENT
V$ROWCACHE_SUBORDINATE
V$RSRCMGRMETRIC
V$RSRCMGRMETRIC_HISTORY
V$RSRC_CONSUMER_GROUP
V$RSRC_CONSUMER_GROUP_CPU_MTH
V$RSRC_CONS_GROUP_HISTORY
V$RSRC_PLAN
V$RSRC_PLAN_CPU_MTH
V$RSRC_PLAN_HISTORY
V$RSRC_SESSION_INFO
V$RULE
V$RULE_SET
V$RULE_SET_AGGREGATE_STATS
V$SCHEDULER_RUNNING_JOBS
V$SECUREFILE_TIMER
V$SEGMENT_STATISTICS
V$SEGSTAT
V$SEGSTAT_NAME
V$SERVICEMETRIC
V$SERVICEMETRIC_HISTORY
V$SERVICES
V$SERVICE_EVENT
V$SERVICE_STATS
V$SERVICE_WAIT_CLASS
V$SERVMOD_ACT_STATS
V$SESSION
V$SESSION_CONNECT_INFO
V$SESSION_CURSOR_CACHE
V$SESSION_EVENT
V$SESSION_FIX_CONTROL
V$SESSION_LONGOPS
V$SESSION_OBJECT_CACHE
V$SESSION_WAIT
V$SESSION_WAIT_CLASS
V$SESSION_WAIT_HISTORY
V$SESSMETRIC
V$SESSTAT
V$SESS_IO
V$SESS_TIME_MODEL
V$SES_OPTIMIZER_ENV
V$SGA
V$SGAINFO
V$SGASTAT
V$SGA_CURRENT_RESIZE_OPS
V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_FREE_MEMORY
V$SGA_RESIZE_OPS
V$SGA_TARGET_ADVICE
V$SHARED_POOL_ADVICE
V$SHARED_POOL_RESERVED
V$SHARED_SERVER
V$SHARED_SERVER_MONITOR
V$SORT_SEGMENT
V$SORT_USAGE
V$SPPARAMETER
V$SQL
V$SQLAREA
V$SQLAREA_PLAN_HASH
V$SQLFN_ARG_METADATA
V$SQLFN_METADATA
V$SQLSTATS
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQL_BIND_CAPTURE
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITY
V$SQL_CS_STATISTICS
V$SQL_CURSOR
V$SQL_FEATURE
V$SQL_FEATURE_DEPENDENCY
V$SQL_FEATURE_HIERARCHY
V$SQL_HINT
V$SQL_JOIN_FILTER
V$SQL_MONITOR
V$SQL_OPTIMIZER_ENV
V$SQL_PLAN
V$SQL_PLAN_MONITOR
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_REDIRECTION
V$SQL_SHARED_CURSOR
V$SQL_SHARED_MEMORY
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$SQL_WORKAREA_HISTOGRAM
V$SSCR_SESSIONS
V$STANDBY_APPLY_SNAPSHOT
V$STANDBY_LOG
V$STATISTICS_LEVEL
V$STATNAME
V$STREAMS_APPLY_COORDINATOR
V$STREAMS_APPLY_READER
V$STREAMS_APPLY_SERVER
V$STREAMS_CAPTURE
V$STREAMS_MESSAGE_TRACKING
V$STREAMS_POOL_ADVICE
V$STREAMS_TRANSACTION
V$SUBCACHE
V$SUBSCR_REGISTRATION_STATS
V$SYSAUX_OCCUPANTS
V$SYSMETRIC
V$SYSMETRIC_HISTORY
V$SYSMETRIC_SUMMARY
V$SYSSTAT
V$SYSTEM_CURSOR_CACHE
V$SYSTEM_EVENT
V$SYSTEM_FIX_CONTROL
V$SYSTEM_PARAMETER
V$SYSTEM_PARAMETER2
V$SYSTEM_WAIT_CLASS
V$SYS_OPTIMIZER_ENV
V$SYS_TIME_MODEL
V$TABLESPACE
V$TEMPFILE
V$TEMPORARY_LOBS
V$TEMPSTAT
V$TEMP_CACHE_TRANSFER
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_PING
V$TEMP_SPACE_HEADER
V$THREAD
V$THRESHOLD_TYPES
V$TIMER
V$TIMEZONE_FILE
V$TIMEZONE_NAMES
V$TRANSACTION
V$TRANSACTION_ENQUEUE
V$TRANSPORTABLE_PLATFORM
V$TSM_SESSIONS
V$TYPE_SIZE
V$UNDOSTAT
V$UNUSABLE_BACKUPFILE_DETAILS
V$VERSION
V$VPD_POLICY
V$WAITCLASSMETRIC
V$WAITCLASSMETRIC_HISTORY
V$WAITSTAT
V$WAIT_CHAINS
V$WALLET
V$WORKLOAD_REPLAY_THREAD
V$XML_AUDIT_TRAIL
V$_LOCK

How to use the data dictionary

The data dictionary is perhaps the equivalent of the ‘mysql’ database — it stores specific information about the logical and physical layout of the database, users in that database, perms, etc. It is automatically created and updated by oracle. It is readonly to all users — only oracle can write to it (similar to /proc filesystem in Linux).

A schema is a collection of database objects. It is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly relate to the data in the database. There is NO relation between tablespace and schema.

Schema Objects:

1. Tables
   - contains rows and colums (just like mysql).
2. Indexes
   - just like mysql indexes — speeds up searches.
3. Views
   - can be thought of as stored query. You can do most operations (select, insert..) on a view. If the view is ‘updateable’ then the operations affect the actual data used to create the view.
4. Clusters
   - groups of one or more tables that are stored together because they share common columns.
5. Synonyms
   - a user-defined name or any ‘nameable’ object in the database. Do not actually take up storage except in the ‘data dictionary’.

1. Blocks
   - similar to a filesystem block. Controllable via the DB_BLOCK_SIZE initialization parameter.
2. Extents
   - a certain number of contiguous blocks used to store the same piece of data.
3. Segments
   - collection of extents. Some of the extents making up the segment may belong to different physical datafiles, but are all part of the same tablespace (below).
4. Tablespaces
   - each database is divided into logical storage units called ‘tablespaces’. The default is to have many small files (smallfile tablespace) to store the tablespace data, but you can also create ‘bigfile’ tablespaces so all of the tablespace data is stored in a single LARGE file.
   - a tablespace can be ‘readonly’ or taken offline independently of the rest of the database.

These are the files (7) that make up the physical database:

1. Datafiles
   - a datafile can only be associated with one database
   - datafiles can be defined to ‘grow’
   - one or more datafiles form a logical unit of database storage called a ‘tablespace’.
2. Control Files
   - the control files store the locations of the datafiles and redo log files
   - also stores the database name and time of creation.
   - oracle can ‘multiplex’ the control files (keep many copies) to safeguard their contents.
3. Online Redo Log Files
   - ‘transaction log’ — keeps track of all changes to the data (kept in memory)
4. Archived Redo Log Files
   - once the online redo logs are written to disk they are now ‘archived’. The parameter ARCHIVELOG is how oracle will automatically archive the redo logs. You can store the archives on separate drives for protection.
5. Parameter Files
   - pfiles (parameter) and spfiles (server parameters) control the config options for the database and instance.
6. Alert and Trace Log Files
   - alert files = errors and warnings. Trace = used by background processes to dump diagnostic data.
7. Backup Files