Questions

Answers

What is a Database ?

A Database can be one of the two definitions:


·A set of dictionary tables and user tables that are treated as a unit.
·One or more operating system files in which ORACLE stores the tables,views,and other objects:also, the set of database objects used bya given application.
·A database is a collection of interrelated data that are to be stored in a single location. It enables sharing of data among various users as and when required.

What is a Database system ?

Database system is a combination of an Instance and a Database. If the instance is started and connected to an open database, then the database is available for access by the users.
A DBMS is a software system with capabilities to organise, manipulate and manage the data.
Note:-
A DBMSmust be able to reliably manage a large amountofdatainamulti-userenvironmentsothatmany users can concurrently access the same data.
A DBMS must also be secure from unauthorised access and provides eficient solutions for failure recovery.

What is an RDBMS ?

A relational database Mangement System (RDBMS) is a computer program for general purpose data storage and retrieval that organizes data into tables consisting of one or more units of information (rows), each containing the same set of data items(columns). ORACLE is a relational database management system.

What are the differnt Database models ?

·Hierarchial.·Networking.·Relational.

What is SQL ?

·S.Q.L - Structured Query Language.SQL is the ANSI industry standard language, used to manipulate information in a relationaldatabase and used in ORACLE and IBM DB2 relational database management systems. SQL is formally pronounced “sequel”, although common usage also pronounces it “S.Q.L.”·SQL is a set of commands that all programmers must use to access data within the tables of Database.


What are the benefits of SQL ?

1. It is flexible, Powerful and easy to learn. 2. It is a non-procedural language. It a] Processes set of records rather than just one at a timeandb] Provides automatic navigation to the data. 3. It provides commands for a variety of tasks including :a] Querying datab] Creating,Updating and Replacing objects andInserting,Updating and Deleting rows. 4. All RDBMS supports SQLThus one can transfer the skills gained with SQL from oneRDBMS to another.5. Programs written in SQL are portable, they can often bemoved from one database to another with little modification.

What is SQL*PLUS ?

SQL*PLUS is the ORACLEdatabase language which includes ANSI standard SQL commands plus additional commands for accessing data in ORACLE database.SQL*PLUS is a Structured Query Language supported by Oracle. Through this only, we store, retrieve, edit, enter & run SQL commands and PL/SQL blocks. We can perform calculations , list column definitions, format query reults in the form of a query.

What is PL/SQL ?

It is a Procedural Language extension of SQL. It can contain any no of SQL statements integrated with flow of control statements. Thus it combine the Data Manipulating power of SQL with data processing power of Procedural language.

What are the different types of SQL commands ?

DDL ( Data definition language )DML ( Data manipulation language )TCL( Transact control language)Session Control Statements. ( ALTER SESSION, ROLE )System Control Statements. ( ALTER SYSTEM )

What is A DDL statements?

DDL statements are one catagory of SQL statements. DDL statements define (create) or delete (drop) database objects.Examples are ceate view, create table, create index,drop table and rename tabl. The other catagories are DML statements and DCL statements.

What is a DML statements ?

DML statements are one catagory of SQL statements. DML statements, such as select, insert, delete and update, query and update the actual data. The other catagories are DDL statements and DCL statements.

What are DCL statements ?

DML statements are one catagory of SQL statements. DCLstatments such as, connect, grant select,grant update and revoke dba, control access to the data and to the database. The other catagories are DDL and DML statements.

What is a Transaction ?

·It can be defined as a logical unit of work.·A transaction is a sequence of SQL statements that ORACLE treats as a single unit. The set of statements is made permanentwith theCOMMIT statement. Part or all of a transaction can de undone with the ROLLBACK statement.·All changes to the database between successive COMMITS and / or ROLLBACK operations are called a transaction.

What is a Commit ?

