ORACLE 临时表空间的增删改查：

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。

##. 1.查看临时表空间：
SQL>  select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

TABLESPACE_NAME                               FILE_NAME                                      FILE_SIZE AUT
--------------------------------------------- --------------------------------------------- ---------- ---
TEMP                                          /u01/app/oracle/oradata/ora11g/temp01.dbf             38 YES

SQL> select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;

STATUS  ENABLED    NAME                                           FILE_SIZE
------- ---------- --------------------------------------------- ----------
ONLINE  READ WRITE /u01/app/oracle/oradata/ora11g/temp01.dbf             38

##. 2.改变临时表空间的大小：

SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' resize 20M;

Database altered.
我们通过上面语句参看到，temp01以前是38M 现在已经修改为20M
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

TABLESPACE_NAME                               FILE_NAME                                      FILE_SIZE AUT
--------------------------------------------- --------------------------------------------- ---------- ---
TEMP                                          /u01/app/oracle/oradata/ora11g/temp01.dbf             20 YES

##. 3.扩展临时表空间：
方法一、增大临时文件大小：
SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' resize 20M;
方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' autoextend on next 5m maxsize unlimited;
方法三、向临时表空间中添加数据文件：
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf’ size 100m;

##. 4.创建临时表空间：

SQL> create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf'size 10M;
create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf'size 10M
*
ERROR at line 1:
ORA-01537: cannot add file '/u01/app/oracle/oradata/ora11g/temp01.dbf' - file already part of database
注意：这个报错是因为temp01默认已经有了，所以我们不能再创建temp01

SQL> create temporary tablespace temp02 tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 10M;

Tablespace created.

##. 5.更改系统的默认临时表空间：

--查询默认临时表空间
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace

--修改默认临时表空间

SQL> alter database default temporary tablespace temp2;

Database altered.
我们可以查询是否切换为TEMP2：
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP2                Name of default temporary tablespace

##. 6.删除临时表空间：
--删除临时表空间的一个数据文件：
alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
drop database temp2 including contents and datafiles;

SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 10M;
create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 10M
*
ERROR at line 1:
ORA-01119: error in creating database file '/u01/app/oracle/oradata/ora11g/temp02.dbf'
ORA-27038: created file already exists
Additional information: 1

SQL> drop tablespace temp2 including contents and datafiles;
drop tablespace temp2 including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP2' does not exist

##. 7. 关于查看当前临时表空间使用大小与正在占用临时表空间的sql语句
SQL> col sql_text for a40
SQL> select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  2    from v$sort_usage sort, v$session sess, v$sql sql
  3   where sort.SESSION_ADDR = sess.SADDR
  4     and sql.ADDRESS = sess.SQL_ADDRESS
  5   order by blocks desc;

       SID SEGTYPE           MB SQL_TEXT
---------- --------- ---------- ----------------------------------------
        49 DATA           1.024 select sess.SID, segtype, blocks * 8 / 1
                                000 "MB", sql_text   from v$sort_usage s
                                ort, v$session sess, v$sql sql  where so
                                rt.SESSION_ADDR = sess.SADDR    and sql.
                                ADDRESS = sess.SQL_ADDRESS  order by blo
                                cks desc
 
##. 8.查看临时表空间的使用情况

SELECT temp_used.tablespace_name,
       total - used as "Free",
       total as "Total",
       round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
          FROM GV_$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name;
 
TABLESPACE_NAME                                     Free      Total Free percent
--------------------------------------------- ---------- ---------- ------------
TEMP                                                  16         20           80
TEMP01                                                29         30       96.667

##. 9.查找消耗资源比较的sql语句

Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid;

USERNAME          SID    EXTENTS      SPACE TABLESPACE SEGTYPE   SQL_TEXT
---------- ---------- ---------- ---------- ---------- --------- ----------------------------------------
SYS                49          1    1048576 TEMP       DATA      Select se.username,        se.sid,
                                                                   su.extents,        su.blocks * to_numb
                                                                 er(rtrim(p.value)) as Space,        tabl
                                                                 espace,        segtype,        sql_text
                                                                   from v$sort_usage su, v$parameter p, v
                                                                 $session se, v$sql s  where p.name = 'db
                                                                 _block_size'    and su.session_addr = se
                                                                 .saddr    and s.hash_value = su.sqlhash
                                                                    and s.address = su.sqladdr  order by
                                                                 se.username, se.sid

欢迎大家批评指正：
QQ交流群：300392987
论        坛：