关于表空间offline小知识点

1、表空间offline的原因Taking Tablespaces OfflineTaking a tablespace offline makes it unavailable for normal access.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  ##上次文档就是因为要重命名数据文件offline了表空间

实验1SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            +DATADG/archOldest online log sequence     266Next log sequence to archive   268Current log sequence           268SQL> SQL> alter tablespace users offline ;  ##默认使用normal参数Tablespace altered.SQL> alter system switch logfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> 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> 

实验2SQL> 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;  ##参数immediateTablespace altered.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> 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> 

小结：
默认offline表空间执行一致性检查点，使用normal参数来offline表空间。
如果使用immediate参数offline表空间需要进行recover操作。大家可以看实验过程了解。