·COMMIT commits any changes made to the database since the last COMMIT was executed implicitly or explicitly. WORK is optional and has no effect on usage.·To COMMIT means to make changes to data (inserts,updates and deletes) permanent. before changes are stored both the old and new data exists so that changes can be made, or so that the data can be restoredto its prior state.(“rollback”). When a user enters the ORACLE SQL Command COMMIT, all changes from that transaction are made permanent.·To end a transaction and make permanent all changes performed in the transaction. This command also erases all Savepoints in the transaction and release the transaction locks

What is a Rollback ?

·A ROLLBACK discards part or all of the work you have done in the current transaction, since the last COMMIT or SAVEPOINT.·To undo work done in current tranaction.

What is locking ?

To lock is to temporarily restrict other user’s access to data. The restriction is placed on such data is called “a lock”. The modes are SHARE, SHARE UPDATE,EXCLUSIVE,ROW SHARE AND ROW EXCLUSIVE. Not all locks can be acquired in all modes. EXCLUSIVE locks permit users to query the locked table but not to do anything else. No other user may lock the table. SHARED locks permit concurrent queries but no updates to the locked table. With a ROW SHARE orSHARE UPDATE lock, no users can lock the whole table for exclusive access, allowing concurrent access for all users to the table. The two types of locks are synonymous, and SHARE UPDATE exists for compatibilitywith previous vrsions of ORACLE. ROW EXCLUSIVE locks are similar to ROW SHARE but they prohibit shared locking, so only one user user may access the table at the same time.

What is a Savepoint ?

The Savepointis used to identify the point in a transaction to which you can later Rollback.

What is SHARE LOCK ?

A SHARE lock is one that permits other users to query data, but not to change it.

What is SHARE UPDATE LOCK ?

A SHARE UPDATE lock is one that permits other users to both query and lock data.

What is EXCLUSIVE LOCK ?

An EXCLUSIVE LOCK is one that permits other users to query data, but not to change it. It differs from the SHARE lock because it does not permit another user to place any type of lock on the same data; several users may place SHARE locks on the same data at the same time.

What is a ROW SHARE LOCK ?

With a ROW SHARE orSHARE UPDATE lock, no users can lock the whole table for exclusive access, allowing concurrent access for all users to the table.

What is a ROW EXCLUSIVE LOCK ?

ROW EXCLUSIVE locks are similar to ROW SHARE but they prohibit shared locking, so only one user may access the table at the same time.

What is a DEAD LOCK ?

A DEAD lockisararesituationinwhichtwoormore user processes of a database cannot complete theirtransactions.This occurs because each process is holdinga resource that the other process requires(such as a row in a table)in order to complete.Although these situations occur rarely, ORACLE detects and resolves deadlocks by rolling back the work of one of the processes.

What are INTEGRITY CONSTRAINTS ?

INTEGRITY CONSTRAINT is a rule that restricts the range of valid values for a column, it is placed on a column when the table is created.

What is REFERENTIAL INTEGRITY ?

REFERENTIAL INTEGRITY is the property that guarantees that values from one column depend on values from another column. This property is enforced through integrity constraints.

What is a PRIMARY KEY ?

The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.

What is a FOREIGN KEY ?

A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.

What is a UNIQUE KEY ?

A UNIQUE KEY is one or more columns that must be unique for each row of the table.

What is the difference between UNIQUE and PRIMARY KEY ?

The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well asNULL value is restricted.

What is a SEQUENCE ?

A SEQUENCE is a database object used to generate UNIQUE INTEGERSfor use as PRIMARY KEYS.

What is a VIEW ?

A View is a database objectthat is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.

What is a SYNONYM ?

A SYNONYMis a name assigned to a table or view that may thereafter be used to refer it. If you access to another user’s table, you may create a synonym for it and refer to it by the synonym alone, without entering the user’s name as a qualifier.

What is a ROWID ?

