1 先查看 临时表空间文件有哪个?
v$tempfile.name
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/oracle/oradata/orcl/temp01.dbf
2 查看当前临时表空间是哪个?
SQL> select distinct TEMPORARY_TABLESPACE from dba_users;
TEMPORARY_TABLESPACE
------------------------------
TEMP
3 查看临时表空间和对于的文件名字?
SQL> select TABLESPACE_NAME,FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
TEMP /u01/oracle/oradata/orcl/temp01.dbf
如果临时表空间文件丢失,怎么办?
SQL> ho rm -rf /u01/oracle/oradata/orcl/temp01.dbf
删除临时表空间,但是还是可以进行排序的。
SQL> alter system flush SHARED_POOL;
System altered.
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> alter system flush SHARED_POOL;
System altered.
SQL> alter system flush BUFFER_CACHE;
System altered.
这个时候如果排序数据再大一点就异常了
SQL> select * from tmp_20170409 order by 1,2,3,4,5,6,7,8;
select * from tmp_20170409 order by 1,2,3,4,5,6,7,8
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/oracle/oradata/orcl/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
解决办法:创建另一个临时文件,切换一下临时表空间
4 创建一个好的临时表空间进行替换
4.1 将原来的临时表空间改个名字
alter tablespace temp rename to tempold.dbf;
SQL> alter tablespace temp rename to tempold;
Tablespace altered.
4.2 新建一个新的表空间temp
SQL> create temporary tablespace temp tempfile '/u01/oracle/oradata/orcl/temp1.dbf' size 10M autoextend on;
Tablespace created.
原来的临时表空间temp 已经改tempold ,而且已经创建了一个新的临时表空间temp,下面只需把新建的temp 表空间设置为默认临时表空间
4.3 设置默认临时表空间
SQL> alter database default temporary tablespace temp;
Database altered.
默认表空间已经修复
SQL> select * from tmp_20170409 where rownum < 10 order by 1,2,3,4,5,6,7,8;
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CONSUMER_GROUP
#可以正常排序
4.4 删除原来的已经被改名为tempold的临时表空间
SQL> select * from tmp_20170409 where rownum < 10 order by 1,2,3,4,5,6,7,8;
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CONSUMER_GROUP
被改成tempold 对于的表空间文件已经删除了,所以这里只需要把这个表空间删除就好了
SQL> drop tablespace tempold;
Tablespace dropped.