Small knowledge points about tablespace offline
You may want to take a tablespace offline for any of the following reasons:To make a portion of the database unavailable while allowing normal access to the remainder of the databaseTo perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)To make an application and its group of tables temporarily unavailable while updating or maintaining the applicationTo rename or relocate tablespace data files Database log mode Archive ModeAutomatic archival EnabledArchive destination +DATADG/archOldest online log sequence 266Next log sequence to archive 268Current log sequence 268SQL> SQL> alter system switch logfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 1653518336 bytesFixed Size 2253784 bytesVariable Size 1006636072 bytesDatabase Buffers 637534208 bytesRedo Buffers 7094272 bytesDatabase mounted.Database opened.SQL> SQL> alter tablespace users online;Tablespace altered.SQL> select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINEUNDOTBS2 ONLINE6 rows selected.SQL> Test2SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination +DATADG/archOldest online log sequence 267Next log sequence to archive 269Current log sequence 269SQL> alter tablespace users offline immediate; ##arguments immediateTablespace altered.SQL> alter system switch logfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> ORACLE startupinstance started.Total System Global Area 1653518336 bytesFixed Size 2253784 bytesVariable Size 1006636072 bytesDatabase Buffers 637534208 bytesRedo Buffers 7094272 bytesDatabase mounted.Database opened.SQL> alter tablespace users online;alter tablespace users online*ERROR at line 1:ORA-01113: file 4 needs media recoveryORA-01110: data file 4: '+DATADG/orcl/datafile/users.259.954803993'SQL> recover tablespace users;Media recovery complete.SQL> alter tablespace users online;Tablespace altered.SQL> select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINEUNDOTBS2 ONLINE6 rows selected.SQL>
Summary:
The default offline table space performs consistency checkpoints, using the normal parameter to offline the table space.
If the immediate parameter offline is used, a recover operation is required. You can see the experimental process to understand.
