Oracle table Space Online and offline (offline)

Database Administrators can have any tablespace online (online) (accessible) or offline (offline) (inaccessible) in the open (open) State of the database in addition to the SYSTEM tablespace (tablespace). The SYSTEM table space is always online when the database is open (open), because Oracle needs to use the data dictionary in the dictionary.

Table spaces (tablespace) are typically online (online) so that database users can access the data in them. However, the database administrator can take the table space offline (offline) while maintaining the tablespace, backing up , or resuming operations.

Make Table space offline

When a tablespace (tablespace) is offline (offline), Oracle does not allow any SQL statements to continue referencing objects in this tablespace (schema object). If an SQL statement in an active transaction (activetransaction) is exactly in use of an object in a tablespace that is set offline, its transactional status is not compromised. Oracle uses deferred rollback segments in the SYSTEM tablespace (deferred rollback segment) to save rollback information (rollback data) for completed SQL statements in this transaction. When the offline table space is reset to the online status, Oracle applies the rollback information as needed.

Table Space (tablespace) online (online) or offline (offline), data dictionary in the SYSTEM tablespace are logged. If a table space is offline when a user closes (shut down) a database, the tablespace remains offline after the next time the database is loaded (mount) and opened (open).

a user can only bring a tablespace (tablespace) online to the database in which it was created, because only the SYSTEM table space in the database holds the necessary data dictionary information for the Table space (dictionary). A table space that is offline (offline) cannot be opened or edited by a tool other than Oracle. Therefore, the offline table space cannot be moved to another database.

When certain errors occur, Oracle automatically switches the associated online tablespace (online tablespace) to the offline (offline) state. For example, Oracle will switch this tablespace to an offline state when the database write process (db writerprocess,dbwn) attempts to write to the tablespace's data file (datafile) more than once. Users who attempt to access data tables in the offline tablespace will get an error message. In the case of media failure, this disk I/O failure causes the user to recover (recover) the affected tablespace after processing the failure.

Table space usage in special cases

Users can store different types of data in separate table spaces (tablespace). When a user makes a table space offline (offline) because of certain tasks, the remaining table spaces remain online (online), where the data remains available to the user. However, the table space can also cause special situations when taken offline. For example, two table spaces are used to store tables and the corresponding indexes for this table, and the following problem occurs: if the table space that contains the index is offline, the query can still access the table data, because access to the table data does not necessarily require index data.

If the table space that contains the table is taken offline, the query will not be able to access the table data because the objects in the offline table space cannot be referenced.

If Oracle is able to get enough information from the online table space (tablespace) to execute an SQL statement, the statement will be executed. If the relevant information must be obtained from the offline table space (offline), the statement will fail.

read-only table space

The primary purpose of using read-only tablespace (Read-only tablespace) is to avoid backup and restore operations on static, large-capacity database content. Oracle does not update data in read-only tablespace, so data files (datafile) for such tablespaces can be placed on read-only media (Read-only media) such as CD-ROM or worm.

Tip: Users can only have one tablespace (tablespace) online to create her database, so read-only tablespace (read-only tablespace) technology is not available for archival work (archiving).

Read-only table space (read-only tablespace) cannot be modified. To update a read-only table space, first make the table space readable and writable. After the update, you can also restore the tablespace to a read-only state.

because read-only tablespace (read-only tablespace) cannot be modified, no duplicate backup operations are required as long as the read-only table space is not put in a writable state. when a user needs to recover a database, there is no need to recover the read-only table space, as described earlier.

This must be done with alter TABLESPACE or MANAGE tablespace system permissions, GRANT ALTER tablespace to "USER";

GRANT MANAGE tablespace to "USER";(double quotes must be capitalized)

You can also take all data files or temporary files offline in a tablespace and then bring them online without affecting the tablespace's own offline or online status.

Reasons for offline table space

Invalidates a portion of the database while allowing normal access to the rest of the database.

Make an offline tablespace backup (although it can be backed up if the tablespace is online and in use)

When you upgrade or maintain an application, the application and its set of tables are temporarily unavailable.

Offline table Space Options

NORMAL

If all data files in the tablespace have no errors, the tablespace can be taken offline properly. A write error causes the tablespace to not be offline for all data files. When you specify offline normal, Oracle checks all data files while they are offline.

Temporary

The tablespace can be temporarily offline, even if one or more data files in the tablespace have errors. When you specify the offline temporary option, Oracle goes offline with data files that are not offline and checks them. If no data files are offline and then taken offline with the temporary option, media recovery is not required when the online tablespace is available. However, if a file with a tablespace is taken offline because of a write error, and then the tablespace is temporarily offline, media recovery is required before the tablespace is brought online.

IMMEDIATE

The tablespace can be taken offline immediately, without any data files being checked by Oracle. In the case of specifying offline immediate, media recovery is required when the online tablespace is in place. If the database is running in Noarchivelog mode, the tablespace cannot be taken offline immediately.

For RECOVER

The database table space in the recovery set is taken offline for point-in-time recovery.

Attention:

If you must have an offline table space, try using the normal option (default). This will ensure that no recovery is required when the tablespace is linked. Although the redo log sequence is reset with the alter DATABASE OPEN resetlogs statement after partial recovery, recovery is still not required. The temporary option is specified only if the table space is not normal offline. In this case, only the files that are offline due to an error are brought online when the table space is restored. The immediate option is only used if both normal and temporary are specified.

Considerations before offline Table space:

• Verify that the tablespace does not contain an active rollback segment so that the tablespace cannot be taken offline.
• You may want to change the location of the tablespace, which the user of this tablespace has specified as the default or temporary tablespace. This is advisable because when offline, they cannot access table space objects and cannot sort the tablespace area (sort areas in the tablespace).

Online table Space

The table space for the Oracle database can be brought back online as long as the database is open. If the table space that you want to bring online is not "clean" offline (that is, the ALTER TABLESPACE offline statement with the normal option), you must first perform a media restore of the tablespace before you go online. Otherwise, Oracle returns an error and keeps the table space offline.

Change the availability of data files or temporary files

The clause of the ALTER TABLESPACE statement can change the online or offline status of all data files or temporary files in the tablespace. Specific statements that affect the online/offline status:

• ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}
• ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}

You only need to enter a tablespace name, not a separate data file or a temporary file. All data files or temporary files are affected, but the tablespace's own online/offline status does not change. In most cases, the above ALTER TABLESPACE statement can be executed as long as the database is mounted, even if it is not open. The database must not open if it is a system tablespace, a redo tablespace, or a default temporary table space. The ALTER DATABASE datafile and ALTER DATABASE tempfile statements also have online/offline clauses, but in those statements you need to enter the file names of all tablespaces. Syntax vs. alter Tablespace for table space usability ... online| The offline statement is different because it is a different operation. The ALTER TABLESPACE statement offline the data file and table space, but cannot be used to alter the temporal tablespace or its temporary files.

Case:

SELECT tablespace_name from Dba_tablespaces

ALTER tablespace users OFFLINE NORMAL;

ALTER tablespace users ONLINE;

Oracle table Space Online and offline (offline)