ROWID is the logical address of a row, and it is unique within the database.The ROWIDis broken into three sections: left,middle,, and right (corresponding to 00001F20,000C, AND 0001, just shown). The numbering is in hexadecimal notation. The left section is the block in the file, the middle is the row sequence number within the block(numbering starts with 0, not 1), and the right is the file number within the database. Note that the file numbers are unique within the whole database. The tablespace they are in is not relevant to the ROWID. ROWIDcan be selected, or used in a where clause, but cannot be changed by an insert, update, or delete. However it can change if the table it is in is exported and imported.

What is INDEX ?

INDEX is a general term for an ORACLE / SQL feature used primarily to speed execution an impose UNIQUENESSupon certain data. INDEX provides a faster access method to one table’s data than doing a full table scan. There are several types of Indexes : UNIQUE INDEX, COMPRESSED INDEX, CONCATENATEDINDEX. An Index has an entry for each value found in the table’s Indexed field(s) ( except those with a NULL value ) and pointer(s) to the rows having that value.

What is an UNIQUE INDEX ?

An UNIQUE INDEXis an index that imposes uniqueness on each value in indexes. The index may be one column or concatenated columns.

What is a COMPRESSED INDEX ?

A COMPRESSED INDEX is an index for which only enough index information is stored to identify unique entries; information that an index stores with the previous or following key is “compressed” (truncated)and not stored to reduce the storage overhead required by an index.

What is CONCATENATED INDEXor KEY?

A CONCATENATED INDEXis one that is created on more than one column of a table. It can be used to guarentee that those columnsare unique for every row in the table and to speed access to rows via those columns

What are CLUSTERS ?

A CLUSTER is a means of storing together data from multiple tables, when the data in those tables contains information and is likely to be accessed concurrently.

What is CLUSTER KEYor CLUSTER COLUMNS ?

A CLUSTER KEY is the column or columns that cluster tables have in common, and which is chosen as the storage / access key. For example two tables, WORKER and WORKERSKILL, might be clustered on the column name. A cluster key is the same thing as a cluster column.

What is CLUSTER INDEX ?

A CLUSTER INDEXis one manually created after a cluster has been created and before any DML ( that is SELECT, INSERT, UPDATE AND DELETE )statements can operate on the cluster. This index is created on the CLUSTER KEY columns with the SQL statement CREATE INDEX. In ORACLE 7, you can define a hash cluster to index on the primary key.

What are EXCEPTIONS ?

Exceptions are the error handling routines of PL/SQL.The EXCEPTION section of a PL/SQL block is where program control is transfered whenever an exception flag is raised. Exception flags are either user-defined or system exceptions raised automatically by PL/SQL.

What are CURSORS ?

Cursor has two definitions :·A cursor is a marker such as a blinking square or line, that marks your current position on a CRT screen.·Cursor is also a synonym for context area - a work area in memory where ORACLE stores the current SQL statement. For a query , the area in memory also includes column headings and one row retrieved by the SELECT statement.

What is NULL?

A NULL value is one that is unknown, irrelevant, or not meaningful. Any ORACLE data type can be NULL. NULL in a number data type is not the same as zero.The default value for a field in ORACLE is NULL.

What is EXPRESSION ?

An expression is any form of a column. This could be a literal, a variable, a  mathematical computation, a function, or virtually any combination of functions and columns whose final result is a single value, such as a string, a number, or a value.

What is a CONDITION ?

A Condition is an expression whose value evaluates to either TRUE or FALSE, such as AGE > 16.

What is a PROFILE ?

A PROFILEis a collection of settings on ORACLE7 that limit database resources.

What are ROLES ?

A ROLE is a set of privileges that an ORACLE7 user can grant to another user or to a role. ORACLE version 6 privileges DBA, CONNECT, AND RESOURCE have become system-supplied roles in ORACLE7, and there are also two new roles for importing and exporting a database. ORACLE has five system-supplied roles : CONNECT,RESOUCE,DBA,EXP_FULL_DATABASE,IMP_FULL_DATABASE.

What is a SEGMENT ?

