AUTO is the default. 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. Oracle Database Administrator's Guide for more information about automatic segment space management.
If y ou created your database with an earlier version of Oracle, then you could be using dictionary managed tablespaces. 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. 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. This is set when the database is created and can be any valid size. Legitimate values are from 2K to 32K. In the initialization parameter file or server parameter, you can configure subcaches within the buffer cache for each of these block sizes.
Subcaches can also be configured while an instance is running. You can create tablespaces having any of these block sizes. The standard block size is used for the system tablespace and most other tablespaces. Multiple block sizes are useful primarily when transporting a tablespace from an OLTP database to an enterprise data warehouse. This facilitates transport between databases of different block sizes.
Oracle Database Data Warehousing Guide for information about transporting tablespaces in data warehousing environments. A database administrator can bring any tablespace other than the SYSTEM tablespace online accessible or offline not accessible whenever the database is open. A tablespace is usually online so that the data contained within it is available to database 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 at 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 needed. When a tablespace goes offline or comes back online, this is recorded in the data 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 which it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that database. An offline tablespace cannot be read or edited by any utility other than Oracle.
Thus, offline tablespaces cannot be transposed to 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, DBW n , fails in several attempts to write to a datafile of the tablespace.
Users trying to access tables in the offline tablespace receive an error. Oracle Database Utilities for more information about tools for data transfer. If you create multiple tablespaces 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 tables 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 tablespace, 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, static portions of a database.
Read-only tablespaces cannot be modified. After updating the tablespace, you can then reset it to be read only. 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. You can manage space for sort operations more efficiently by designating one or more temporary tablespaces 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. For example, you can create indexes on very large tables, and the sort operation during index creation can be distributed 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.
The 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 temporary tablespace. 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 tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory.
The sort segment of a given temporary tablespace is created at the time of the first sort operation. The sort segment expands by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance.
Oracle Database Performance Tuning Guide for information about setting up temporary tablespaces for sorts and hash joins. A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even across different platforms.
You can clone a tablespace and plug it into another database, copying the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug it into another Oracle database, moving the tablespace between databases. When you transport tablespaces you can also move index data, so you do not have to rebuild the indexes after importing or loading the table data. You can transport tablespaces across platforms. Many, but 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 running Oracle on a different platform.
Simplify the distribution of data from a data warehouse environment to data marts which are often running on smaller platforms. A tablesp ace repository is a collection of tablespace sets.
Any attempt to access data in offline tablespace will result in an error. The read-only tablespaces allow Oracle to avoid performing backup and recovery of large, static parts of a database. Oracle allows you to remove objects such as tables and indexes from a read-only tablespace.
However, it does not allow you to create or alter objects in a read-only tablespace. In a traditional tablespace, three positions in the ROWID are used to identify the relative file number of the row. Because you only have one datafile in bigfile tablespaces, these three positions are instead used to lengthen the data block number for the row, thereby allowing for a much larger number of ROWIDs from traditional smallfile tablespaces. Also, if you are using traditional filesystems, make sure you are using a logical volume manager that provides the flexibility to map out your storage system appropriately so the single datafile can grow as needed.
It is reserved for system-managed undo data. Creating a Temporary Tablespace: Example This statement shows how the temporary tablespace that serves as the default temporary tablespace for database users in the sample database was created:. If we assume that the default database block size is 2K, and that each bit in the map represents one extent, then each bit maps 2, blocks. The following example sets the default location for datafile creation and then creates a tablespace with an Oracle-managed tempfile in the default location.
The tempfile is M and is autoextensible with unlimited maximum size. These are the default values for Oracle-managed files:. If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:. When more space is required, kilobyte extents will be added up to a maximum size of megabytes:.
This statement creates a locally managed tablespace in which every extent is K and each bit in the bit map describes 64 blocks. Specifying Segment Space Management for a Tablespace: Example The following example creates a tablespace with automatic segment-space management:. Creating Oracle-managed Files: Examples The following example sets the default location for datafile creation and creates a tablespace with a datafile in the default location.
The datafile is M and is autoextensible with an unlimited maximum size:. The following example creates a tablespace with an Oracle-managed datafile of M that is not autoextensible:.
Note: Oracle strongly recommends that you run your database in automatic undo management mode. For more information, please refer to Oracle Database Administrator's Guide. Note: Media recovery does not recognize tempfiles. Note: On some operating systems, Oracle does not allocate space for a tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used.
To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well.
Then proceed with the creation or resizing operation. Please refer to Oracle Database Administrator's Guide for information on when to use this setting. Note: After you have specified extent management with this clause, you can change extent management only by migrating the tablespace. Note: Oracle strongly recommends that you create only locally managed tablespaces. Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces.
See Also: Oracle Database Concepts for a discussion of locally managed tablespaces. See Also: Oracle Database Administrator's Guide for information on automatic segment-space management and when to use it Oracle Database Reference for information on the data dictionary views "Specifying Segment Space Management for a Tablespace: Example".
0コメント