表空间和数据文件的离线是分开的

1 使表中（所有）数据文件offline时，表空间不会offline

2 离线表空间时，数据文件会offile

SQL> alter database  datafile '/u01/apppp/oradata/ogg1/users01.dbf' offline;

Database altered.

SQL> select tablespace_name , status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

SONG_TS                        ONLINE

TBS_COMPRESS                   ONLINE

TBS_SONG02                     ONLINE

8 rows selected.

SQL> alter database  datafile '/u01/apppp/oradata/ogg1/users01.dbf' online;

alter database  datafile '/u01/apppp/oradata/ogg1/users01.dbf' online

*

ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '/u01/apppp/oradata/ogg1/users01.dbf'

SQL> recover datafile '/u01/apppp/oradata/ogg1/users01.dbf'

Media recovery complete.

SQL> alter database  datafile '/u01/apppp/oradata/ogg1/users01.dbf' online;

Database altered.

SQL> select count(*) from dba_data_files where tablespace_name='USERS';

  COUNT(*)

----------

         1

SQL> select tablespace_name,status,online_status from dba_data_files;

TABLESPACE_NAME                STATUS    ONLINE_

------------------------------ --------- -------

USERS                          AVAILABLE ONLINE

UNDOTBS1                       AVAILABLE ONLINE

SYSAUX                         AVAILABLE ONLINE

SYSTEM                         AVAILABLE SYSTEM

SONG_TS                        AVAILABLE ONLINE

TBS_COMPRESS                   AVAILABLE ONLINE

TBS_SONG02                     AVAILABLE ONLINE

SONG_TS                        AVAILABLE ONLINE

8 rows selected.

SQL> ALTER TABLESPACE users OFFLINE ;

Tablespace altered.

SQL> select tablespace_name,status,online_status from dba_data_files;

TABLESPACE_NAME                STATUS    ONLINE_

------------------------------ --------- -------

USERS                          AVAILABLE OFFLINE

UNDOTBS1                       AVAILABLE ONLINE

SYSAUX                         AVAILABLE ONLINE

SYSTEM                         AVAILABLE SYSTEM

SONG_TS                        AVAILABLE ONLINE

TBS_COMPRESS                   AVAILABLE ONLINE

TBS_SONG02                     AVAILABLE ONLINE

SONG_TS                        AVAILABLE ONLINE

8 rows selected.

SQL> ALTER TABLESPACE users onLINE ;

Tablespace altered.

by song

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-772739/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-772739/