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. Oracle creates a datafile for a tablespace by allocating the specified 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.
When a datafile is first created, the allocated disk space is formatted 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.
This command writes a SQL script to the database trace file where it can be captured and edited to reproduce the control file. This section presents ways that you can recover your control file from a current backup or from a multiplexed copy. Oracle 10g Interview Questions. Oracle 10g Practice Tests. IT Skills. Management Skills. Communication Skills.
Business Skills. Digital Marketing Skills. Human Resources Skills. Health Care Skills. Finance Skills. All Courses. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might not be able to recover the database.
If the database is open, shut down the database normally if possible. In this case, you will need to recover from the loss of the redo logs Step 8. If you are creating the control file as part of recovery, recover the database. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files. This section describes the most common control file usage errors, and contains the following topics:.
After creating a new control file and using it to open the database, check the alert file to see if Oracle has detected inconsistencies between the data dictionary and the control file, such as a datafile that the data dictionary includes but the control file does not list. If a datafile exists in the data dictionary but not in the new control file, Oracle creates a placeholder entry in the control file under the name MISSING nnnn where nnnn is the file number in decimal.
In this case, you must drop the tablespace containing the datafile. In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle removes references to it from the new control file.
In both cases, Oracle includes an explanatory message in the alert. In this case, you should restore the files you backed up in Step 3 and repeat the procedure from Step 4 , using the correct filenames. You have two options:. This command writes a SQL script to the database's trace file where it can be captured and edited to reproduce the control file. For more information on backing up your control files, depending upon your backup stratagy refer to one of the following books:.
This section presents ways that you can recover your control file from a current backup or from a multiplexed copy.
You can drop control files from the database. For example, you might want to do so if the location of a control file is no longer appropriate. By default, the log file generated for Application Server Control is saved in text format. When you enable ODL for the Application Server Control log files, the logging and diagnostic information is saved in XML format and the contents of the log files are loaded automatically into the Log Repository.
You can then use the Log Repository to search for diagnostic information generated by the Application Server Control. By default, Application Server Control logs information and errors to the following log file in the application server home directory:.
After you perform the following procedure, Application Server Control will instead log information and error messages to the following file, which formats the data according to the ODL standard:. As soon as Application Server Control creates the log. Follow the instructions in the file to replace the default properties with those that are commented by default.
Example A-3 shows the properties in the emiasconsolelogging. Determines the maximum amount of disk space to be used by the log. Determines the maximum size of the log. When the log. When you enable ODL, the resulting log. The file is designed to reach a maximum size, determined by the MaxSegmentSize property described in Table A When the file reaches the predefined maximum size, Application Server Control renames or rolls the logging or trace information to a new file name and starts a new log or trace file.
This process keeps the log file from growing too large. To be sure you have access to important log information, Application Server Control will rollover the log.
When the log file and its rollover files reach this predefined target, Application Server Control deletes the oldest rollover file. As a result, you will often see multiple log files in the log directory. The following example shows three Application Server Control rollover files and the current log file in the log directory:.
If you do not enable ODL, you can still configure the logging properties for the Application Server Control by modifying the following configuration files:. Modify the properties in this file to configure the amount of information saved to the emias. Modify the properties in this file to configure the amount of information saved to the emagent. The following sections provide information on the benefits of running Enterprise Manager in accessibility mode, as well as instructions for enabling accessibility mode:.
Enterprise Manager takes advantage of user interface development technologies that improve the responsiveness of some user operations. For example, when you navigate to a new record set in a table, Enterprise Manager does not redisplay the entire HTML page.
0コメント