Oracle Database 10g is a significant upgrade from prior releases of Oracle. New features give developers, database administrators, and end users greater control over the storage, processing, and retrieval of their data.

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 you can 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.

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. Within the Oracle database, the basic structure is a table. Oracle Database 10g 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, and more.

• 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. As of Oracle Database 10g, 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.

To support access to tables, you can use views that perform joins and aggregations, limit the rows returned, or alter the columns displayed. Views may be read-only or updatable, and they can reference local or remote tables. Remote tables can be accessed via database links. You can use synonyms to mask the physical location of the tables.