Introduction to Oracle

Concepts

Data Dictionary

The data dictionary is a set of tables Oracle uses to maintain information about the database. The data dictionary contains information about tables, indexes, clusters, and so on.

Schema

A schema is a collection of objects associated with the database.

Schema Objects

Schema objects are abstractions or logical structures that refer to database objects or structures. Schema objects consist of such things as clusters, indexes, packages, sequences, stored procedures, synonyms, tables, views, and so on.

Tables

A table is created in a table segment. The table segment in turn consists of one or more extents. If the table grows to fill the current extents, a new extent is created for that table. These extents grow in a manner specified by the STORAGE clause used to create the table.

The data from the table is stored in database blocks. The number of rows put in one data block depends on the size of the row and the storage parameters. Depending on the type of data and how it will be accessed, you might want to consider how it is stored.

Views

A window into a table. Although a view is treated like a table in that you can select columns from it, a view is not a table; it is a logical structure that looks like a table but is actually a superset or subset of a table. A view derives its data from other tables, which are referred to as base tables. These base tables can be tables or even other views. Views are used to simplify the access of certain data and to hide certain pieces of data.

System Global Area (SGA)

The SGA is a shared-memory region that Oracle uses to store data and control information for one Oracle instance. The SGA is allocated when the Oracle instance starts; it is deallocated when the Oracle instance shuts down. Each Oracle instance that starts has its own SGA. The information in the SGA is made up of the database buffers, the redo log buffer, and the shared pool; each has a fixed size and is created at instance startup.

OLTP

The Online Transaction Processing (OLTP) system is probably the most common of the RDBMS configurations. OLTP systems have online users that access the system. These systems are typically used for order-entry purposes, such as for retail sales, credit-card validation, ATM transactions, and so on.

Characteristics of OLTP Systems
OLTP systems typically support large numbers of online users simultaneously accessing the RDBMS. Because users are waiting for data to be returned to them, any excessive response time is immediately noticeable. OLTP systems are characteristically read and write intensive. Depending on the specific application, this read/write ratio might vary.

DSS

The Decision Support System (DSS) is used to assist with the decision-making process. These decisions might be based on information such as how sales in a particular region are doing, what cross-section of customers is buying a particular product, or to whom to send a mailing. The DSS system is used to help make decisions by providing good data.

OLAP

The term OLAP (Online Analytical Processing) is usually used in relation with multidimensional data. OLAP users might be financial analysts or marketing personnel looking at global data.

Database

The physical layer of the database consists of three types of files:

The logical layer of the database consists of the following elements:

The database is divided into one or more logical pieces known as tablespaces. A tablespace is used to logically group data together. For example, you can create one tablespace for accounting and a separate tablespace for purchasing. Segmenting groups into different tablespaces simplifies the administration of these groups (see Figure 2.1). Tablespaces consist of one or more datafiles. By using more than one datafile per tablespace, you can spread data over many different disks to distribute the I/O load and improve performance.

As part of the process of creating the database, Oracle automatically creates the SYSTEM tablespace for you. Although a small database can fit within the SYSTEM tablespace, it's recommended that you create a separate tablespace for user data. The SYSTEM tablespace is where the data dictionary is kept. The data dictionary contains information about tables, indexes, clusters, and so on. Datafiles can be operating system files or, in the case of some operating systems, RAW devices Within Oracle, the space used to store data is controlled by the use of logical structures. These structures consist of the following:

The Oracle instance

The basic memory structures associated with Oracle are the System Global Area (SGA) and the Program Global Area (PGA).

The SGA is a shared memory region that Oracle uses to store data and control information for one Oracle instance. The SGA is allocated when the Oracle instance starts and deallocated when the Oracle instance shuts down. Each Oracle instance that starts has its own SGA. The information in the SGA consists of the following elements, each of which has a fixed size and is created at instance star tup:

The PGA is a memory area that contains data and control information for the Oracle server processes. The size and content of the PGA depends on the Oracle server options you have installed. This area consists of the following components:

Data dictionary

The data dictionary contains information such as the following:

Paritioned objects

Partitioned objects allow Oracle objects, such as tables and indexes, to be broken into smaller, more manageable pieces. Partitioning these objects allows many operations that could normally be performed on only a table or an index to be divided into operations on a partition. By dividing these operations, you can often increase the parallelism of those operations, thus improving performance and minimizing system down time.

SLQ*Net

Oracle's communication protocol. SQL*Net uses various network communication protocols such as TCP/IP, DECNet, and SPX/IPX, and provides a common programming layer for the Oracle developer.

SQL*Plus

Primary interface into Oracle. SQL*Plus can be used for administrative purposes if desired

PL/SQL

User interface tool for the Oracle RDBMS. With SQL*Plus, you can connect into the RDBMS and run SQL commands and PL/SQL programs.

