Tablespace---The primary logical database structures of any oracle database. datafiles correspond to each tablespace.
Oracle stores data logically in tablespace and physically in datafiles associated with the corresponding tablespace. Objects store in tablespaces may span several datafiles but a datafile is a physical structure associated with only one tablespace.
Database, tablespaces and datafiles:
An Oracle database consists of one or more logical storage 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 each tablespace of the database.
Oracle-Managed Files
Oracle-managed files eliminate the need for the DBA to directly manage the operating system files comprising an Oracle database. The DBA can then 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:
A. Tablespace
B. Redo log files
C. Control files
The size of a tablespace is the size of the datafiles that consitute the tablespace. The size of the a database is the collective size of the tablespaces that consitute the database. The DBA can add a datafile to a tablespace; add a new tablespace; increase the size of a datafile. Tablespaces are divided into logical units of storage called segments, which are further divided into …
Bigfile tablespace
Bigfile tablespace allows Oracle database to contain tablespaces made up of single large files rather than numerous smaller ones.
SYSTEM tablespace
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 database is open.
Create a locally managed SYSTEM tablespace or migrate an existing dictionary managed SYSTEM tablespace to a locally managed format. With a locally managed SYSTEM tablespace, dictionary managed tablespace can not be created. If a tablespace is locally managed, it cannot be reverted back to being dictionary managed.
The SYSTEM tablespace always contains the data dictionary tables for the entire database. The data dictionary tables are stored in datafile 1.
All data stored on behalf of stored PL/SQL program units (procedures, functions, packages and triggers) resides in the SYSTEM tablespace.
SYSAUX tablespace
The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. The SYSAUX tablespace is always created during database creation or database upgrade. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. During normal database operation, the Oracle database server does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.
Undo tablespace
Undo tablespaces are special tablespaces used solely for storing undo information. Any other segment types (e.g tables or indexes) are not allowed to be created in the undo tablespace.
Each undo tablespace is composed of a set of undo files and is locally managed. Undo blocks are also grouped in extents and the status of each extent is represented in the bitmap.
Default Temporary Tablespace
When the SYSTEM tablespace is locally managed, the DBA 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 The DBA do not define default temporary tablespace when creating the database, the SYSTEM is still used for default temporary storage.
Tablespace allocates space in extents. Tablespace can use two different methods to keep track of their free and used space:
A. Locally managed tablespace: extent management by the tablespace
B. Dictionary managed tablespace: extent management by the data dictionary.
Locally Managed Tablespace: a tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. Oracle changes the bitmap values to show the new status of the blocks when an extent is allocated for freed for reuse. These changes do not generate rollback information because they do not update tables in the data dictionary.
Locally managed tablespaces’ advantages:
1. automatically tracks adjacent free space, eliminating the need to coalesce free extents.
2. avoids recursive space management operations. Such recursive operations can occur in dictionary managed tablespace if consuming or releasing space in an extent results in another operation that consumes or release space in a data dictionary table or rollback segment.
The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options.
Segment Space Management in Locally Management Tablespace can be both AUTO and MANUAL.
Dictionary Managed Tablespace
For a tablespace that 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.
Oracle supports multiple block sizes in a database. The standard block size is used for the SYSTEM tablespace. The DBA specifies the standard block size by setting the initialization parameter DB_BLOCK_SIZE, values from 2K to 32K.
A DBA can bring any tablespace other than the SYSTEM tablespace online(accessible) or offline(not accessible) whenever the database is open. The SYSTEM tablespace is always online when the database is open because the data dictionary must always be available to Oracle.
The primary purpose of read-only tablespace is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle never updates the fiels of a read-only tablespace, and therefore the files can be reside on read-only media such as CD-ROMs or WORM drives.
Temporary tablespace for sort operations
The DBA can manage space for sort operations more efficiently by designating one or more temporary tablespace exclusively for sorts. Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space. A single SQL operation can use more than one temporary tablespace for sorting. All operations that use sorts, including joins, index builds, ordering, computing aggregates and collecting optimizer statistics benefit from temporary tablespace.
One or more temporary tablespace can be used only for sort segments. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists for every instance that performs a sort operation in a given tablespace. Temporary tablespace provides performance improvements when you have multiple sorts that are too large to fit into memory.
Transport of Tablespace between database
A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even cross different platforms.
A tablespace repository is a collection of tablespace sets. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases.
A tablespace in an Oracle database consists of one or more physical datafiles. a datafile can be associated with only one tablespace and only one database.
Datafiles
When a datafile is first created, the allocated disk space is formatted but does not contain any user data. Oracle reserves the space to hold the data for future segments of the associated tablespace—it’s used exclusively by Oracle.
Data associated with schema objects in a tablespace is physically stored in one or more of the datafiles that constitute the tabelspace. Schema object does not correspond to a specific datafile; a datafile is a repository for the data of any schema object within a specific tablespace. The DBA can alter the size of a datafile after its creation or can specify that a datafile should dynamically grow as schema objects in the tablespace grow.