Data Dictionary Tables and Views

Some important VIEWS that we frequently query in Oracle database as a DBA.

When DBA_.. then Datadictionary Table and V$..then it is Datadictionary View. 

DATABASE_PROPERTIES - List most of the mejor properties of any database

DBA_TABLESPACES - Complete information about tablespaces.

DBA_DATA_FILES - Complete information of the files present in the Tablespaces.

DBA_TEMP_FILES - File's information of temporary tablespace.

DBA_EXTENTS - To check the extents for a given segment.

DBA_SEGMENTS - View to get number of extents and blocks allocated to a Segment.

DBA_FREE_SPACE - Displays free extents in tablespace.

DBA_TABLES - All the information about tables can be obtained here.

DBA_OBJECTS - All the information about objects in table can be Obtained here.

DBA_INDEXES - Provides information about the indexes.

DBA_IND_COLUMNS - Provides information about the indexed columns.

V$OBJECT_USAGE - Provides information on the usage of Indexes and Tables.

DBA_CONSTRAINTS - To obtain name, type and status of all constraints.

DBA_CONS_COLUMNS- To obtain the columns in the constraints on table.

DBA_USERS - To obtain information about account status, default Tablespace for users.

DBA_PROPERTIES - View to display passwd profile information.

DBA_TS_QUOTAS - Amount of space a user can use in tablespaces.

DBA_SYS_PRIVS - Lists system privileges granted to users and roles.

V$SESSION_PRIVS - Lists the privileges that are currently available to user.

DBA_TAB_PRIVS - Lists all grants and owners of all Tables in the database.

DBA_COL_PRIVS - Lists all grants and owners of all columns in the database.

DBA_ROLES - All roles that exist in the database.

DBA_ROLES_PRIVS - Roles granted to users and roles.

DBA_SYS_PRIVS   - System privileges granted to users and roles.

DBA_UNUSED_COL_TABS - To identify tables with unused columns.

DBA_PARTIAL_DROP_TABS - To identify tables that have partially Completed DROP columns operations.

DBA_ROLLBACK_SEGS - To obtain information about all the undo segments In the database.

Information about undo segments that are offline can be seen only in this view.

V$CONTROLFILE - Lists the names and status of the control files.

V$DATABASE - Contains database information from the control files.

V$VERSION - Version numbers of core library components in oracle server.

V$INSTANCE - Displays the state of the current instance.

V$DATAFILE - Contains data file information from control file.

V$TABLESPACE - Displays tablespace information from the control file.

V$LOGFILE - Displays each redo log group, member and status of each Member.

V$LOG - Same as above.

V$THREAD - To display the current redo log group, the number of online redo log groups and current sequence number.

V$PARAMETER - Lists parameters and values currently in effect for the Session also status and location of all parameters.

V$SESSION - Lists session information for each current session.

V$SPPARAMETER - Lists the contents of SPFILE.

V$SGA - Contains summary information on SGA.

V$FIXED_TABLE - To find list of data dictionary views.

V$ROLLSTAT - Views to obtain the statistics of the undo Segments currently used by the instance.

V$ROLLNAME - same as above

V$ROLE_SYS_PRIVS - System privileges granted to roles.

V$ROLE_TAB_PRIVS - Object privileges granted to roles.

V$SESSION_ROLES  - Roles that the user currently has enabled.

V$ROLE_ROL_PRIVS - Roles that are granted to roles.

PL/SQL Objects

Anonymous Block: A block of PL/SQL code that is not stored in the database, but instead is embedded in a form, web page, or SQL script.
Procedure: A block of PL/SQL code that is stored in the database and performs a specific action.
Function: A block of PL/SQL code that is stored in the database and returns a value when called in a SQL statement.
Package: A collection of related procedures and/or functions that perform related functions.
Trigger: A block of PL/SQL code that runs whenever an INSERT, UPDATE, or DELETE activity occurs on a table. Can also be defined to run when certain database events occur.

Datablock, Extent and Segment

A segment is defined as any entity that consumes physical storage space within the database.

Each Oracle segment is made up of contiguous chunks of storage space in the database called extents. Every segment must have at least one extent, but can have as many as 2 billion extents.

Each extent is itself made up of a collection of smaller chunks of space called Oracle database blocks. The minimum size of an extent is five database blocks.

The default size of these database blocks is set at database creation, but Oracle 10g db can use multiple block sizes within one db. The common database block sizes are 2KB, 4KB, 8KB, and 16KB.

Each database block is in turn composed of one or more operating system blocks.

SQL Limitations