A SEGMENTis another way to classify the space allocated to a table, index, or cluster. A table has one segment that consists of all of its extents. Every index has one segment similarly defined. A cluster has atleast two segments, one for its data and one for its cluster key index.

What is TABLE SPACEin ORACLE ?

TABLE SPACEis a file or set or files that is used to store ORACLE data. An ORACLE database is composed of the SYSTEM tablespace andpossibly othertablespaces.

What are PCTUSED and PCTFREE parameters ?

PCTFREE is aportion of the data block that is not filled by rows as they are inserted into a table. but is reserved for future updates made to the rows in that block. PCTUSED is the percentage of space in a data block, which ORACLEattempts to fill before it allocates another block.

CLIENT

A Client or Front End database application acts as an interface between the user and the Database. It also checks for validation against the data entered by the user. CLIENT is a general term for a user , software application, or computer that requires the services, data, or processing of another application or computer.

SERVER

A Database server or Back End is used to manage the Database tables optimally among multiple clients who concurrently request the server for the same data. It also enforces data integrity across all client applications and controls database access and other security requirements. SERVER system is the configuration of the ORACLE when a remoe user accesses ORACLE via SQL*NET.

What is a SESSION ?

A SESSION is a sequence of events that happens between the time auser connects to SQL and the timehe or she disconnects.

What is an INSTANCE ?

An INSTANCE is everything required for ORACLE to run: backround processes (programs), memory, and so on. An INSTANCEis the means of accessing a database.

What is a BACKROUND PROCESS ?

A BACKROUND process is one of the processes used by an instance of multiple-process ORACLE to perform and coordinate tasks on behalf of concurrent users of the database. The base process are named ARCH(archiever),DBWR (database writer), LGWR (log writer), PMON (process monitor), and SMON (system monitor), and exists as long as an instance does.

What is a BLOCK in ORACLE ?

Basic unit of storage (physical and logical) for all ORACLE data. The number of blocks allocated per ORACLE table depends on the table space in which the table is created. The ORACLE block size varies by operating system and may differ from the block size of the host operating system.. Common block sizes are 512 bytes (characters) and 2048 bytes. A Block is a logical container for items. It is also a separate object, with its own set of properties. The properties of the block determine how end users interact with the interface items it contains.

What is the use of ROLLBACK segment ?

A ROLLBACK segment is a storage space within a table space that holds transaction information used to guarantee data integrity during a ROLLBACK and used to provide read consistency across multiple transactions.

What is READ CONSISTENCY ?

READ CONSISTENCY is a state that guarentees that all dataencountered by a statement / transaction is a consistent set throughout the duration of the statement / transaction.

What is SGA ?

SGA is a shared storage area in main or virtual memory (depending on your operating system) that is the center of ORACLE activity while the database is running. The size of the SGA ( and perfomance of the system ) depends on the values of the variable init.ora parameters. The SGA provides communication between the user and the backround processes.

What is SYSTEM USERID ? What does it have ?

SYSTEM is one of the DBAusers that is created when the database system is installed and initialized ( the other is SYS ). While SYS owns most of the data dictionary tables, SYSTEM owns the views created on those base tables.

What is SYS USERID ? What does it have ?

SYS is one of the DBAusers that is created when the database system is installed and initialized ( the other is SYSTEM ). SYS owns most of the data dictionary tables, SYSTEM owns the views created on those base tables.

What is a Datadictionaryin ORACLE ?

The DATA DICTIONARY is a comprehensive set of tables and views owned by the DBA users SYS and SYSTEM, which activates when ORACLE is initially installed, and is a cental source of information for the ORACLE RDBMSitself and for all users of ORACLE. The tables are automatically maintained by ORACLE, and holds a set of views and tables containing information about the databaseobjects, users, privileges, events, and use.

What is Sqldba ?

SQL * DBAis an ORACLE utility used by DBAs while performing database maintenance and monitoring.

What are Databasefiles ?