Administrative accounts

As part of the installation of the Oracle RDBMS, several accounts are created with these special privileges:

INTERNAL

The INTERNAL account is provided mainly for backward compatibility with earlier versions of Oracle, but is still used for key functions such as starting up and shutting down the instance.

SYS

Automatically created whenever a database is created. Used primarily to administer the data dictionary. This account is granted the DBA role, as well as CONNECT and RESOURCE roles.

SYSTEM

Automatically created whenever a database is created. This account is used primarily to create tables and views important to the operation of the RDBMS.

It is recommended to create independent user acounts, and grant them the DBA role.

Administrative roles

User authentication

Done either by the OS or through the use of Oracle password files.

Oracle instances

Each running of Oracle is identified by a SID (system identifier.)

Managing Oracle

Once an instance is running, you must configure the network to connect to it. This allows a remote process to connect to a database (done via the LISTENER.ORA) and to allow a connection into an instance via an alias (done via the TNSNAMES.ORA file, which can be configured with the Net8 utility.)

The Oracle RDBMS uses two types of processes: the user processes (also known as the shadow or server processes) and the Oracle processes (also known as background processes). User, or client, processes are the user's connections into the RDBMS system. The user process manipulates the user's input and communicates with the Oracle server process through the Oracle program interface. The user process is also used to display the information requested by the user and, if necessary, can process this information into a more useful form.

Because the load incurred by large numbers of user processes can be quite heavy on the system, measures should be taken to reduce this number. Several different methods that involve multiplexing the connections into the Oracle instance can be used to reduce the user load. Multiplexing involves reducing the number of connections. On one side of the multiplexor, each process or user might have its own connection On the other side, many processes or users might share the same connections. In its simplest form, the multiplexor reduces a large number of connections to a smaller number of connections.

The multiplexing can be done on the server itself, but in general, the multiplexing is done on another server. A system that has a middle server to handle connection processing is typically called a three-tier system. If you are directly connecting all the users from client systems to the database server, this is known as a two-tier system.

Managing databases

Split into the tasks of the DBA and the tasks of the end user or application developer, depending on what level they access the DBMS.

Creating the database actually occurs in two separate--but related--steps: The actual database-creation command (creates the redo log files, the control files, and the datafiles necessary to create the SYSTEM tablespace. The SYSTEM tablespace contains the SYSTEM rollback segment, the data dictionary, stored procedures, and other structures necessary to run the Oracle instance), and adding tablespaces, tables, indexes, and so on that are used to store your specific data.

The DBA is responsible for creating the database, adding datafiles, and managing the control files and redo log files necessary for the proper function of the Oracle RDBMS. The DBA is also responsible for allocating these resources to the end user so that he or she can properly use them. The DBA or developer must then build tables, indexes, and clusters on these tablespaces. After the tables have been built and loaded, the user can then access this data.

The Oracle tablespace is the lowest logical layer of the Oracle data structure. The tablespace consists of one or more datafiles; these can be files on the operating system filesystem or raw devices. Until recently the size of a datafile was fixed, but now datafiles can be extended automatically or manually. Think of a tablespace as a filesystem on a set of disk drives. The space is there and allocated, but is not used until somebody creates a file or saves some data. This is also true of the Oracle tablespace.

The tablespace is important in that it provides the finest granularity for laying out data across datafiles. After the tablespace is created, you don't have much control over how the actual tables are distributed within the tablespace. By carefully configuring the tablespace, you have some coarse configuration options (you will see these later today), but for the most part, the internal layout of schema objects on tablespaces is done automatically. The maximum size of a datafile is 32GB (gigabytes). The maximum number of datafiles per tablespace is 1,022. The maximum size of a tablespace is 32TB (terabytes).

A tablespace can hold four different types of segments:

You might to separate your database into different tablespaces based on function. That way, maintenance operations and backups can be done on a per-department basis. For example, you can put accounting and sales on different tablespaces so they can be backed up separately.

There are a few initial setup steps that should be completed before you begin the actual creation process:

  1. Backing up any existing databases on the system
  2. Creating the init.ora file (necessary for each new database)
  3. Starting up the Oracle instance

After the database has been created, two scripts (CATALOG.SQL and CATPROC.SQL) should be run to create the data dictionary views. These views are important to the operation of the system as well as for the DBA.

Import-export

There are several different ways of performing both of these tasks:

Managing users

Oracle security is administered differently depending on what resource is needed. Access to the database is allowed or disallowed based on a user ID. This user ID has permissions associated with it. These permissions can be assigned either individually or via a role or profile. A role is used to assign privileges that allow the user to access different objects and operations; a profile is used to control the amount of system resources that the user is allowed to consume.

Resources