Oracle 临时表空间 temp表空间切换

转载

mb5ff40a36c352c 2013-10-12 23:46:00

文章标签 oracle sed sql 临时表空间表空间 文章分类 代码人生

一.TEMP表空间

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。注意这里的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放。所以Temp表空间可能会越来越大。

排序是很耗资源的，Temp表空间满了,关键是优化你的语句，尽量使排序减少才是上策.

二、oracle temp表空间切换

查询temp表空间、创建temp2表空间5000m、切换临时表空间为 temp2、删除临时表空间temp

oracle@xyy:/home/oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 11 14:18:44 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent"

2 FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM GV$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,

3 (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total

4 WHERE temp_used.tablespace_name = temp_total.tablespace_name;

TABLESPACE_NAME Free Total Free percent

------------------------------ ---------- ---------- ------------

TEMP 0 29 0

SQL> create temporary tablespace temp2 tempfile '/home/oracle/ora11g/oradata/xyy/temp02.dbf' size 5000M autoextend off;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> drop tablespace temp;

Tablespace dropped.

SQL> SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent"

2 FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM GV$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,

3 (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total

4 WHERE temp_used.tablespace_name = temp_total.tablespace_name;

TABLESPACE_NAME Free Total Free percent

------------------------------ ---------- ---------- ------------

TEMP2 4998 5000 99.96

temp表空间的 tempfile 为 temp01.dbf, 删除 temp01.dbf

oracle@xyy:/home/oracle/ora11g/oradata/xyy> ll

total 3297068

-rw-r----- 1 oracle oinstall 9748480 Oct 11 14:31 control01.ctl

-rw-r----- 1 oracle oinstall 9748480 Oct 11 14:31 control02.ctl

-rw-r----- 1 oracle oinstall 328343552 Oct 11 14:31 example01.dbf

-rw-r----- 1 oracle oinstall 536871424 Oct 11 14:31 redo01.log

-rw-r----- 1 oracle oinstall 536871424 Oct 11 14:31 redo02.log

-rw-r----- 1 oracle oinstall 536871424 Oct 11 14:31 redo03.log

-rw-r----- 1 oracle oinstall 566239232 Oct 11 14:31 sysaux01.dbf

-rw-r----- 1 oracle oinstall 744497152 Oct 11 14:31 system01.dbf

-rw-r----- 1 oracle oinstall 5242888192 Oct 11 14:29 temp01.dbf

-rw-r----- 1 oracle oinstall 5242888192 Oct 11 14:21 temp02.dbf

-rw-r----- 1 oracle oinstall 99622912 Oct 11 14:31 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 Oct 11 14:31 users01.dbf

oracle@xyy:/home/oracle/ora11g/oradata/xyy>rm temp01.dbf

查询当前临时表空间

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYS TEMP2

SYSTEM TEMP2

OUTLN TEMP2

MGMT_VIEW TEMP2

FLOWS_FILES TEMP2

MDSYS TEMP2

ORDSYS TEMP2

EXFSYS TEMP2

DBSNMP TEMP2

WMSYS TEMP2

APPQOSSYS TEMP2

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

APEX_030200 TEMP2

OWBSYS_AUDIT TEMP2

ORDDATA TEMP2

CTXSYS TEMP2

ANONYMOUS TEMP2

SYSMAN TEMP2

XDB TEMP2

ORDPLUGINS TEMP2

OWBSYS TEMP2

SI_INFORMTN_SCHEMA TEMP2

OLAPSYS TEMP2

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SCOTT TEMP2

ORACLE_OCM TEMP2

XS$NULL TEMP2

BI TEMP2

PM TEMP2

MDDATA TEMP2

IX TEMP2

SH TEMP2

DIP TEMP2

OE TEMP2

APEX_PUBLIC_USER TEMP2

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

HR TEMP2

SPATIAL_CSW_ADMIN_USR TEMP2

SPATIAL_WFS_ADMIN_USR TEMP2

36 rows selected.

创建临时表空间temp为5000m、切换临时表空间为temp表空间、查询数据库临时表空间使用、删除临时表空间temp2

SQL> create temporary tablespace temp tempfile '/home/oracle/ora11g/oradata/xyy/temp01.dbf' size 5000M autoextend off;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYS TEMP

SYSTEM TEMP

FLOWS_FILES TEMP

MDSYS TEMP

ORDSYS TEMP

EXFSYS TEMP

DBSNMP TEMP

SCOTT TEMP

WMSYS TEMP

ORACLE_OCM TEMP

APPQOSSYS TEMP

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

XS$NULL TEMP

APEX_030200 TEMP

OWBSYS_AUDIT TEMP

BI TEMP

PM TEMP

MDDATA TEMP

IX TEMP

ORDDATA TEMP

CTXSYS TEMP

ANONYMOUS TEMP

SH TEMP

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

OUTLN TEMP

DIP TEMP

OE TEMP

APEX_PUBLIC_USER TEMP

HR TEMP

SYSMAN TEMP

XDB TEMP

SPATIAL_CSW_ADMIN_USR TEMP

SPATIAL_WFS_ADMIN_USR TEMP

ORDPLUGINS TEMP

OWBSYS TEMP

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

MGMT_VIEW TEMP

SI_INFORMTN_SCHEMA TEMP

OLAPSYS TEMP

SQL> drop tablespace temp2;

Tablespace dropped.

oracle@xyy:/home/oracle/ora11g/oradata/xyy>ll