oracle temporary tablespace boom unexpectedly

linux的内核漏洞

compile net-snmp on debian lenny

Large | Medium | Small

[

2009/09/07 08:38 | by askwan ]

临时表空间用途：
INDEX CREATE ,INDEX REBUILD,ORDER BY ,GROUP BY, DISTINCT,Analyze,UNION, Sort-Merge etc..
上面这些操作均会用到临时表空间

临时表空间重建步骤：
比如临时表空间一直增大，导致系统磁盘空间吃紧，考虑重建
1 定位当前临时表空间位置

Quotation

2 创建临时表空间TEMP02

Quotation

SQL> CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/u01/oracle/oradata/askwan/temp02.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;

Tablespace created

review:

Quotation

SQL>  SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_TEMP_FILES;

FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/u01/oracle/oradata/askwan/temp01.dbf                                            TEMP
/u01/oracle/oradata/askwan/temp02.dbf                                            TEMP02

have created successfull.

3 改变缺省临时表空间为TEMP02

Quotation

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

Database altered

4 .删除原缺省表空间temp

Quotation

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped

5.创建新表空间temp

Quotation

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oracle/oradata/askwan/temp.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;

Tablespace created

6. 改变缺省表空间

Quotation

SQL> alter database default temporary tablespace temp;

Database altered

7.删除中间过程表空间TEMP02

Quotation

SQL> drop tablespace temp02 including contents and datafiles;

Tablespace dropped

8.重新指定用户临时表空间

Quotation

SQL> alter user ASKWAN temporary tablespace temp;

User altered

涉及临时文件相关操作
eg.
alter database tempfile '/u01/oracle/oradata/askwan/temp.dbf' autoextend off;
alter database tempfile '/u01/oracle/oradata/askwan/temp.dbf' resize 300M;
alter database tempfile ''/u01/oracle/oradata/askwan/temp.dbf' autoextend on;

作者：askwan@『AskWan』
地址：http://www.askwan.com/oracle_Temporary_tablespace_boom_unexpectedly/
可以转载,转载时请以链接形式注明作者和原始出处及本声明！