Oracle Architecture and Concepts

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.