| Orac
le® Database Concepts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
| Orac
le® Database Concepts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
T his chapter desc ribes tablespaces, the primary logical database structures of any Oracle database, and the physical datafiles that correspond to each tablespace.
This chapter contains the following topics:
Oracle stores data logically in tables paces and physically in datafiles associated with the corresponding tablespace. Figure 3-1 illustrates this relationship.
Databases, tablespaces, and datafiles are clo sely related, but they have important differences:
An Oracle database consists of one or more logical st orage units called tablespaces, which collectively store all of the database's data.
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
A database's data is collectively stored in the datafiles that constitute e ach tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another databas e can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).
Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle databas e. You specify operations in terms of database objects rather than filenames. Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:
Tablespaces
Redo log files
Control files
Through initialization parameters, you specif y the file system directory to be used for a particular type of file. Oracle then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.
The size of a tablesp ace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces tha t constitute the database.
You can enlarge a database in three ways:
Add a datafile to a tablespa ce
Add a new tablespace
Increase the size of a datafile
Wh en you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespa ce. Figure 3-2 illustrates this kind of space increase.
Figure 3-2 Enlarging a Database by A dding a Datafile to a Tablespace
Alternatively, you can create a new tablespace (which contains at least one additional datafile) to incre ase the size of a database. Figure 3-3 illustrates this.
Figure 3-3 Enlarging a Database by Adding a New Tablespace
The third option for enlarging a database is to change a datafile's size or let datafiles in existing tablespaces grow dynamically as more space is needed. You accomplish this by altering existing files or by adding files with dynamic extension pr operties. Figure 3-4 illustrates this.
Figure 3-4 Enlarging a Database by Dynamically Sizing Datafiles
A database is divided into one or more logical storage units ca lled tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided i nto extents. Extents are a collection of contiguous blocks.
This section includes the following topics about tablespaces:
Transport of Tablespaces Betwee n Databases
|
See Also:
|
Oracle lets you create bigfile tablespaces up to 8 exabytes (8 million terabytes) in size. With Oracle-managed files, bigfile tablespaces make datafiles completely transparent for users. In other words, you can perform operations on tablespaces, rather than the underlying datafile. Bigfile tablespaces make the tablespace the main unit of the disk space adminis tration, backup and recovery, and so on. Bigfile tablespaces also simplify datafile management with Oracle-managed files and Automati c Storage Management by eliminating the need for adding new datafiles and dealing with multiple files.
The system default is t
o create a smallfile tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX
tablespace types are always created using the system default type.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management. There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed.
An Oracle database can contain both bigfile and smallfil e tablespaces. Tablespaces of different types are indistinguishable in terms of execution of SQL statements that do not explicitly re fer to datafiles.
You can create a group of temporary tablespaces that let a user consume temporary space from multiple tables paces. A tablespace group can also be specified as the default temporary tablespace for the database. This is useful with bigfile tab lespaces, where you could need a lot of temporary tablespace for sorts.
Because they can be up to 8 exabytes (8 million terabytes) in size, bigfile tablespaces can s ignificantly increase the storage capacity of an Oracle database.
Bigfile tablespaces simplify managemen t of datafiles in ultra large databases by reducing the number of datafiles needed. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.
They simplify database managem ent by providing datafile transparency.
Bigfile tablespaces are intended to be used with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel execut ion and RMAN backup parallelization.
Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tab lespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.
Performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tables paces instead of traditional tablespaces. However, increasing the datafile size might increase time to restore a corrupted file or cr eate a new datafile.
|
See Also: Oracle Database Administrator's Guide for details on cre ating, altering, and administering bigfile tablespaces |
Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the d
atabase is open.
To take advantage of the benefits of locally managed tablespaces, you can create a locally managed SYST
EM tablespace, or you can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.
In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created. It is possible t
o plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.
|
Note: If a tablespace is locally m anaged, then it cannot be reverted back to being dictionary managed. |
All data stored on behalf of stored PL/SQL
program units (that is, procedures, functions, packages, and triggers) resides in the SYSTEM tablespace. If the databas
e contains many of these program units, then the database administrator must provide the space the units need in the SYSTEM tablespace.
|
See Also:
|
The SYSAUX tablespace is an auxiliary tablesp
ace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location
to store data. Therefore, the SYSAUX tablespace is always created during database creation or database upgrade.
T
he SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases.
During normal database operation, the Oracle database server does not allow the SYSAUX tablespace to be dropped or re
named. Transportable tablespaces for SYSAUX is not supported.
Note:
If the
SYSAUX tablespace is unavailable, such as due to a media
failure, then some database features might fail.Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or inde xes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instan ce is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automa tically created and maintained by Oracle.
When the first DML operation is run within a transaction, the transaction is bound ( assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace. In rare circumstances, if the ins tance does not have a designated undo tablespace, the transaction binds to the system undo segment.
|
Caution: Do not run any user transactions before creating the first undo tablespace and taking it online. |
Each undo tablespace is composed of a set of undo files and is locally managed. Like other types of tablespaces, undo blocks are grouped in ex tents and the status of each extent is represented in the bitmap. At any point in time, an extent is either allocated to (and used by ) a transaction table, or it is free.
You can create a bigfile undo tablespace.
A database administrator creates u
ndo tablespaces individually, using the CREATE UNDO TABLESPACE statement. It can also be creat
ed when the database is created, using the CREATE DATABASE statement. A set of files is assigned to each ne
wly created undo tablespace. Like regular tablespaces, attributes of undo tablespaces can be modified with the ALTER DROP TABLESPACE statement.
|
Note: An undo tablespace cannot be dropped if it is b eing used by any instance or contains any undo information needed to recover transactions. |
You assign an undo tablespace to an in stance in one of two ways:
At instance startup. You can specify the undo tablespace in the initializatio n file or let the system choose an available undo tablespace.
While the instance is running. Use A
LTER SYSTEM SET UNDO_TABLESPACE to replace the active undo tablespace with another undo
tablespace. This method is rarely used.
You can add more space to an undo tablespace by adding more datafiles to th
e undo tablespace with the ALTER TABLESPACE statement.
You can have more than one undo tablespace an d switch between them. Use the Database Resource Manager to establish user quotas for undo tablespaces. You can specify the retention period for undo information.
|
See Also: Oracle Database Administrator's Guide for detailed information about creating and managing undo tablespaces |
When the SYSTEM tables
pace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed
SYSTEM tablespace cannot be used for default temporary storage.
If SYSTEM is dictionary managed and if you
do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporar
y storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is r
ecommended and will be necessary in future releases.
Specify default temporary tablespaces when you create a database, using the DEFAULT TEMPORARY
TABLESPACE extension to the CREATE DATABASE statement.
If you drop all default temporar
y tablespaces, then the SYSTEM tablespace is used as the default temporary tablespace.
You can create bigfile tem porary tablespaces. A bigfile temporary tablespaces uses tempfiles instead of datafiles.
|
Note: You cannot make a default temporary tablespace permanent or tak e it offline. |
|
See Also:
|
|
Note: If you do not specify extent management when you create a tablespace, then the default is local ly managed. |
Locally managed tablespaces have the following advantages over dictionary managed tablespaces:
Lo cal management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
Local management of extents avoids recursive space management operations. Such recursive operations can occur in dictionar y managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or rollback segment.
The sizes of extents that are managed locally can be determined automatic ally by the system. Alternatively, all extents can have the same size in a locally managed tablespace and override object storage opt ions.
The LOCAL clause of the CREATE TABLESPACE or CREATE TEMPORARY<
/code> TABLESPACE statement is specified to create locally managed permanent or temporary tablespaces, respectively.
When you create a locally managed tablespac
e using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed. Your choices are:
< code>AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block a vailable for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitma ps enable Oracle to manage free space more automatically; thus, this form of space management is called automatic segment-space manag ement.
Locally managed tablespaces using automatic segment-space management can be created as smallfile (traditional) or bigfi le tablespaces.
MANUAL
This keyword tells Oracle that you want to use free lists for
managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. MANUAL<
/code> is the default.
|
See Also:
Oracle Database SQL Reference for syntax |
If you created your database with an earlier version of Oracle, then you could be using dictionary managed tablespaces. For a tablespace tha t uses the data dictionary to manage its extents, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Oracle also stores rollback information about each update of the dictionary tables. Because dictionary tables and rollback segments are part of the database, the space that they occupy is subject to the same space management operations as all other data.
Oracle supports multiple block sizes in a database. The standard block size is used for the In the initialization parameter file or server parameter, you can configure subcaches within the buffer cache for each of thes
e block sizes. Subcaches can also be configured while an instance is running. You can create tablespaces having any of these block si
zes. The standard block size is used for the system tablespace and most other tablespaces. Multiple block sizes are use
ful primarily when transporting a tablespace from an OLTP database to an enterprise data warehouse. This facilitates transport betwee
n databases of different block sizes. S
ee Also: "Transport of Tablespaces Between Databases " Oracle Data Warehousing Guide for information about transporting t
ablespaces in data warehousing environmentsSYSTEM tablespace. This is set when the database is created and can be any valid size. You specify the standard block size by setting t
he initialization parameter DB_BLOCK_SIZE. Legitimate values are from 2K to 32K.<
/p>
A database administrator can bring any tablespace
other than the SYSTEM tablespace online (accessible) or offline (not accessible) whene
ver the database is open. The SYSTEM tablespace is always online when the database is open because the data dictionary m
ust always be available to Oracle.
A tablespace is usually online so that the data contained within it is available to databas e users. However, the database administrator can take a tablespace offline for maintenance or backup and recovery purposes.
When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects
contained in that tablespace. Active transactions with completed statements that refer to data in that tablespace are not affected a
t the transaction level. Oracle saves rollback data corresponding to those completed statements in a deferred rollback segment in the
SYSTEM tablespace. When the tablespace is brought back online, Oracle applies the rollback data to the tablespace, if n
eeded.
When a tablespace goes offline or comes back online, this is recorded in the dat
a dictionary in the SYSTEM tablespace. If a tablespace is offline when you shut down a database, the tablespace remains
offline when the database is subsequently mounted and reopened.
You can bring a tablespace online only in the database in whic
h it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that databa
se. An offline tablespace cannot be read or edited by any utility other than Oracle. Thus, offline tablespaces cannot be transposed t
o other databases.
Oracle automatically switches a tablespace from online to offline when certain errors are encountered. For example, Oracle switches a tablespace from online to offline when the database writer process, DBWn, fails in several attemp ts to write to a datafile of the tablespace. Users trying to access tables in the offline tablespace receive an error. If the problem that causes this disk I/O to fail is media failure, you must recover the tablespace after you correct the problem.
|
See Also:
|
If you create multiple tablespace s to separate different types of data, you take specific tablespaces offline for various procedures. Other tablespaces remain online, and the information in them is still available for use. However, special circumstances can occur when tablespaces are taken offline. For example, if two tablespaces are used to separate table data from index data, the following is true:
If the tablespace containing the indexes is offline, then queries can still access table data because queries do not require an index to access the table data.
If the tablespace containing the tabl es is offline, then the table data in the database is not accessible because the tables are required to access the data.
If Oracle has enough information in the online tablespaces to run a statement, it does so. If it needs data in an offline tables pace, then it causes the statement to fail.
The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, stati c portions of a database. Oracle never updates the files of a read-only tablespace, and therefore the files can reside on read-only m edia such as CD-ROMs or WORM drives.
|
Note: Because you can only bring a tablespace online in the database in which it was created, read-only tablespaces are n ot meant to satisfy archiving equirements. |
Read-only tabl espaces cannot be modified. To update a read-only tablespace, first make the tablespace read/write. After updating the tablespace, yo u can then reset it to be read only.
Because read-only tablespaces cannot be modified, and as long as they have not been made read/write at any point, they do not need repeated backup. Also, if you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.
|
See Also:
|
You can manage space for sort operations more efficiently by desig nating one or more temporary tablespaces exclusively for sorts. Doing so effectively eliminates serialization of space management ope rations involved in the allocation and deallocation of sort space. A single SQL operation can use more than one temporary tablespace for sorting. For example, you can create indexes on very large tables, and the sort operation during index creation can be distribute d across multiple tablespaces.
All operations that use sorts, including joins, index builds, ordering,
computing aggregates (GROUP BY), and collecting optimizer statistics, benefit from temporary tablespaces. T
he performance gains are significant with Real Application Clusters.
One or more temporary tablespaces can be used only for sort segments. A temporary tablespace is not the same as a tablespace that a user designates for temporary segments, which can be any tablespace available to the user. No permanent schema objects can reside in a te mporary tablespace.
Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists for e very instance that performs a sort operation in a given tablespace.
Temporary tablespaces provide performance improvements whe n you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the t ime of the first sort operation. The sort segment expands by allocating extents until the segment size is equal to or greater than th e total storage demands of all of the active sorts running on that instance.
Create temporary tablespaces by using the CREATE T
ABLESPACE or CREATE TEMPORARY TABLESPACE statement.
|
See Also:
|
A transportable tablespace lets you move a subset of an Oracle da tabase from one Oracle database to another, even across different platforms. You can clone a tablespace and plug it into another data base, copying the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug it into another Orac le database, moving the tablespace between databases on the same platform.
Moving data by transporting tablespaces can be orde rs of magnitude faster than either export/import or unload/load of the same data, because transporting a tablespace involves only cop ying datafiles and integrating the tablespace metadata. When you transport tablespaces you can also move index data, so you do not ha ve to rebuild the indexes after importing or loading the table data.
You can transport tablespaces across platforms. (Many, bu t not all, platforms are supported for cross-platform tablespace transport.) This can be used for the following:
Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers r unning Oracle on a different platform
Simplify the distribution of data from a data warehouse environmen t to data marts which are often running on smaller platforms
Enable the sharing of read only tablespaces across a heterogeneous cluster
Allow a database to be migrated from one platform to another
To move or copy a set of tablespac es, you must make the tablespaces read only, copy the datafiles of these tablespaces, and use export/import to move the database info rmation (metadata) stored in the data dictionary. Both the datafiles and the metadata export file must be copied to the target databa se. The transport of these files can be done using any facility for copying flat files, such as the operating system copying facility , ftp, or publishing on CDs.
After copying the datafiles and importing the metadata, you can optionally put the tablespaces in read/write mode.
The first time a tablespace's datafiles are opened under Oracle Database with the COMPATIBLE in
itialization parameter set to 10 or higher, each file identifies the platform to which it belongs. These files have identical on disk
formats for file header blocks, which are used for file identification and verification. Read only and offline files get the compati
bility advanced after they are made read/write or are brought online. This implies that tablespaces that are read only prior to Oracl
e Database 10g must be made read/write at least once before they can use the cross platform transportable feature.
|
Note: In a database with a locally managedSYSTEM tablespace, dictionary tablespaces cannot be created. It is possible to plug in a dictionary man
aged tablespace using the transportable feature, but it cannot be made writable. |
|
See Also:
|
A tablespace in an Oracle database consists of one or more p hysical datafiles. A datafile can be associated with only one tablespace and only one database.
Oracle creates a datafile for a tablespace by allocating the specifi
ed amount of disk space plus the overhead required for the file header. When a datafile is created, the operating system under which
Oracle runs is responsible for clearing old information and authorizations from a file before allocating it to Oracle. If the file is
large, this process can take a significant amount of time. The first tablespace in any database is always the SYSTEM ta
blespace, so Oracle automatically allocates the first datafiles of any database for the SYSTEM tablespace during databas
e creation.
|
See Also: Your Oracle operating system-specific documentation for information about the amount of space required for the file header of data files on your operating system |
When a datafile is first created, the allocated disk space is fo rmatted but does not contain any user data. However, Oracle reserves the space to hold the data for future segments of the associated tablespace—it is used exclusively by Oracle. As the data grows in a tablespace, Oracle uses the free space in the associated datafiles to allocate extents for the segment.
The data associated with schema objects in a tablespace is physically stored in one or more of the datafiles that constitute the tablespace. Note that a schema object does n ot correspond to a specific datafile; rather, a datafile is a repository for the data of any schema object within a specific tablespa ce. Oracle allocates space for the data associated with a schema object in one or more datafiles of a tablespace. Therefore, a schema object can span one or more datafiles. Unless table striping is used (where data is spread across more than one dis k), the database administrator and end users cannot control which datafile stores a schema object.
You can alter the size of a datafile after its creation or you can specify that a datafile should dynamically grow as schema objects in the tablespace grow. Th is functionality enables you to have fewer datafiles for each tablespace and can simplify administration of datafiles.
|
Note: You need sufficient space on the operating system for expansion. |
You can take tablespaces offline or bring them online at any time, except for the SYSTEM tablespace. All o
f the datafiles of a tablespace are taken offline or brought online as a unit when you take the tablespace offline or bring it online
, respectively.
You can take individual datafiles offline. However, this is usually done only during some database recovery pr ocedures.
Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles, with the following exceptions:
Tempfiles are always set to NOLOGGING mode.
You cannot make a tempfile read only.
You cannot rename a tempfile.
You cannot create a tempfile with the
ALTER DATABASE statement.
When you create or resize tempfiles, they are not always g uaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.
|
|
Tempfile infor
mation is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not
in DBA_DATA_FILES or the V$DATAFILE view.
The database control file is a small binar y file necessary for the database to start and operate successfully. A control file is updated continuously by Oracle during database use, so it must be available for writing whenever the database is open. If for some reason the control file is not accessible, then the database cannot function properly.
Each control file is associated with only one Oracle database.
A control file contains information about the associated database that is required for a ccess by an instance, both at startup and during normal operation. Control file information can be modified only by Oracle; no databa se administrator or user can edit a control file.
Among other things, a control file contains information such as:
The timestamp of database cre ation
The names and locations of associated datafiles and redo log files
Tablespace information
Dataf ile offline ranges
The log history
Archived log information
Backup set and backup piece information
Backup datafile and redo log information
Datafile copy information
Checkpoint information
The database name and timestamp originate at database creation. The database name is taken from either the
name specified by the DB_NAME initialization parameter or the name used in the CREATE DATABASE statement.
Each time that a datafile or a redo log file is added to, renamed in, or d ropped from the database, the control file is updated to reflect this physical structure change. These changes are recorded so that:< /p>
Oracle can identify the datafiles and redo log files to open during database startup
Oracle can identify files that are required or available in case database recovery is necessary
Therefore,
if you make a change to the physical structure of your database (using ALTER DATABASE statements), then yo
u should immediately make a backup of your control file.
Control files also record information about checkpoints. Every three seconds, the che ckpoint process (CKPT) records information in the control file about the checkpoint position in the redo log. This information is use d during database recovery to tell Oracle that all redo entries recorded before this point in the redo log group are not necessary fo r database recovery; they were already written to the datafiles.
|
See Also: Orac le Database Backup and Recovery Advanced User's Guide for information about backing up a database's control file |
As with redo log files, Oracle enables multiple, identical control files to be open concurrently and written for the same database. By storing multiple control files for a single database on d ifferent disks, you can safeguard against a single point of failure with respect to control files. If a single disk that contained a control file crashes, then the current instance fails when Oracle attempts to access the damaged control file. However, when other co pies of the current control file are available on different disks, an instance can be restarted without the need for database recover y.
If all control files of a database are permanently lost during operation, then the instance is aborted and media r ecovery is required. Media recovery is not straightforward if an older backup of a control file must be used because a current copy i s not available. It is strongly recommended that you adhere to the following:
Use multiplexed control fi les with each database
Store each copy on a different physical disk
Use ope rating system mirroring
Monitor backups
|
 Copyright ©&nb sp;1993, 2003 Oracle Corporation All Rights Reserved. |
|
