0062-0064

Previous Table of Contents Next

Page 62

When you create objects that use storage in the database (such as tables), you should specify the tablespace location of the object as part of the CREATE statement for the object. Only system tables should occupy storage in the SYSTEM tablespace. The system tables are tables such as tab$, col$, ind$, fet$, and other internal tables.

Objects such as synonyms and views do not take up storage within the database other than the storage in the data dictionary table for their definitions, along with the definitions for all other types of objects.

Tablespaces can be added, dropped, taken offline and online, and associated with additional database files. By adding another file to a tablespace, you increase the size of the tablespace and therefore the database itself.

You cannot drop the SYSTEM tablespace; this would destroy the database because the system tables are there. You also cannot take the SYSTEM tablespace offline.

Segments

A segment is a generic name given to any object that occupies storage in the database files. Some examples of segments are table segments (data segments), index segments, rollback segments, temporary segments, and the cache (bootstrap) segment. A segment uses a number of Oracle blocks that are in the same tablespace (although the blocks themselves can be in different files that make up the tablespace).

Extents

The storage for any object on the database is allocated in a number of blocks that must be contiguous in the database files. These contiguous blocks are known as extents. For example, when a table is first created using default settings, five Oracle blocks are allocated to the table for the very first extent ( otherwise known as the initial extent). As rows are inserted and updated into the table, the five blocks fill with data. When the last block has filled and new rows are inserted, the database automatically allocates another set of blocks (five blocks) for the table, and the new rows are inserted into the new set of blocks. This allocating of additional storage (additional extents) continues until there is no more free space in the tablespace. The table starts with one initial extent and is then allocated other secondary (or next) extents. The blocks for an extent must be contiguous within the database files.

Once an extent is allocated to a segment (table), those blocks cannot be used by another database object, even if all the rows in the table are deleted. The table must be dropped or truncated to release the storage allocated to the table. The exception to this is rollback segments, which can dynamically release storage that was allocated to them.

Oracle Blocks

Oracle "formats" the database files into a number of Oracle blocks when they're first created ”making it easier for the RDBMS software to manage the files and easier to read data into the memory areas.

Page 63

These blocks are usually 1KB (the default for PC-DOS systems), 2KB (the default for most UNIX machines and VAX VMS), 4KB (the default for IBM mainframes), or larger. For a 50MB database file, there would be 25,600 Oracle blocks assuming a block size of 2KB (50MB/2KB).

The block size should be a multiple of the operating system block size. Regardless of the block size, not all of the block is available for holding data; Oracle takes up some space to manage the contents of the block. This block header has a minimum size, but it can grow.

These Oracle blocks are the smallest unit of storage. Increasing the Oracle block size can improve performance, but you should do this only when the database is first created.

When you first create a database, it uses some of the blocks within the first file, and the rest of the blocks are free. In the data dictionary, Oracle maintains a list of the free blocks for each data file in each tablespace.

Each Oracle block is numbered sequentially for each database file starting at 1. Two blocks can have the same block address if they are in different database files.

CAUTION
Do not modify the Oracle block size once you've created the database.

ROWID in Oracle8

The ROWID is a unique database-wide physical address for every row on every table. Once assigned (when the row is first inserted into the database), it never changes until the row is deleted or the table is dropped.

The ROWID, stored in base-64 format, consists of the following four components , the combination of which uniquely identifies the physical storage location of the row:

  • Database object ID that owns the row
  • Oracle database file number, which contains the block with the row
  • Oracle block number, which contains the row
  • The row within the block (because each block can hold many rows)

The ROWID is used internally in indexes as a quick means of retrieving rows with a particular key value. Application developers also use it in SQL statements as a quick way to access a row once they know the ROWID.

Free Space and Automatic Compaction

When a database file is first created or added to a tablespace, all the blocks within that file are empty blocks that have never been used. As time goes by, the blocks within a database file are used by a segment (table), or they remain free blocks. Oracle tracks the file's free blocks in a list

Page 64

in the data dictionary. As you create and drop tables, the free space becomes fragmented , with free space in different parts of the database file. When the free blocks are scattered in this way, Oracle has no way to automatically bring the free storage together.

When two fragments of free space are physically next to each other in the database file, the two smaller fragments can be compacted together into one larger fragment, which is recorded in the free space list. This compacting reduces the overhead when Oracle actually needs the free space (when a table wants to allocate another extent of a certain size, for example). The SMON background process performs this automatic compaction.

System Database Objects

This section discusses some of the system objects that support the workings of the architecture and give information about the structure of the database.

The Data Dictionary

The first tables created on any database are the system tables, also known as the Oracle data dictionary. These tables are owned by the first Oracle user account that is created automatically ” by the user SYS. The system tables record information about the structure of the database and the objects within it, and Oracle accesses them when it needs information about the database or every time it executes a DDL statement (Data Definition Language) or DML statement (Data Manipulation Language). These tables are never directly updated; however, updates to them occur in the background whenever a DDL statement is executed.

The core data dictionary tables hold normalized information that is cryptic to understand, so Oracle provides a set of views to make the information in the core system tables more meaningful. You can access the names of over 170 of the views in the data dictionary with the following command:

 SELECT * FROM DICT; 

Oracle requires the information in the data dictionary tables to parse any SQL statement. The information is cached in the data dictionary area of the shared pool in the SGA.

Because the very first tablespace created is the SYSTEM tablespace, the data dictionary tables use storage in the database files associated with the SYSTEM tablespaces.

Rollback Segments

Whenever you change data in Oracle, the change must be either committed or reversed. If a change is reversed or rolled back, the contents of the data block are restored back to the original state before the change. Rollback segments are system-type objects that support this reversing process. Whenever you make any kind of change to either application tables or system tables, a rollback segment automatically holds the previous version of the data that is being modified, so the old version of the data is available if a rollback is required.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net