Tablespace_回收Temp表空间

た入场券 2022-06-15 00:43 419阅读 0赞

前几天重新建立大数据量表索引的时候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