Changing the Write Mode- Tablespaces and Data Files

In environments such as data warehouses, you may need to load data into tables and then never modify the data again. To enforce that no objects in a tablespace can be modified, you can alter the tablespace to be read-only. To do this, use the ALTER TABLESPACE statement:

One advantage of a read-only tablespace is that you have to back it up only once. You should be able to restore the data files from a read-only tablespace no matter how long ago the backup was made.

If you need to modify the tablespace out of read-only mode, you do so as follows:

Make sure you re-enable backups of a tablespace after you place it in read/ write mode.

Note You can’t make a tablespace that contains active rollback segments read-only. For this reason, the SYSTEM tablespace can’t be made read-only because it contains the SYSTEM rollback segment.

Be aware that individual tables can be modified to be read-only. This allows you to control the read-only at a much more granular level (than at the tablespace level); for example,

While in read-only mode, you can’t issue any insert, update, or delete statements against the table. Making individual tables read/write can be advantageous when you’re doing maintenance (such as a data migration) and you want to ensure that users don’t update the data.

This example modifies a table back to read/write mode:

Dropping a Tablespace

If you have a tablespace that is unused, it is best to drop it so it does not clutter your database, consume unnecessary resources, and potentially confuse DBAs who are not familiar with the database. Before dropping a tablespace, it is a good practice to first take it offline:

You may want to wait to see if anybody screams that an application is broken because it can no longer write to a table or index in the tablespace to be dropped. Depending on the reason for dropping a tablespace, objects can be moved to another tablespace first before dropping. When you are sure the tablespace is not required, drop it, and delete its data files:

Tip You can drop a tablespace whether it is online or offline.the exception to this is the SYSTEM and SYSAUX tablespaces, which cannot be dropped. it’s always a good idea to take a tablespace offline before you drop it. by doing so, you can better determine whether an application is using any objects in the tablespace.
if you attempt to query a table in an offline tablespace, you receive this error: ORA-00376: file can’t be read at this time.

Dropping a tablespace using INCLUDING CONTENTS AND DATAFILES permanently removes the tablespace and any of its data files. Make certain the tablespace does not contain any data you want to keep before you drop it.

If you attempt to drop a tablespace that contains a primary key that is referenced by a foreign key associated with a table in a tablespace different from the one you are trying to drop, you receive this error:

Run this query first to determine whether any foreign key constraints will be affected:

If there are referenced constraints, you need to first drop the constraints or use the CASCADE CONSTRAINTS clause of the DROP TABLESPACE statement. This statement uses CASCADE CONSTRAINTS to drop any affected constraints automatically:

This statement drops any referential integrity constraints from tables outside the tablespace being dropped that reference tables within the dropped tablespace.

If you drop a tablespace that has required objects in a production system, the results can be catastrophic. You must perform some sort of recovery to get the tablespace and its objects back. Needless to say, be careful when dropping a tablespace.