A sort operation will fail if a sort to disk needs more disk space and there are 1 no unused blocks in the sort segment, and 2 no space available in the temporary tablespace for the sort segment to allocate an additional extent. This will most likely cause the statement that prompted the sort to fail with the Oracle error, ORA unable to extend temp segment.
As mentioned, this error message also gets logged in the alert log for the instance. It is important to note that not all ORA errors indicate temporary tablespace issues. It is helpful that Oracle logs ORA errors to the instance alert log as it informs a database administrator that there is a space issue. The error message includes the name of the tablespace in which the lack of space occurred, and a DBA can use this information to determine if the problem is related to sort segments in a temporary tablespace or if there is a different kind of space allocation problem.
Unfortunately, Oracle does not identify the text of the SQL statement that failed. Thus we are informed that a problem has occurred but we are not given tools with which to identify the cause of the problem nor measure the user impact of the statement failure.
However, Oracle does have a diagnostic event mechanism that can be used to give us more information whenever an ORA error occurs by causing Oracle server processes to write to a trace file.
This trace file will contain a wealth of information, including the exact text of the SQL statement that was being processed at the time that the ORA error occurred. This diagnostic event imposes very little overhead on the system, because Oracle only writes information to the trace file when an ORA error occurs.
You can set a diagnostic event for the ORA error in your individual database session with the following statement:. The above statement will affect the current instance only and will not edit the server parameter file. That is to say, if you stop and restart the instance, the diagnostic event setting will no longer be active. You can deactivate the ORA diagnostic event or remove all diagnostic event settings from the server parameter file with statements such as the following:.
The entry in the instance alert log that indicates an ORA error occurred will also indicate that a trace file was written. An entry in the instance alert log will look like this:. From the trace file you can clearly see the full text of the SQL statement that failed. You can also see when it failed along with attributes of the database session such as module, action, and service name. It is important to note that the statements captured in trace files with this method may not themselves be the cause of space issues in the temporary tablespace.
For example, one query could run successfully and consume The second query is the one that will get captured in a trace file, while the first query is more likely to be the root cause of the problem.
The trace file will contain additional information, including a call stack trace and a binary stack dump. This information is not likely to be useful, unless perhaps you want to learn more about Oracle internals. The diagnostic event facility has been built into the Oracle database product for a very long time, but it is not widely documented.
In general, you absolutely should not try setting diagnostic events in a production database unless you have a very good idea of what they do. In case of some errors such as hardware failures, Oracle automatically takes an online tablespace offline. 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.
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. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases. Different tablespace sets may be stored in a tablespace repository, and different versions of a particular tablespace set also may be stored.
A version of a tablespace set in a tablespace repository consists of the following files:. Both the datafiles and the metadata export file must be copied to the target database.
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. These files have identical on disk formats for file header blocks, which are used for file identification and verification. Oracle Database Administrator's Guide for details about how to move or copy tablespaces to another database, including details about transporting tablespaces across platforms. Oracle Streams Concepts and Administration for more information on ways to copy or transport files.
A t ablespace in an Oracle database consists of one or more physical datafiles. A datafile can be associated with only one tablespace and only one database. The file cannot be written to unless its associated tablespace is returned to the read-write state. Restrictions on Read-Only Tablespaces You cannot add datafiles to a tablespace that is read-only, even if you take the tablespace offline. When you add a datafile, Oracle must update the file header, and this write operation is not allowed.
To update a read-only tablespace, you must first make the tablespace writeable. After updating the tablespace, you can then reset it to be read-only. Read-Only Tablespaces and Recovery Read-only tablespaces have several implications upon instance or media recovery. See Chapter 24 , "Database Recovery", for more information about recovery. Temporary Tablespaces Space management for sort operations is performed more efficiently using temporary tablespaces designated exclusively for sorts.
This scheme effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space. The performance gains are significant in parallel server environments. A temporary tablespace is a tablespace that can only be used for sort segments. No permanent objects can reside in a temporary tablespace. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists in 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 grows 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.
Datafiles 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. When a datafile is created for a tablespace, Oracle creates the file by allocating the specified amount of disk space plus the overhead required for the file header.
When a datafile is created, the operating system is responsible for clearing old information and authorizations from a file before allocating it to Oracle. If the file is large, this process might take a significant amount of time. Additional Information: For information on the amount of space required for the file header of datafiles on your operating system, see your Oracle operating system specific documentation. Datafile Contents After a datafile is initially created, the allocated disk space does not contain any data; however, Oracle reserves the space to hold only the data for future segments of the associated tablespace -- it cannot store any other program's data.
0コメント