offline datafile and offline tablespace

1)offline datafile
OFFLINE
Specify OFFLINE to take the datafile offline. If the database is open, you must perform media recovery on the datafile before bringing it back online, because a checkpoint is not performed on the datafile before it is taken offline.

引用

DATA FILE #5:
  (name #4) /u01/oracle/oradata/PROD/example01.dbf
creation size=0 block size=8192 status=0x1c head=4 tail=4 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:75 scn: 0x0000.000e2959 05/04/2011 10:45:17
Stop scn: 0x0000.000e296f 05/04/2011 10:45:30
Creation Checkpointed at scn:  0x0000.00072202 03/12/2011 23:05:50
thread:0 rba:(0x0.0.0)

2)offline tablespace

引用

DATA FILE #4:
  (name #5) /u01/oracle/oradata/PROD/users01.dbf
creation size=0 block size=8192 status=0x80 head=5 tail=5 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:114 scn: 0x0000.000e268b 05/04/2011 10:28:24
Stop scn: 0x0000.000e268b 05/04/2011 10:28:24
Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
thread:0 rba:(0x0.0.0)

Specify ONLINE to bring the tablespace online. Specify OFFLINE to take the tablespace offline and prevent further access to its segments. When you take a tablespace offline, all of its datafiles are also offline.

Restriction on Taking Tablespaces Offline
You cannot take a temporary tablespace offline.

OFFLINE NORMAL
Specify NORMAL to flush all blocks in all datafiles in the tablespace out of the system global area (SGA). You need not perform media recovery on this tablespace before bringing it back online. This is the default.

OFFLINE TEMPORARY
If you specify TEMPORARY, then Oracle Database performs a checkpoint for all online datafiles in the tablespace but does not ensure that all files can be written. Files that are offline when you issue this statement may require media recovery before you bring the tablespace back online.

OFFLINE IMMEDIATE
If you specify IMMEDIATE, then Oracle Database does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace users online;

Tablespace altered.

SQL> alter database datafile '/u01/oracle/oradata/PROD/users02.dbf' offline;

Database altered.

SQL> alter database datafile '/u01/oracle/oradata/PROD/users02.dbf' online;
alter database datafile '/u01/oracle/oradata/PROD/users02.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/oracle/oradata/PROD/users02.dbf'


SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile '/u01/oracle/oradata/PROD/users02.dbf' online;

Database altered.