A DATABASE file is simply any file used in a database. A database is made up of one or more tablespaces, which in turn are made up of one or more database files.

What is a Controlfile ? What is its significance ?

A CONTROL file is a small administrative file required by every database, necessary to start and run a database system. A control file is paired with a database, not with an instance. Multiple identical control files are preferred to a single file, for reasons of data security.

What is an INIT file ? What is its significance ?

init.ora is a database system parameter file that contains numerous settings and file names used when a system is started using the CREATE DATABASE , START UP, or SHUT DOWN command.

What does a INSERT statement do ?

INSERT adds one or more new rows to the table or view.

What does an UPDATE statement do ?

Updates (changes) the values in the listed columns in the specified table.

What does a DELETE statement do ?

DELETEdeletes all rows that satisfy condition from table.

What does a SELECTstatement do ?

SELECT retrieves rows from one or more tables( or views or snapshots ), either as a command, or as a subquery in another SQL command (with limitations), including SELECT,INSERT,UPDATE and DELETE. ALL means that all rows satisfying the conditions will be returned ( this is the default ). DISTINCT means that only rows that are unique will be returned: any duplicates will be weeded out first.

What isStartup and Shutdown ?

STARTUP is the process of starting an instance, presumably with the intent of mounting and opening a database in order to make a database system available for use. To SHUTDOWNis to disconnect an instance from the database and terminate the instance.

What is Mounting of database ?

To MOUNTa database is to make it available to the database administrator.

What is Two Phase - Commit ?

ORACLE7 manages distributed transactions with a special feature called TWO PHASE - COMMIT. TWO PHASE - COMMITguarantees that a transaction is valid at all sites by the time it commits or roll back. All sites either commit or rollback together, no matter what errors occur in the network or on the machines tied together by the network. You don’t need to do anything special to have your applications use a TWO PHASE - COMMIT.

What are Snapshots ?

A SNAPSHOTis a means of creatinga local copy of remote data. A snapshot  can be used to replicate all or part of a single table, or to replicate the result of a query against multiple tables. The refreshes of the replicated data can be done automatically by the database ( at time intervals you specify ) or manually.

What are Triggers ?

A DATABASE TRIGGER is a stored procedure associated with a table that ORACLE7 automatically executes on one or more specified events (BEFORE or AFTER an INSERT,UPDATE or DELETE) affecting the table. Triggers can execute for the table as a whole or for each affected row in the table.

What are Packages ?

A PACKAGE is a PL/SQL object that groups PL/SQLtypes, variables, SQL cursors, exceptions,procedures, and functions.Each package has a specification and a body. The specification shows the object you can access when you use the package. The body fully defines all the objects and can contain additional objects used only for the internal workings. You can change the body (for example, by adding procedures to the packages) without invalidating any object that uses the package.

What are Packaged Procedures ?

A PACKAGED PROCEDUREis abuilt-in PL/SQL procedure that is available in all forms. Each packaged procedure executes a SQL*FORMS function, such as moving to a field or executing a query.

What are Restricted Packaged Procedures ?

Any PACKAGED PROCEDUREthat affects the basic functions of SQL*FORMS is a RESRICTED PACKAGED PROCEDURE. You should use restricted packaged procedure only in KEY-TRIGGERS, USER-NAMED TRIGGERS that are invoked by KEY-TRIGGERS, and ON_NEW_FIELD_INSTANCE triggers. You should not use restricted packaged procedures in any of the following types of triggers.·On-error,On-Database-Record,On-delete,On-insert,On-Lock, On-Message,On-New-Record,On-Remove-record,On-Update, On-Validate-Field, and On-validate-Record triggers.·Post-Change triggers.·Pre-and Post- Field, Pre- and Post- Record, Pre- and Post-Block, Pre- and Post-Form triggers.·Pre- and Post-Query triggers.·Pre-and Post-Insert, Pre- and Post-Update, Pre- and Post-Delete, Pre- and Post-Commit triggers.·User-Named triggers that are invoked by any of the above triggers.

