Oracle

An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management.

• Oracle 9i is an Object/Relational Database Management System specifically designed for e-commerce.
• Oracle 9i, a version of Oracle database. The letter “i” refers to the internet.
• It can scale ten thousands of concurrent users.
• It includes Oracle 9i Application server and Oracle 9i Database that provide a comprehensive high-performance infrastructure for Internet Applications.
• It supports client-server and web based applications.
• The maximum Database holding capacity of Oracle 9i is upto 512 peta bytes(PB).[1 Peta Byte = 1000 Tera Byte]
• It offers Data warehousing features and also many management features.

We can set primary key on table up to 16 columns of table in oracle 9i as well as in Oracle 10g.
The maximum number of data files in Oracle 9i and Oracle 10g Database is 65,536.

Oracle 9i Architecture:

Oracle Storage Structures:

An essential task of a relational database is data storage. This section briefly describes the physical and logical storage structures used by Oracle Database.

 

Physical Storage Structures

The physical database structures are the files that store the data. When you execute the SQL command CREATE DATABASE, the following files are created:

• Data files
  Every Oracle database has one or more physical data files, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the data files.
• Control files
  Every Oracle database has a control file. A control file contains metadata specifying the physical structure of the database, including the database name and the names and locations of the database files.
• Online redo log files
  Every Oracle Database has an online redo log, which is a set of two or more online redo log files. An online redo log is made up of redo entries (also called redo records), which record all changes made to data.

Logical Storage Structures

This section discusses logical storage structures. The following logical storage structures enable Oracle Database to have fine-grained control of disk space use:

• Data blocks
  At the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes on disk.
• Extents
  An extent is a specific number of logically contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
• Segments
  A segment is a set of extents allocated for a user object (for example, a table or index), undo data, or temporary data.
• Tablespaces
  A database is divided into logical storage units called tablespaces. A tablespace is the logical container for a segment. Each tablespace contains at least one data file.

Redo:In the Oracle RDBMS environment, redo logs comprise files in a proprietary format which log a history of all changes made to the database. Each redo log file consists of redo records. A redo record, also called a redo entry, holds a group of change vectors, each of which describes or represents a change made to a single block in the database.

For example, if a user UPDATEs a salary-value in a table containing employee-related data, the DBMS generates a redo record containing change-vectors that describe changes to the data segment block for the table. And if the user then COMMIT the update, Oracle generates another redo record and assigns the change a “system change number” (SCN).

LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again. The numbers next to each line indicate the sequence in which LGWR writes to each redo log file.

Reuse of Redo Log Files by LGWR:

Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are calledinactive redo log files.

A log switch is the point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.

UNDO: Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

• Roll back transactions when a ROLLBACK statement is issued
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using Oracle Flashback Query
• Recover from logical corruptions using Oracle Flashback features.

A Snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data between distributed databases.

Snapshot connected to a Single Master Site:

Snapshots can also contain a WHERE clause so that snapshot sites can contain customized data sets. Such snapshots can be helpful for regional offices or sales forces that do not require the complete corporate data set.When a snapshot is refreshed, Oracle must examine all of the changes to the master table to see if any apply to the snapshot. Therefore, if any changes where made to the master table since the last refresh, a snapshot refresh will take some time, even if the refresh does not apply any changes to the snapshot. If, however, no changes at all were made to the master table since the last refresh of a snapshot, the snapshot refresh should be very quick.

Snapshot and materialized view are almost same same but with one difference.
You can say that materialized view =snapshot + query rewrite functionality query rewrite functionality:In materialized view you can enable or disable query rewrite option. which means database server  will rewrite the query so as to give high performance. Query rewrite is based on some rewritten standards(by oracle itself).So the database server will follow these standards and rewrite the query written in the materialized view ,but this functionality is not there in snapshots.

Simple snapshots are the only type that can use the FAST REFRESH method. A snapshot is considered simple if the defining query meets the following criteria:

• It does not contain any DISTINCT or aggregation functions.
• It does not contain a GROUP BY or CONNECT BY clause.
• It does not perform set operations (UNION, UNION ALL, INTERSECT, etc.).
• It does not perform joins other than those used for subquery subsetting.
• Essentially, a simple snapshot is one that selects from a single table and that may or may not use a WHERE clause.

Oracle8 extends the universe of simple snapshots with a feature known as subquery subsetting, described in the later section entitled “Subquery Subsetting.”

Not surprisingly, any snapshot that is not a simple snapshot is a complex  snapshot.

Complex snapshots can only use COMPLETE refreshes, which are not always practical. For tables of more than about 100,000 rows, COMPLETE refreshes can be quite unwieldy.You can often avoid this situation by creating simple snapshots of individual tables at the master site and performing the offending query against the local snapshots.