Tablespace_回收Temp表空间

前几天重新建立大数据量表索引的时候temp表空间扩展了好大，一般的temp不会用太多，因此回收temp表空间

SQL>Alter tablespace TEMP coalesce;

ORA-03217:invalid option for alter of TEMPORARY TABLESPACE 

 

Cause: invalid option for alter of temporary tablespace was specified

Action: Specify one of the valid options: ADD TEMPFILE, TEMPFILE ONLINE, TEMPFILE OFFLINE

 

出错原因：Oracle9i 以上本地管理的temp表空间是不能使用 alter database , alter tablespace 来更改的。因为他们 在数据字典中没有参考信息。 也不需要备份tempfile 文件 。 

There is no need to backup the temporary locally manged tablespaces because: 
1. Locally managed tempfiles are always set to NOLOGGING mode. So thus will have no undo. 
2. Extents are managed by bitmap in each datafile to keep track of free or used status of blocks in that datafile.
3. The data dictionary does not manage the tablespace. 
4. Rollback information is not generated because there is no update on the data dictionary 
5. Media recovery does not recognize tempfiles.

 

1）用户缺省表空间查询：

SQL>select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ISGIS                                  TEMP
SCOTT                               TEMP

 

 

2）表空间包含的数据文件：

SQL>select name from v$tempfile;

NAME
-----------------------------------------------
/opt/ora10g/oradata/gis/temp01.dbf

 

 

3）创建新表空间：

SQL>create temporary tablespace TEMP2 TEMPFILE '/opt/oracle/database/oradata/gis/temp02.dbf ' SIZE 512M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
 
Tablespace created

 

4）看哪些用户在使用表间：

SQL>SELECT se.username,sid,serial#,sql_address,machine, program,tablespace,segtype,contents
  2  FROM v$session se,v$sort_usage su
  3  WHERE se.saddr=su.session_addr
  4  Order By machine ;
USERNAME      SID    SERIAL# SQL_ADDRESS MACHINE      PROGRAM       TABLESPACE       SEGTYPE   CONTENTS
------------------------------ ---------- ---------- ----------- ---------

5）修改系统默认表空间及确认：

SQL>alter database default temporary tablespace temp2;
Database altered

SQL>select username,temporary_tablespace from dba_users;
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
XMJL                                  TEMP2
MDDATA                           TEMP2

6）删除旧表空间：

SQL>drop tablespace temp including contents and datafiles;
 Tablespace dropped

其实到此步就应该释放了临时表空间占用的存储空间，为了保持一致，可以删掉这个过渡的表空间，建立和原来名称一致的临时表空间。

 

7）创建原来临时表空间

 
SQL> create temporary tablespace TEMP TEMPFILE '/opt/oracle/database/oradata/gis/temp01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Tablespace created

8）修改系统默认表空间及确认：
SQL> alter database default temporary tablespace temp;
Database altered
 
SQL> select username,temporary_tablespace from dba_users;
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
XMJL                                 TEMP
MDDATA                           TEMP

9）删除中转的临时表空间TEMP2

SQL>drop tablespace temp2 including contents and datafiles;
Tablespace dropped

10）确认临时表空间属性

SQL>select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible from dba_temp_files;
FILE_NAME                                                                        TABLESPACE_NAME                        MB AUTOEXTENSIBLE
-------------------------------------------------------------------------------- ------------------------------ ---------- --------------
/opt/oracle/database/oradata/gis/temp01.dbf                                      TEMP                                  500 YES