-SQL does not have very good mechanisms for condition testing, which would allow a SQL statement to execute if a given condition is true, but not execute if the conditionis false.
-SQL also lacks looping capabilities, the ability to perform a specific SQL action for a specified number of times before stopping.
-Finally, SQL does not offer any exception-handling capabilities;
-all errors raised by SQL statements are returned directly to the user.

Background processes

DBWN:

Writes changed datablocks from the database buffer cache back to the datafiles. Modified blocks not yet written to the datafiles are called dirty datablocks . If multiple database writers are configured (by setting initialization parameters), each process is given the name DBWn , where n is either an integer from 0 to 9 or a letter from a to j. The maximum number of database writers allowed in a single Oracle instance is 20.

The DBWn background process writes to the datafiles whenever one of the following events occurs:

--A user’s Server Process has searched too long for a free buffer when reading a buffer into the Buffer Cache.
--The number of modified and committed, but unwritten, buffers in the Database Buffer Cache is too large.
--At a database Checkpoint event. See Chapters 10 and 11 for information on checkpoints.
--The instance is shut down using any method other than a shutdown abort.
--A tablespace is placed into backup mode.
--A tablespace is taken offline to make it unavailable or changed to READ ONLY.
--A segment is dropped.

Log writer (LGWR):

Log writer Transfers data from the redo log buffer cache to the redo log files. A database has a set number of redo log files, which work cyclically. As one log file becomes full, the LGWR will switch to a new, empty log file. Once the LGWR has filled the last redo log file, it will switch back to the first log file and begin reusing them. The LGWR records redo log files frequently, when the following events occur:

• Every three seconds.
• Every time a user commits a transaction.
• When the redo log buffer becomes one-third full.
• When the redo log buffer reaches 1 MB.

Checkpoint process (CKPT):

Synchronizes all database files. Every change made to the data is given a System Change Number (SCN). CKPT updates the headers of all datafiles, control files, and redo logs with the latest SCN and a date and time stamp. In the case of failure, recovery brings all database files up to date by tracking the SCNs in the file headers.

Process monitor (PMON):
Tracks all the other background processes. If a user process fails, PMON handles the cleanup by deallocating memory back to the SGA.

System monitor (SMON):
Handles instance recovery on instance startup, cleans up temporary segments, and consolidates free space in the datafiles.

Dirty datablocks

Writes changed datablocks from the database buffer cache back to the datafiles. Modified blocks not yet written to the datafiles are called dirty datablocks .

PGA

A program global area (PGA) is a memory region that contains data and control information for a server process. It is a nonshared memory created by Oracle when a server process is started.

SGA

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:

DB_CACHE_SIZE - The size of the cache of standard blocks.
LOG_BUFFER - The number of bytes allocated for the redo log buffer.
SHARED_POOL_SIZE - The size in bytes of the area devoted to shared SQL and PL/SQL statements.
LARGE_POOL_SIZE - The size of the large pool; the default is 0.
JAVA_POOL_SIZE - The size of the Java pool.

Shared pool:
The shared pool contains shared memory constructs, such as shared SQL areas, cached SQL and PL/SQL statements. It is called the shared pool because its contents are shared by all database users. The shared pool contains the library cache, the dictionary cache, and an area for control structures and character sets.

Java pool:
Optional SGA component that caches the most recently used Java objects and application code.

Large pool:
Optional SGA component that caches data for large operations, such as Shared Server or Parallel Execution.

Streams pool:
Optional SGA component that caches data from queued message requests.

Dictionary:
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.
The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache , also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).
The other area in memory to hold dictionary data is the library cache.
All Oracle user processes share these two caches for access to data dictionary information.


Library cache:
Acts as a warehouse for database commands. When a user issues a command, Oracle looks in the library cache to see if a duplicate statement exists. If it does, the new statement is discarded, and the stored statement is used instead. This approach saves processing time.

Supporting Files

Supporting files are not actually part of the database, but are important to its operation.

There are different Supporting Files:

Parameter file:
Parameter file contains a list of initialization parameters that control how certain aspects of the database instance behave. Most parameters can be modified without restarting the instance, while some require that it be shut down and restarted to pick up the new values. Also known as the initialization file.
When the Oracle database is started, one of the first things it needs to do is read the database initialization parameter file. The parameter file (init.ora) is created by the DBA and defines the overall instance configuration, such as how much memory should be allocated to the instance, the file locations, and internal optimization parameters.

Password file:
Password file Stores passwords in an encrypted format for certain privileged users who are allowed to start and stop the database. Normally, when users connect to the database, their privileges are verified from within the database itself. Since the database is unavailable when it is not running, a privileged user who is starting the database must be authenticated by some other means. The password file provides this capability by storing the privileged user’s password outside of the database.


