自动tempfile恢复

一：删除临时表空间数据文件

1. [oracle@hu ~]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.4.0 Production on Sun May 7 10:54:18 2017
   
4. 
   
5. Copyright (c) 1982, 2013, Oracle. All rights reserved.
   
6. 
   
7. 
   
8. Connected to:
   
9. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
11. 
    
12. SQL>
    
13. SQL>
    
14. SQL> select file_name from dba_temp_files;
    
15. 
    
16. FILE_NAME
    
17. --------------------------------------------------------------------------------
    
18. /u01/app/oracle/oradata/HU/temp01.dbf
    
19. SQL> ! rm /u01/app/oracle/oradata/HU/temp01.dbf
    
20. SQL> !ls /u01/app/oracle/oradata/HU/temp01.dbf
    
21. ls: /u01/app/oracle/oradata/HU/temp01.dbf: No such file or directory
    
22. 
    
23. SQL>

二：创建大表，并对大表进行排序
由于PGA空间有限，所以会使用临时表空间，由于临时表空间的数据文件已经丢失，故会报错

1. SQL> create table bigtab as select * from dba_objects;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into bigtab select * from bigtab;
   
6. 
   
7. 86965 rows created.
   
8. 
   
9. SQL> /
   
10. 
    
11. 173930 rows created.
    
12. 
    
13. SQL> /
    
14. 
    
15. 347860 rows created.
    
16. 
    
17. SQL> commit;
    
18. 
    
19. Commit complete.
    
20. 
    
21. 
    
22. SQL> select * from bigtab order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15;
    
23. select * from bigtab order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
    
24.               *
    
25. ERROR at line 1:
    
26. ORA-01116: error in opening database file 201
    
27. ORA-01110: data file 201: '/u01/app/oracle/oradata/HU/temp01.dbf'
    
28. ORA-27041: unable to open file
    
29. Linux-x86_64 Error: 2: No such file or directory
    
30. Additional information: 3
    
31. 
    
32. SQL>

三：重启数据库之后，会自动重建临时数据文件

1. SQL> shutdown immediate
   
2. Database closed.
   
3. Database dismounted.
   
4. ORACLE instance shut down.
   
5. SQL> startup
   
6. ORACLE instance started.
   
7. 
   
8. Total System Global Area 830930944 bytes
   
9. Fixed Size 2257800 bytes
   
10. Variable Size 536874104 bytes
    
11. Database Buffers 289406976 bytes
    
12. Redo Buffers 2392064 bytes
    
13. Database mounted.
    
14. Database opened.
    
15. SQL> !ls /u01/app/oracle/oradata/HU/temp01.dbf
    
16. /u01/app/oracle/oradata/HU/temp01.dbf
    
17. 
    
18. SQL>

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

转载于:http://blog.itpub.net/31399171/viewspace-2138615/