Read-only tablespaces in Oracle

To make a tablespace read-only in Oracle 19c, you can use the `ALTER TABLESPACE` statement. Follow these steps:

1. Check Tablespace Status
   Before making a tablespace read-only, ensure that no ongoing operations are writing to the tablespace. You can check the status of the tablespace with the following query:
   

   SELECT TABLESPACE_NAME, STATUS
   FROM DBA_TABLESPACES
   WHERE TABLESPACE_NAME = 'YOUR_TABLESPACE_NAME';
   

2. Verify Existing Transactions
   Ensure no active transactions are using the tablespace by checking for active sessions:
   

   SELECT *
   FROM V$LOCKED_OBJECT
   WHERE OBJECT_ID IN (
       SELECT OBJECT_ID
       FROM DBA_OBJECTS
       WHERE TABLESPACE_NAME = 'YOUR_TABLESPACE_NAME');
       

3. Switch to Read-Only Mode
   To make the tablespace read-only, use the following command:
   

   ALTER TABLESPACE YOUR_TABLESPACE_NAME READ ONLY;
   

   Replace YOUR_TABLESPACE_NAME with the name of your tablespace.
4. Confirm Tablespace is Read-Only
   Run the following query to confirm that the tablespace is now in read-only mode:
   

   SELECT TABLESPACE_NAME, STATUS
   FROM DBA_TABLESPACES
   WHERE TABLESPACE_NAME = 'YOUR_TABLESPACE_NAME';
   

The `STATUS` should display as `READ ONLY`.
Notes:

• Archiving Requirement: If your database is in ARCHIVELOG mode, you must back up the tablespace after setting it to read-only to ensure you can recover it in case of a failure.
• Impact on Users: Any attempts to modify data in a read-only tablespace will result in errors for the users.
• Reverting Back: If you need to make the tablespace writable again, use the following command:

  ALTER TABLESPACE YOUR_TABLESPACE_NAME READ WRITE;
  

These steps ensure a smooth transition of your tablespace to read-only status.

If you have large amounts of static data, which is data that doesn't change, then you should consider storing it in a read-only tablespace. A read-only tablespace is one that has been flagged as read-only using the ALTER TABLESPACE statement. Oracle doesn't allow writes to a read-only tablespace, ensuring that the data remains unchanged. An advantage of read-only tablespaces is that these tablespaces need to be backed up only once--immediately after they have been marked read-only. To flag a tablespace as read-only, or to make it read-write again, use the ALTER TABLESPACE statement:

ALTER TABLESPACE tablespace_name READ ONLY;

ALTER TABLESPACE tablespace_name READ WRITE;

The first command, with the READ ONLY keywords, sets the tablespace so that it is read-only. The second command, with the READ WRITE keywords, resets the tablespace so that the tablespace is writeable again.
Because you need to back up read-only tablespaces only once, you can potentially shorten the amount of time needed to back up your database. The gain depends on the size of the tablespace. The larger the tablespace, the more time you will save by not backing it nightly. Flagging a tablespace read-only also ensures that critical reference data is not changed by mistake. In the next lesson, you will learn how to create and make the most of temporary tablespaces.