Concepts and Architecture Interview Preparation Guide

Download PDF

A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.

A materialized view is a replica of a target master from a singlepoint in time. The concept was first introduced with Oracle7 termed asSNAPSHOT. In Oracle release 7.1.6 snapshots were enhanced to enableDMLs along with a new terminology, updatable snapshots. With Oracle8.1.6 snapshots started to be used in data warehouse environments so anew terminology materialized view was introduced to address bothdistributed and data warehouse materialized views.

It's an oracle object as other object like table. It stores index key + rowid for that key. So when you scan index it finds respective rowid and fetch data from that rowid. and it's faster

Data Segment,
Index Segment,
Rollback Segment and
Temporary Segment.

A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

It stores your uncommited temporart data. ex. If you do any insert/update/delete and you won't commit it is written in rollback segment.

Index is a Database object . It can be accessed rows physical fast. when you move the table from different table spaces or same table spaces indexes will unusually status. So You compulsory rebuild the index. Rebuilding the index is very fast than drop and create the new indexes.

Yes.Use Enable Novalidate to enforce the constraints. Existing data will not be checked, only new/update will be validated.

Yes as U said,If on one side a query is executed and simultaneously a DML is happening at the same time. THEN the user doing the DML action gives a commit in between while the query is still executing on the other side then, on the query side U get this message "SNAPSHOT TOO OLD"Suggestion :Alter system set UNDO_retention = 900 ;or give a large value for undo retention, ie., the undo block will be kept in the memory until 900 microseconds even after the commit transaction on the same table, so that the query ends within that.

Controlfile contains the information about the database like names and locations of datafiles, name of the database, database creation timestamp and apart from this the SCN are also reported into control during the database operation. Especially during the database startup the controlfile is used to gather the information about the database and while opening the database the information in the controlfile is validated to check for the consistency of the database.

Special requirements for table data:
1) allow NULL value
2) uniqueness of value
3) primary key
4) foreign key
5) special value size/text/length etc requirements
All information is stored in Data Dictionary.

UPDATE and DELETE Restrict -
A referential integrity rule that disallows the update or deletion of referenced data.

The controlfile contains :
1. Name and Location of Datafiles, Redo Logfiles.
2. Timestamp of Database Creation.
3. Current log sequence number is recorded when log switches are occur.
4. Checkpoint information is recorded as checkpoints are made.
5. Database identifier is recorded when the database is created.

The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database.

It stores information about both the logical and physical structure of the database, the valid users of an ORACLE database, integrity constraints defined for tables in the database and space allocated for a schema object and how much of it is being used.

HelloAs far as i know, a system tablespace is the only tablespace which is created automatically, when the oracle DB is installed, no matter what the DB will contain atleast one tablespace and that is the system tablespace. Do let me know if u have any other answers.Praveen.R

A tablespace is a group of segments and a segment is a group of extents and an extent is a group of blocks.a tablespace can occupy two physical datafiles.

The phycial structures of the database are
1.datafile
2.redologfile
3.control filedatafile:-
datafile contains actial data of the database.
redologfile:- it contains all changes done to the data base. it works in the circular fashion.
controlfile:- it contains status and structure of the database.

Rollback segments are used to get data blocks to the original state when user issues a 'rollback' statementgive consistent data to a user, hiding uncommitted changes made by other users/sessionsto undo uncommitted changes during database recovery. After applying all changes recorded in the redo logs, Oracle uses rollback segments to undo uncommitted changes. Since rollback segments are stored in the database buffers, they are automatically protected by the redo log.

A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls.Unique key cannot be used to create referential integrity whereas primary key can do.

A check constraint allows you to specify a condition on each row in a table. Note: A check constraint can NOT be defined on a VIEW. The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables. A check constraint can NOT include a SUBQUERY. A check constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Ya synonym is used to rename or to create alias for the data base object ..

An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment. When you create a table, Oracle allocates to the table's data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows. If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment. (The next section explains the factors controlling the size of incremental extents.) For maintenance purposes, the header block of each segment contains a directory of the extents in that segment.

No, View never contain the the data, Only defination of it stores in the data base, when ever you invoke them they show you the data based on their defination.Only Materlized view or SnaptShot contain the the data.

A sequence is an automatically generated unique number.It is typically used to create a primary key value.It is a sharable object.Using sequence change the increment value, maximum value, minimum value, cycle option, or cache option.

Hash Clusters cluster table data in a manner similar to normal, index clusters. To find or store a row in a hash cluster, Oracle applies a hash function to the row's cluster key value. The resulting hash value corresponds to a data block in the cluster.All rows with the same key value are stored together on disk.Hash clusters are a better choice than using an indexed table or index cluster when a table is often queried with equality queries (for example, WHERE product_id=123). For such queries, the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the rows.This reduces the amount of I/Os that must be performed to locate and read/write a row of data.