Database File Overview (Types)

Database File Overview

An Oracle database involves files with several different purposes. Some files store data, some store information that Oracle needs to keep track of your data, and some store information needed to recover from a system crash. The following diagram shows the files that you will find in a typical Oracle database:

Initialization File: Contains parameters that specify the database block size, the amount of memory to use for the Shared Global Area (SGA), and that control other aspects of how the database instance operates.
Control File: Keeps track of all the files that make up a database.
Redo Log Member: Stores a log of all changes made to the database, the redo log, that is used in the event that the database needs to be recovered.
Tablespace: Tablespaces are logical storage structures that contain table and index data. Oracle allows you to map a tablespace onto one or more physical files.
Datafiles: These are the files that hold the data for tables and for indexes. They are the reason all the other files exist.
Redo Log Group: A group of one or more redo log files that Oracle treats as one. Oracle writes the same information to each file in a group.
Archive Log Destination: This is the location to which Oracle copies redo log files when they are filled.

Oracle Database Files: 1) Initialization file 2) Control File 3) Data File

Databases and Instances

An Oracle database is a collection of data in one or more files. The database contains physical and logical structures. In the course of developing an application, you create structures such as tables and indexes to store rows and speed their retrieval. You can create synonyms for the object names, view objects in different databases (across database links), and restrict access to the objects. You can even use external tables to access files outside the database as if the rows in the files were rows in tables. In this book, you will see how to create these objects and develop applications based on them. An Oracle instance comprises a memory area called the System Global Area (SGA) and the background processes that interact between the SGA and the database files on disk. In an Oracle Real Application Cluster (RAC), more than one instance will use the same database. The instances generally are on separate servers connected by a high-speed interconnect.

Inside the Database

Within the Oracle database, the basic structure is a table. Oracle Database 11g supports many types of tables, including the following:

Relational tables: Using the Oracle-supplied datatypes, you can create tables to store the rows inserted and manipulated by your applications. Tables have column definitions, and you can add or drop columns as the application requirements change. Tables are created via the create table command.
Object-relational tables: To take advantage of features such as type inheritance, you can use Oracle's object-relational capabilities. You can define your own datatypes and then use them as the basis for column definitions, object tables, nested tables, varying arrays,
Index-organized tables: You can create a table that stores its data within an index structure, allowing the data to be sorted within the table.
External tables: Data stored in flat files may be treated as a table that users can query directly and join to other tables in queries. You can use external tables to access large volumes of data without ever loading them into your database. Note that Oracle also supports BFILE datatypes, a pointer to an external binary file. Before creating a BFILE or an external table, you must create a directory alias within Oracle (via the create directory command) pointing to the physical location of the file.
Partitioned tables: You can divide a table into multiple partitions, which allows you to separately manage each part of the table. You can add new partitions to a table, split existing partitions, and administer a partition apart from the other partitions of the table. Partitioning may simplify or improve the performance of maintenance activities and user queries. You can partition tables on ranges of values, on lists of values, on hashes of column values, or on combinations of those options.
Materialized views: A materialized view is a replica of data retrieved by a query. User queries may be redirected to the materialized views to avoid large tables during execution. The optimizer will rewrite the queries automatically. You can establish and manage refresh schedules to keep the data in the materialized views fresh enough for the business needs.
Temporary tables: You can use the create global temporary table command to create a table in which multiple users can insert rows. Each user sees only his or her rows in the table.
Clustered tables: If two tables are commonly queried together, you can physically store them together via a structure called a cluster.
Dropped tables: You can quickly recover dropped tables via the flashback table to before drop command. You can flash back multiple tables at once or flash back the entire database to a prior point in time. Oracle supports flashback queries, which return earlier versions of rows from an existing table.

The next several lessons talk about each of the file types shown in the above diagram. You will learn why each file exists and what it contains.

Lesson 2	Database file overview
Objective	Name the types of files used in an Oracle database.