IZ0-053 Q699(临时表空间临时文件被删除恢复)

699. Your database is using a default temporary tablespace that contains the temp01.tmp temporary file.
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 running, 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:A


SQL> select * from v$tempfile;
     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         1           925793 02-MAR-16          3          1 ONLINE  READ WRITE
  30408704       3712     20971520       8192
/u01/app/oracle/oradata/inst1/temp01.dbf
SQL> conn scott/tiger
Connected.
SQL> show user;
USER is "SCOTT"
SQL> create global temporary table test_del(col1 varchar2(20)) on commit preserve rows;
Table created.
SQL> host rm /u01/app/oracle/oradata/inst1/temp01.dbf
SQL> insert into test_del values('a');
insert into test_del values('a')
            *
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/inst1/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


接下来恢复临时表空间：
SQL> alter tablespace temp offline;  
alter tablespace temp offline
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/inst1/temp01.dbf';
ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/inst1/temp01.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TEMP has only one file

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/inst1/temp02.dbf' size 50m reuse autoextend on next 1m maxsize 500m;
Tablespace altered.

SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/inst1/temp01.dbf';
Tablespace altered.

SQL> insert into test_del values('a');
1 row created.

可以看到，临时表空间临时文件的临时文件被删除，可以通过先增加新的临时文件，再drop原被删除的临时文件的方式来恢复。

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

转载于:http://blog.itpub.net/8520577/viewspace-2051872/