How to set table space read-only in Oracle

In Oracle, you can use the "alter tablespace tablespace name read only" statement to set the table space to read-only; the alter tablespace statement is used to modify the data of the table space. When the table space is in the read-only state, the The data will not change and the efficiency of the system can be improved.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

Oracle how to set table space read-only

alter tablespace 表空间名 read only;

When a table space is in a read-only state, only read operations can be performed, so the data in the data file will not change, so Redo logs will not be protected and no redo operations will occur. Therefore, being in this state can improve the efficiency of the system.

When it was just changed to the read-only state, the table space was still in the intermediate state. It was set to the read-only state after all transactions were completed. When a table space is modified to a read-only state, the system generates a checkpoint. Of course, you can delete objects in the read-only table space, such as tables, indexes, etc. Because the command to delete an object is a DDL statement, it only modifies the data dictionary rather than the data file.

Examples are as follows:

1. View the dba_tablespaces structure

SQL> desc dba_tablespaces;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                   NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                          VARCHAR2(9)
 CONTENTS                                        VARCHAR2(9)
 LOGGING                                          VARCHAR2(9)
 FORCE_LOGGING                                  VARCHAR2(3)
 EXTENT_MANAGEMENT                           VARCHAR2(10)
 ALLOCATION_TYPE                                VARCHAR2(9)
 PLUGGED_IN                                      VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                   VARCHAR2(6)
 DEF_TAB_COMPRESSION                          VARCHAR2(8)
 RETENTION                                      VARCHAR2(11)
 BIGFILE                                          VARCHAR2(3)

2. Query the status of the XUANXUAN table space

SQL> select  TABLESPACE_NAME, STATUS, CONTENTS from dba_tablespaces where TABLESPACE_NAME like 'XUANXUAN';
 
TABLESPACE_NAME                STATUS    CONTENTS                               
------------------------------ --------- ---------                              
XUANXUAN                       ONLINE    PERMANENT

Judging from the query results, it is online state.

3. Modify the XUANXUAN table space to read-only status.

SQL> alter tablespace XUANXUAN read only;

The table space has been changed.

4. After modification, query to see if the modification has been successful.

SQL> select  TABLESPACE_NAME, STATUS, CONTENTS from dba_tablespaces where TABLESPACE_NAME like 'XUANXUAN';
 
TABLESPACE_NAME                STATUS    CONTENTS                               
------------------------------ --------- ---------                              
XUANXUAN                       READ ONLY PERMANENT

5. If you want to change it back to the readable and writable state, you need to execute the following command.

SQL> alter tablespace XUANXUAN read write;

The table space has been changed.

6. After modification, query to see if the modification has been successful.

SQL> select  TABLESPACE_NAME, STATUS, CONTENTS from dba_tablespaces where TABLESPACE_NAME like 'XUANXUAN';
 
TABLESPACE_NAME                STATUS    CONTENTS                               
------------------------------ --------- ---------                              
XUANXUAN                       ONLINE    PERMANENT

The results show that the table space XUANXUAN is already online.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to set table space read-only in Oracle. For more information, please follow other related articles on the PHP Chinese website!