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.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2051872/，如需轉載，請註明出處，否則將追究法律責任。