Alert log:
Alert log Records all internal errors and information about several database operations. The DBA should check the alert log at least once a day. The alert log entry briefly describes an error and provides the name of a trace file , which contains more detailed information regarding the internal errors.

Control File

Every Oracle database has a control file containing the operating system filenames of all other files that constitute the database. The control files record crucial information such as -

1. Database name,
2. Timestamp of database creation,
3. Names of the database's Datafiles and online and archived redo log files,
4. Datafile locations,
5. Checkpoint, a record indicating the point in the redo log where all database changes prior to this point have been saved in the datafiles
6. Synchronization information, and
7. Recovery Manager(RMAN) backup meta-data

Without these files, the database cannot start up. If anything happens to the control files while the database is running, the database will crash. If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle to reflect the change.

Oracle (or Oracle users can) multiplex the control file, allowing Oracle to write multiple copies of the control file to protect it against disaster or failure.

If the operating system supports disk mirroring, the control file can also be mirrored, allowing the O/S to write a copy of the control file to multiple disks. Every time a user mounts an Oracle database, its control file is used to identify the datafiles and online redo log files that must be opened for database operation.
The control file should be backed up whenever the structure of the database changes. Structural changes can include adding, dropping, or altering datafiles or tablespaces and adding or dropping online redo logs.

Redo log file

Redo log files record all changes made to the data. Every Oracle database has a set of two or more redo log files . The set of redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records ).In the event of file corruption or disk failure, the DBA can restore datafiles from a backup copy, and then apply the changes listed in the redo log to bring the backup datafiles up to date.

Redo log contains one or more copies of itself in case one of the copies becomes corrupt or is lost due to a hardware failure. Collectively, these sets of redo logs are referred to as redo log groups. Each multiplexed file within the group is called a redo log group member.

Each database must have a minimum of two redo log groups because redo logs are used in a circular fashion.

Datafiles

ADD Datafile to Tablespace:

ALTER TABLESPACE DATA02
ADD DATAFILE '/oradata1/THRP/DATA02_02.dbf'
SIZE 100M;

ALTER TABLESPACE STATE_DATA01
add datafile '/data/state_datafiles/STATE_DATA01_01.dbf'
size 1G AUTOEXTEND ON NEXT 100M;


ALTER Datafile:
ALTER database datafile '/oradata1/THRP/DATA02_02.dbf' AUTOEXTEND ON MAXSIZE 20G;
alter database datafile '/oradata1/THRP/DATA02_02.dbf' AUTOEXTEND ON NEXT 50M;

The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database. Every Oracle database has one or more physical datafiles.

The datafile is divided into smaller units called data blocks. The data of logical database structures, such as tables and indexes, is physically located in the blocks of the datafiles allocated for a database.

Datafiles hold the following characteristics:

• One or more physical datafiles form a logical database storage unit called a tablespace.
• Datafiles can have certain characteristics set to let them automatically extend when the database runs out of space.

The first block of every datafile is the header. The header includes important information such as file size, block size, tablespace, and creation timestamp. Whenever the database is opened, Oracle checks to see that the datafile header information matches the information stored in the control file. If it does not, then recovery is necessary. Oracle reads the data in a datafile during normal operation and stores it in the buffer cache.

For example, assume that a user wants to access some data in a table. If the requested information is not already in the buffer/memory cache, Oracle reads it from the appropriate datafiles and stores it in memory.

Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the database writer process (DBWn) background process.

Oracle Architecture

Oracle database:
1.Physical database:
----i.Data file
----ii.Redo log file
----iii.Control file
2.Instance:
---- i.Large memory area:
-------- a. System Global Area (SGA):
-------------------- 1. Database buffer cache
-------------------- 2. Redo log buffer
-------------------- 3. Shared pool
-------------------- 4. Java pool
-------------------- 5. Large pool (optional)
-------------------- 6. Streams pool
-------------------- 7. Dictionary cache:
---------------------------- Data Dictionary cache
---------------------------- Library cache
-------------------- 8. Other miscellaneous information
-------- b. Program Global Area (PGA)
-------- c. Optional Area – Software Area Code
---- ii.Set of background processes:
-------- a. Database Writer Process (DBWn)
-------- b. Log Writer Process (LGWR)
-------- c. Checkpoint Process (CKPT)
-------- d. System Monitor Process (SMON)
-------- e. Process Monitor Process (PMON)
-------- f. Recoverer Process (RECO)
-------- g. Job Queue Processes
-------- h. Archiver Processes (ARCn)
-------- i. Queue Monitor Processes (QMNn)
-------- j. Other Background Processes