1.准备内存环境
memory_target=0
sga_target=400m
pga_aggregate_target=10m
2.查询当前临时文件位置
select name from v$tempfile;
/u01/app/oracle/oradata/orcl/temp01.dbf
删除掉当前数据库中的临时表空间数据文件
rm -rf /u01/app/oracle/oradata/orcl/temp01.dbf
3.查询大的排序语句
select * from dba_objects order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
4.发现报错
5.给临时表空间添加新文件
alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 100m;
6.删除临时表空间中,丢失的文件
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';
7.再次查询,发现故障排除
select * from dba_objects order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
8.或者发现报错,直接重启数据库
在数据库的报警日志中,会发现如下信息:
Re-creating tempfile /u01/app/oracle/oradata/orcl/temp02.dbf