What are Unrestricted Packaged Procedures ?

Any PACKAGED PROCEDUREthat does not interface withthe basic functions of SQL*FORMS is an UN- RESRICTED PACKAGED PROCEDURE.You can use unrestricted packaged procedures in any type of trigger. The following list shows the unrestricted packaged procedures: Abort_Query, Anchor_View,Bell, Break, Call, Call_query, Default_Value, Display_Error, Display_field, Display_page, Edit_field, Erase, Execute_Trigger, Help, Hide_Page, Host, Lock_Record, Message, Move_View, Pause, Print, Redisplay,Resize_View, Set_Field, Show_keys, Show_page, Synchronize.

What are Pseudo Columns in ORACLE ?

A PSEUDO COLUMN is a “column”that yields a value when selected, but which is not an actual column of the table. An example is ROWID or SYSDATE.

What is a Schema?

A SCHEMA is a collection of objects. SCHEMA objects are logical structures that directly refer to the database’s data. SCHEMA objects include structures such as tables, views, synonyms, sequences, indexes, clusters, stored procedures and data links.

What are the major aspects of the Relational Database Management System ?

The Relational model has three major aspects:Structures : Structures are well-defined objects that store the data of the database. Structures and the data contained within them can be manipulated by operations.Operations : Operations are clearly defined actions that allow the user to manipulate the data and structure of the database. The operation on a database must adhere to a pre-defined set of integrity rules.Integrity rules : Integrity rules are the laws that govern which operations are allowed on the data and structure ofa database. Integrity rules protect the data and the structures of a database.

What are the benefits of Relational Database Management System ?

RDBMS offers benefits such as : 1] Independence of physical data storage and logicaldatabase structure. 2] variable and easy access to all data. 3] Complete flexibility in database design. 4] Reduced data storage and redundancy.

What is a Database Structure ?

An ORACLEdatabase structure has both a physical and logical structure.Physical database structure : An ORACLEdatabase physical structure is determined by the operating system files that constitute the database. Each ORACLE database is comprised of three types of files: one or more data files, two or more redolog files, and one or more control files. The files ofa database provide the actual physical storage of the database information.Logical database structure: An ORACLEdatabase’s logical structure is determined by ·One or more tablespaces.·The database’s schema objects (e.g. tables, views, indexes, clusters, sequences,and stored procedures ) The logical storage structures,including tablespaces, segments, and extents, dictate how the physical space of a database is used. the schema objects and the relationships among them form the relational design of the database.

What are the LOGICAL STRUCTURES ?

1.Tablespaces: A database is divided into logical storage units called tablespaces. A tablespaces used to group related logical structures together. For example , tablespaces commonly group all of an applications objectssimplify certain administrative operations.2.Databases,Tablespaces and Datafiles:·Each database is logically divided into one or more tablespaces.·One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace·The combined size of a tablespace’s data files is the total storage capacity of the tablespace (SYSTEM has 2MB storage capacity while data has 4MB )·The combined storage capacity of a database’s tablespaces is the total storage capacity of the database. (6MB )

What is On-line and Off-line tablespaces ?

An tablespace can be On-line or Off-line. A tablespace is normally On-line so that users can access the information within the tablespace. A tablespace can be Off-line to make a portion of the database unavailable while allowing normal access to the remainder of the database.

What are Hash clusters ?

Hash clusters are also cluster table data in a manner similar to normal clusters. However, a row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value.

What are Database Links ?

A database link is a named objectthat describes a path from one database to the other. Database links are implicitly used when a reference is made to a global object name in a distributed database.

What are Datablocks ?

At the finest level of granularity, an ORACLE databasedata is stored in datablocks . One datablock corresponds to a specific number of bytes of physical database space on the disk. A datablock size is specified for each ORACLE database when the database is created. A database uses and allocates free database space in ORACLEdatablocks.

