一：删除临时表空间数据文件
二：创建大表，并对大表进行排序
由于PGA空间有限，所以会使用临时表空间，由于临时表空间的数据文件已经丢失，故会报错
三：重启数据库之后，会自动重建临时数据文件
-
[oracle@hu ~]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Sun May 7 10:54:18 2017
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
-
Connected to:
-
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
-
SQL>
-
SQL>
-
SQL> select file_name from dba_temp_files;
-
-
FILE_NAME
-
--------------------------------------------------------------------------------
-
/u01/app/oracle/oradata/HU/temp01.dbf
-
SQL> ! rm /u01/app/oracle/oradata/HU/temp01.dbf
-
SQL> !ls /u01/app/oracle/oradata/HU/temp01.dbf
-
ls: /u01/app/oracle/oradata/HU/temp01.dbf: No such file or directory
-
- SQL>
二：创建大表，并对大表进行排序
由于PGA空间有限，所以会使用临时表空间，由于临时表空间的数据文件已经丢失，故会报错
-
SQL> create table bigtab as select * from dba_objects;
-
-
Table created.
-
-
SQL> insert into bigtab select * from bigtab;
-
-
86965 rows created.
-
-
SQL> /
-
-
173930 rows created.
-
-
SQL> /
-
-
347860 rows created.
-
-
SQL> commit;
-
-
Commit complete.
-
-
-
SQL> select * from bigtab order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15;
-
select * from bigtab order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
-
*
-
ERROR at line 1:
-
ORA-01116: error in opening database file 201
-
ORA-01110: data file 201: '/u01/app/oracle/oradata/HU/temp01.dbf'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
- SQL>
三：重启数据库之后，会自动重建临时数据文件
-
SQL> shutdown immediate
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SQL> startup
-
ORACLE instance started.
-
-
Total System Global Area 830930944 bytes
-
Fixed Size 2257800 bytes
-
Variable Size 536874104 bytes
-
Database Buffers 289406976 bytes
-
Redo Buffers 2392064 bytes
-
Database mounted.
-
Database opened.
-
SQL> !ls /u01/app/oracle/oradata/HU/temp01.dbf
-
/u01/app/oracle/oradata/HU/temp01.dbf
-
- SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31399171/viewspace-2138615/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
控制文件备份到跟踪文件
下一篇：
丢失 INACTIVE状态 日志
请登录后发表评论
登录
全部评论
