Oracle Table Space Status

1. Tablespace read-only

To view the current table space status

[Email Protected]>column file_nameformat A60[email protected]>columntablespace_name format A20[email protected]>SelectTablespace_name,file#,file_name, v.status,v.enabled fromDba_data_files D,v$datafile VwhereD.file_id=V.file#; Tablespace_nameFILE#file_nameSTATUS ENABLED-------------------- ---------- ------------------------------------------------------------ ------------------- -- ------------------------------SYSTEM1  　　　　　　 /U01/App/Oracle/Oradata/UserData/SYSTEM01.DBF SYSTEMREADWRITEUNDOTBS12  　　　　　　 /U01/App/Oracle/Oradata/UserData/UNDOTBS01.DBF ONLINEREADWritesysaux3  　　　　　　 /U01/App/Oracle/Oradata/UserData/SYSAUX01.DBF ONLINEREADwriteusers4 　　　　　　 /U01/App/Oracle/Oradata/UserData/USERS01.DBF ONLINEREADWriteexample5           /U01/App/Oracle/Oradata/UserData/EXAMPLE01.DBF ONLINEREADWRITE

Set the users table space to read-only, attempting to modify a read-only tablespace

[Email Protected]>column file_nameformat A60[email protected]>columntablespace_name format A20[email protected]>AlterTablespace usersRead  only; [Email protected]>SelectTablespace_name,file#,file_name, v.status,v.enabled fromDba_data_files D,v$datafile VwhereD.file_id=V.file#; Tablespace_nameFILE#file_nameSTATUS ENABLED-------------------- ---------- ------------------------------------------------------------ ------------------- -- ------------------------------SYSTEM1  　　　　　　 /U01/App/Oracle/Oradata/UserData/SYSTEM01.DBF SYSTEMREADWRITEUNDOTBS12  　　　　　　 /U01/App/Oracle/Oradata/UserData/UNDOTBS01.DBF ONLINEREADWritesysaux3  　　　　　　 /U01/App/Oracle/Oradata/UserData/SYSAUX01.DBF ONLINEREADwriteusers4  　　　　　　 /U01/App/Oracle/Oradata/UserData/USERS01.DBF ONLINEREAD  onlyEXAMPLE5           /U01/App/Oracle/Oradata/UserData/EXAMPLE01.DBF ONLINEREADWRITE
[Email protected]>UpdateScott.empSetSal=Sal+1;UpdateScott.empSetSal=Sal+1             *ERROR at line1: ORA-00372:file 4cannot is modified at this Timeora-01110: Datafile 4:'/u01/app/oracle/oradata/userdata/users01.dbf'

To reset the tablespace to read-only

SYS@userdata>alterread write;

2. Table Space Offline

SYS@userdata>Altertablespace users offline; Tablespace altered. SYS@userdata>SelectTablespace_name,file#,file_name, v.status,v.enabled fromDba_data_files D,v$datafile VwhereD.file_id=V.file#; Tablespace_nameFILE#file_nameSTATUS ENABLED-------------------- ---------- ------------------------------------------------------------ ------------------- -- ------------------------------SYSTEM1  　　　　　　 /U01/App/Oracle/Oradata/UserData/SYSTEM01.DBF SYSTEMREADWRITEUNDOTBS12  　　　　　　 /U01/App/Oracle/Oradata/UserData/UNDOTBS01.DBF ONLINEREADWritesysaux3 　　　　　　 /U01/App/Oracle/Oradata/UserData/SYSAUX01.DBF ONLINEREADwriteusers4  　　　　　　 /U01/App/Oracle/Oradata/UserData/users01.dbf OFFLINE disabledexample5           /U01/App/Oracle/Oradata/UserData/EXAMPLE01.DBF ONLINEREADWritesys@userdata>UpdateScott.empSetSal=Sal+1;UpdateScott.empSetSal=Sal+1             *ERROR at line1: ORA-00376:file 4Cannot beReadAt this Timeora-01110: Datafile 4:'/u01/app/oracle/oradata/userdata/users01.dbf'SYS@userdata>Altertablespace users online; Tablespace altered.

Oracle Table Space Status