What are Extents ?

The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.

What are Segments ?

The level of logical database storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure. The different types of segments include :Data segment : Each non-clustered table has a data segment.All of the tables data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s data segment.Index segment : Each index has an index segment that stores all of its data.Rollback segment : In or more rollback segments are created by the database administrator for a database to temporarily store “undo” information. This information is used to generate read-consistentdatabase information, during database recovery and to rollback uncommitted transactions for users.Temporary segment :Temporary segments are created by ORACLE when a SQL statement needs a temporary work area to complete execution. when the statement finishes execution the temporary segment’s extents are returned to the system for future use.

What is Application Partitioning ?

PL/SQL is the language used for both client-side Oracle forms applications and server-side database triggers and stored procedures and there is a PL/SQl engine in both Oracle forms Runform and the Oracle7 Server. This means that you can take advantage of application patitioning to execute application code on either the client or the server. Application partitioning allows you to optimize performance and resource usage by storing and executing procedures either locally or at the server, which makes the most sense for your particular application and configuration.

Explain the Physicalstructure of the Oracle database ?

The physical structure of an ORACLE database includes datafiles, redolog files and control files.1.Datafiles: Every ORACLE database has one or more physical data files. A database’s data files contains all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database. The characteristics of data files are :A datafile can be associated with only one database, once created, a data file cannot change in size and one or more data files form a logical unit of database storage called a tablespace.Note:Modified or new data is not necessarily written to a data file immediately. To reduce the amount of disk output and increase performance, data is pooled in memory and written to the appropriate data file all at once, as determined by the DBWR backround process of ORACLE.2. Redo log files: Every ORACLE database has a set of two or more Redo logfiles. The set of redo log files for a database is collectively known as the Database’s redolog.The primary function of a redo log is to record changes made to data.Should a failure prevent modified data to be written to the data files , the changes can be obtained from the redo log and the work is never lost. Thus redo log files are critical in protecting a database against failures. The process of applying the redo log during a recovery operation is called Rolling forward. To protect against failures of the redo log itself, ORACLE allows a mirrored redo log so that two or more copies of the redo log can be maintained on different disks.3. Control files: Every ORACLEdatabase has a Control file. A control file records the physical structure of the database. For example, it contains the following information :Database name, names and locations of a database’sdata files and redolog files and the time stamp of database creation.

Every time an instance ofan ORACLE is started, its control file is used to identify database and the redo log files that must be opened for database operation to proceed. If the physical makeup of the database is altered ( for example, if a new data file or redo log file is created ), the database’s control file is automatically modified by the ORACLE to reflect the change. Note:A database’s control file is also used if database recovery is necessary.

Explain the Memory Structures of the Oracle database ?

Oracle creates and uses memeory sructures to complete several jobs. For example, memory is used to store program code being executed and data that is shared among users. Several basic memory structures are associated with Oracle; the system global area. ( which includes the database and redolog buffers, and the shared pool ) and the program global areas.a) System global area:The SGA is a shared memory region allocated by Oracle that data and information for one Oracle instance. An SGA and the Oracle backround processes constitute an Oracle Instance. The SGA is allocated when an instance starts and deallocated when the instance shuts down. Each instance that is started has its own SGA. The data in the SGA is shared among the users currently connected to the database. For optimal performance , the entire SGA should be as large as possible to store as much data as possible in memory and minimise disk I/O. The information stored within the SGA is divided into several types of memory structures, including the database buffers, redo log buffers and the shared pool. These area have fixed size and are created during instance startup.1. Database Buffer Cache :Database buffers of the SGA store the most recently used blocks of database data; the set of database buffers in an instance is the database buffer cache. These buffers can contain modified data that has notyet beenwritten to disk. Because the most recently used data is kept in memory, less disk I/O is necessary and performance is increased.

