Your Oracle 11g database has 6 tablespaces (view Exhibit) in which:
- TEMP is the default temporary tablespace
- UNDOTBS1 is the default undo tablespace
- USERS is the default permanent tablespace
In this database, which three tablespaces can be made offline? (Choose three.)
Exhibit:
A. TEMP
B. PROD
C. USERS
D. SYSAUX
E. SYSTEM
F. UNDOTBS1
Answer: BCD
ONLINE | OFFLINE
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 data files are also offline.
Suggestion:
Before taking a tablespace offline for a long time, consider changing the tablespace allocation of any users who have been assigned the tablespace as either a default or temporary tablespace. While the tablespace is offline, such users cannot allocate space for objects or sort areas in the tablespace. See ALTER USER for more information on allocating tablespace quota to users.ONLINE | OFFLINE Clauses
Use these clauses to take all data files or temp files in the tablespace offline or put them online. These clauses have no effect on the ONLINE or OFFLINE status of the tablespace itself.
The database must be mounted. If tablespace is SYSTEM, or an undo tablespace, or the default temporary tablespace, then the database must not be open.
SYSTEM，undo，default temporary文件offline必须是mounted。
Overview of Tablespaces
用户表空间可以offline，SYSAUX经过测试可以offline，除了UNDO和临时表空间其他都可以设置为PERMANENT表空间
SYSTEM表空间肯定不能offline了
alter tablespace UNDOTBS1 offline
undo表空间可以切换不能直接offline
alter tablespace TEMP offline
文件脱机联机
SQL> ALTER DATABASE TEMPFILE
2 '/u01/app/oracle/oradata/GSP/temp02.dbf' OFFLINE;
Database altered.
SQL> ALTER DATABASE TEMPFILE
2 '/u01/app/oracle/oradata/GSP/temp02.dbf' ONLINE;
Database altered.
默认临时表空间并不能脱机,否则会报错，如下所示
SQL> ALTER TABLESPACE TEMP OFFLINE;
ALTER TABLESPACE TEMP OFFLINE
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE