All the users on the database use the default temporary tablspace. A user issues a query on the ORDERS table and receives the following error:
What would be the most efficient way to rectify this error?
A.Add a new tempfile to the user’s temporary tablespace and drop the tempfile that produced the error.
B.Shut down the database instance, restore the temp01.tmp file from the backup, and then restart the database.
C.Allow the database to continue runn ing, drop the temp01.tmp temporary file, and then re-create it with new tempfiles.
D.Take the temporary tablespace offline, recover the missing tempfile by applying redo logs, and then bring the temporary tablespace online.
Answer: C
正确答案应该是A。
在表空间只有一个数据文件的情况下，是不允许删除数据文件的。
SQL> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
C:\APPS\ORACLE\ORADATA\COW\TEMP02.DBF
SQL> alter tablespace temp drop tempfile 'C:\APPS\ORACLE\ORADATA\COW\TEMP02.DBF';
alter tablespace temp drop tempfile 'C:\APPS\ORACLE\ORADATA\COW\TEMP02.DBF'
*
第 1 行出现错误:
ORA-03261: 表空间 TEMP 只有一个文件
SQL> alter tablespace temp add tempfile 'C:\APPS\ORACLE\ORADATA\COW\TEMP01.DBF' size 100M;
表空间已更改。
SQL> alter tablespace temp drop tempfile 'C:\APPS\ORACLE\ORADATA\COW\TEMP02.DBF';
表空间已更改。
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------
C:\APPS\ORACLE\ORADATA\COW\TEMP01.DBF
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/81227/viewspace-2122329/ ，如需转载，请注明出处，否则将追究法律责任。