2. Redo log buffer:The redo log buffer of the SGA stores redo entries - a log of changesmade to the database. The redo entries stored in the redo log buffers are written to an online redo log file, which is used if database recovery is necessary. Its size is static.3. Shared Pool:The shared pool is a portion of the SGA that contains shared SQL constructs such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted in a database. A shared SQL area contains information such as the parse tree and execution plan for the coressponding statement. A single shared SQL area is used by multiple application that issue the same statement leaving more control over cursors.4. Cursors:A cursor is a handle ( a name or pointer ) for the memory associated with a specific statement. Although most Oracle Users rely on the automatic handling of the Oracle Utilities, the programmatic interfaces offer application designers more control over cursors.

b) Program Global Area: The PGA is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the configuration of Oracle.

What is a Process ?

A Process is a “thread of control” or a mechanism in a operating system that can execute a series of steps. Some operating system use the term job as task.

Explain the types of Processes used by Oracle ?

An Oracle database system has two types of processes :1) User Process.2) Oracle Process.User Process : A user process is created and maintainedto execute the software code of an application program ( such as a Pro*C program ) or an Oracle tool ( such as SQL* DBA ). The user processes also manages the communication with the server processes through the program interface.Oracle Processes:Oracle processes are called by other processes to perform functions on behalf of the invoking process. The different types of Oracle processes and their specific functions are as follows :Server Process:Oracle creates server processes to handle requests from connected user processes. A server process is in charge of communicating with the user process and interacting with the Oracle to carry out requests of the associated user process. Oracle can be configured to vary the number of users processes per server process. In a dedicated server configuration, a server process handles requests for a single user process. A multi- threaded server configuration allows many users processes to share a small number of server processes and maximizing the utilization of available system resources.

Backround Processes:Oracle creates a set of backround processes for each Oracle Instance.They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user processes. The backround processes asynchronouslyperform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

An SGA and the Oraclebackround processes constitute an Oracle Instance. Each Oracle instance may use several backround processes. They areDBWR,LGWR,CKPT,SMON,PMON, ARCH,RECO,Dnnn, AND Lckn.

What is a Database Writer ?

The DBWRwrites modified blocks from the database buffer cache to the datafiles. Because of the way Oracle performs logging, DBWR does not need to write blocks when a transaction commits.Instead, DBWR is optimized to minimize disk writes. In general, DBWRwrites onlywhen more data needs to be read into the SGA and too few database buffers are free. The leastrecentlyused data is written to the datafile first.

What is a Log Writer?

The log writer writes redo log entries to disk. Redo logdata is generated in the redo log buffer of the SGA. As transactions commit and the log buffer fills, LGWR writesredo log entries into an on-line redo log file.

What isa Checkpoint ?

At specific times, all modified database buffers in the SGA are written to the data files by DBWR; this event is called a Checkpoint. The checkpoint process is responsible for signaling DBWR at checkpoints and updating all the data files and control files of the database to indicate the most recent checkpoint. CKPT is optional; if CKPT is not present, LGWR assumes the responsibilities of CKPT.

What is a System Monitor ?

The System monitor performs instance recovery at instance startup. SMON cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file read or off-line errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back. SMON also coalesces free extents within the database, to make free space contiguous and easier to allocate.

What is a Process monitor ?

The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on the Dispatcher and server processes and restarts them if they have failed.

What is an Archiever ?

The Archiever copies the on-line redo log files to archival storage when they are full. ARCH is active only when a database’s redo log is usedin ARCHIEVELOG mode.

What is a Recoverer?

The recoverer is used to resolve distributed transactions that are pending due to a NETWORK or system failure in a distributed database.

What is a Dispatcher ?

Dispatcher are optional backround processes, present only when a Multi-threaded server configuration is used. Atleast one dispatcher process is created for every communication protocol in use (D000,...Dnnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the response back to the appropriate user processes.

What is a LOCK(LCKn)?

Upto ten lock processes (LCK0..LCK9) are used for inter- instance locking when the Oracle parallel server is used.