Where is the oracle tablespace located

Oracle Database Concepts. Oracle Database Administrator's Guide. Previous Next JavaScript must be enabled to correctly display this content. An Oracle Database is divided into smaller logical areas of space known as tablespaces.

DBF Stores the data dictionary, including definitions of tables, views, and stored procedures needed by the Oracle Database. DBF Stores undo information. And where is that exactly in the file system?

Improve this question. Add a comment. Active Oldest Votes. It means you use ASM disk. Improve this answer. Alfabravo Alfabravo 7, 6 6 gold badges 43 43 silver badges 78 78 bronze badges. I updated my question. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. However, the database administrator might take a tablespace offline for any of the following reasons: to make a portion of the database unavailable, while allowing normal access to the remainder of the database to perform an offline tablespace backup although a tablespace can be backed up while online and in use to make an application and its group of tables temporarily unavailable while updating or maintaining the application When a Tablespace Goes Offline When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects contained in the tablespace.

Active transactions with completed statements that refer to data in a tablespace that has been taken offline are not affected at the transaction level. Oracle saves rollback data corresponding to statements that affect data in the offline tablespace 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. You cannot take a tablespace offline if it contains any rollback segments that are in use.

If a tablespace was 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, tablespaces cannot be transferred from database to database transfer of Oracle data can be achieved with tools described in Oracle7 Server Utilities.

Oracle automatically changes a tablespace from online to offline when certain errors are encountered for example, when the database writer process, DBWR, fails in several attempts to write to a datafile of the tablespace.

Users trying to access tables in the tablespace with the problem receive an error. Using Tablespaces for Special Procedures By using multiple tablespaces to separate different types of data, the database administrator can also take specific tablespaces offline for certain procedures, while 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, 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, the table data in the database is not accessible because the tables are required to access the data. In summary, if Oracle determines that it has enough information in the online tablespaces to execute a statement, it will do so.

If it needs data in an offline tablespace, then it causes the statement to fail. Read-Only Tablespaces The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Note : Because you can only bring a tablespace online in the database in which it was created, read-only tablespaces are not meant to satisfy archiving or data publishing requirements. Whenever you create a new tablespace, it is always created as read-write.

Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should 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 drop items, such as tables and indexes, from a read-only tablespace, just as you can drop items from an offline tablespace. However, you cannot create or alter objects in a read-only tablespace.

Read-Only vs. Online or Offline Making a tablespace read-only does not change its offline or online status. Offline datafiles cannot be accessed. Bringing a datafile in a read-only tablespace online makes the file readable.

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.