oracle回收临时表空间,oracle 临时表空间的恢复

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.