一、临时表空间工作原理
用户很多操作都会使用到临时表空间的临时段,最常见的是排序操作。当用户执行排序操作时,有两个区域可以容纳该操作:
1、优先在内存中进行,称为Sort Memory。如果启用自动PGA管理,则受到参数PGA_AGGREGATE_TARGET的影响;如果PGA是手动管理的,则用于排序的内存空间受参数SORT_AREA_SIZE影响。
2、其次使用临时表空间,称为Sort Disk。当内存无法容纳该排序操作时,则会使用到临时表空间进行排序。Sort Memory的性能远高于Sort Disk。
SYS@MTH> select name,value from v$sysstat where name like 'sort%';
NAME VALUE
-------------------- ----------
sorts (memory) 3051115172
sorts (disk) 662
sorts (rows) 2.1834E+10
当第一次使用临时表空间的排序操作开始后,临时段被创建,extent被分配到这个临时段中供排序操作使用;当排序结束后,这个临时段并不会被删除,而是将这个临时段中的extent标记为free,其他排序操作可以继续使用这个临时段。所以,临时段在数据库中是一次分配,循环使用。
在一个数据库实例中,同一个临时表空间只存在一个临时段,Oracle根据排序空间需求,逐渐分配extent到这个临时段中。当数据库重启后,SMON会释放临时段中的extent。
二、重建与切换TEMP表空间
1、首先查看当前的默认TEMP表空间
SYS@MTH> select tablespace_name,file_name,bytes/1024/1024 MB from dba_temp_files;
TABLESPACE_NAME FILE_NAME MB
------------------------------ ------------------------------ ----------
TEMP /oraidx/MTH/temp01.dbf 200
SYS@MTH> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
RMAN TEMP
MTH TEMP
ZYX TEMP
EMST TEMP
DPGS TEMP
EBOARD TEMP
SCOTT TEMP
......
查看TEMP表空间使用情况:
SYS@MTH> select tablespace_name,tablespace_size/1024/1024 tablespace_size,allocated_space/1024/1024 allocated_space,free_space/1024/1024 free_space from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------------- --------------- --------------- ----------
TEMP 200 10 198
2、创建新的临时表空间
SYS@MTH> create temporary tablespace TEMP_NEW tempfile '/oraidx/MTH/temp_new01.dbf' size 20M;
Tablespace created.
3、切换,设置新的临时表空间为默认临时表空间
SYS@MTH> alter database default temporary tablespace TEMP_NEW;
Database altered.
4、检查此时的默认临时表空间
SYS@MTH> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP_NEW
SYS@MTH> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
RMAN TEMP_NEW
MTH TEMP_NEW
ZYX TEMP_NEW
EMST TEMP_NEW
DPGS TEMP_NEW
EBOARD TEMP_NEW
SCOTT TEMP_NEW
FR_ADMIN TEMP_NEW
......
5、查询试图,如果旧的临时表空间没有用户在使用,就可以删除。
SYS@MTH> select /*+ rule*/ distinct a.SID, a.PROCESS, a.SERIAL#, to_char(a.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') login, a.OSUSER,tablespace,b.SQL_TEXT from v$session a, v$sql b, v$sort_usage c where a.SQL_ADDRESS=b.ADDRESS and a.SADDR=c.SESSION_ADDR;
SID PROCESS SERIAL# LOGIN OSUSER TABLESPACE SQL_TEXT
---------- -------- ---------- ------------------- ---------- ---------- ----------------------------------------------------------------------------------------------------
9 19054 2289 2017-05-10 11:33:30 oracle TEMP select /*+ rule*/ distinct a.SID, a.PROCESS, a.SERIAL#, to_char(a.LOGON_TIME,:"SYS_B_0") login, a.OS
USER,tablespace,b.SQL_TEXT from v$session a, v$sql b, v$sort_usage c where a.SQL_ADDRESS=b.ADDRESS a
nd a.SADDR=c.SESSION_ADDR
kill掉这个session,
alter system kill session 'sid,serial#';
重新查询:
SYS@MTH> select /*+ rule*/ distinct a.SID, a.PROCESS, a.SERIAL#, to_char(a.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') login, a.OSUSER,tablespace,b.SQL_TEXT from v$session a, v$sql b, v$sort_usage c where a.SQL_ADDRESS=b.ADDRESS and a.SADDR=c.SESSION_ADDR;
no rows selected
删除旧的临时表空间:
SYS@MTH> drop tablespace TEMP including contents and datafiles;
Tablespace dropped.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30776559/viewspace-2138831/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30776559/viewspace-2138831/