临时表空间总结

oracle 9i前 中的排序受sort_area_size这个参数的影响。

oracle 9i后 pga 管理则主要受pga_aggregate_target 这个参数的影响。

 如果内存无法容纳排序操作，则oracle使用临时表空间作为临时空间进行排序，

使用临时表就可的排序在oracle中称为磁盘排序（sort disk），磁盘排序的性能远远底于内存排序（sort memory）

   数据库的排序信息可以通过动态性能视度v$sysstat查询得到。

select name,value from v$sysstat where name like 'sort%';

sorts (memory)634672911

sorts (disk)183

sorts (rows)16008072120

 排序使用临时表空间的方式和永久表空间不同，但第一个使用临时表空间的排序开始后，临时段被创建，

区间被分配到这个临时段中供排序操作使用，但排序完成后，这个临时段并不回删除，oracle会将这个临时段中的区间标记为free。

其他排序操作可以继续使用这个临时段，也就是说，临时段中的区间是一次分配，循环使用。

 oracle 根据排序的空间需求，逐渐分配区间加入到这个临时段中，

增加的区间可以通过v$sort_segment 中的ADDED_EXTENTS字段查询到。

 select t.ADDED_EXTENTS from v$sort_segment t;

 ----可以用   select * from dba_tab_columns a,dba_tab_columns b order by a.OWNER,b.TABLE_NAME;

-----这样的一条语句测试，千万不要在正式的生产库去做。

 当前正在排序的的用户信息可以通过v$sort_usage 视图得到。

 select * from v$sort_usage;

select * from v$sort_segment;

  可以通过这个条语句查询到是那个用户。

select b.tablespace,b.blocks,a.sid,a.serial#,a.USERNAME,a.STATUS 

from v$session a,v$sort_usage b where a.SADDR=b.SESSION_ADDR；

 select * from v$sqltext order by piece;

 select  * from v$latchname where name like 'sort%';

select * from v$latch where latch#=184;

 select * from v$temp_extent_pool;

 --查询temp表空间的大小

select sum(bytes)/1024/1024 from v$tempfile;

select sum(bytes)/1024/1024 from dba_temp_files;

 ----当前正在使用temp表空间的的用户和sql语句。

 select  distinct a.sid,a.process,a.serial#,to_char(a.logon_time,'yyyy-mm-dd hh24:mi:ss'), 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;

 select /*+ rule */  distinct a.sid,a.process,a.serial#,to_char(a.logon_time,'yyyy-mm-dd hh24:mi:ss'), 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;

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

 查看当前默认的temp表空间

 select* from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

 

 我们通过转储控制文件可以得到相关命令。转储控制文件的创建语句是

 alter database backup controlfile to trace;

 

在进入这个目录 user_dump,就可以得到如下语句。

ALTER TABLESPACE TEMP3 ADD TEMPFILE '/oracle/oradata/xjcskfdb/temp104.dbf'

     SIZE 10240M REUSE AUTOEXTEND OFF; 等。。。。

     

  -------创建temp表空间

---CREATE TEMPORARY TABLESPACE temp1

TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP00.DBF' SIZE 10m

AUTOEXTEND ON

NEXT 2m MAXSIZE 2048m

EXTENT MANAGEMENT LOCAL;

 

----添加temp表空间的数据文件

alter tablespace temp  

 add tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP001.DBF' size 10m

autoextend on next 2m maxsize 1024m extent management local;

 

ALTER TABLESPACE "TEMP" 

    ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP001.DBF' 

    SIZE 5M autoextend on next 2m maxsize 1024m extent management local;

 

----改变temp表空间

alter database default temporary tablespace temp1;

select * from dba_users;

 

--删除temp表空间的数据文件。

alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF' offline;

alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF' drop;

 

---删除表空间

 

drop tablespace temp;

 

一样可以删除临时表空间，但数据文件一般没有删除，

可以用

drop tablespace temp1 including  contents and datafiles;

这样的话，数据文件也就删除了。。

 

------有关v$sort_usage和v$tempseg_usage

其实从oracle 9i 开始，v$sort_usage 视图就基本不用了，取而代之的是用v$tempseg_usage 视图。

这一改变因为sort一词可能引起误解，虽然排序是使用临时段的主要操作，但除了排序外，很多其他操作也会用到临时段。

使用用v$tempseg_usage更确切些。

 

 select * from v$tempseg_usage;

  select * from dba_objects where object_name=upper('v$tempseg_usage')  

 可以看出v$tempseg_usage其实就是一个同义词。

 

 select * from dba_synonyms t where  t.synonym_name=upper('v$tempseg_usage')  

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

 lob 对象与临时段

 lob对象的处理机制，对lob对象的操作过程中，oracle会生成临时的lob数据，这部分也会使用临时段。

 

-----测试lob

 --session1

 declare

 A CLOB;

 BEGIN

 A:='ABC';

 DBMS_LOCK.SLEEP(120);

 END;

 /

-- session2

 select  s.USERNAME,s.SID,u.TABLESPACE,u.CONTENTS,u.SEGTYPE,round(u.BLOCKS*8192/1024/1024,2) mb

  from v$session s ,v$tempseg_usage u

 where s.SADDR=u.SESSION_ADDR and u.CONTENTS='TEMPORARY' ORDER BY mb desc;

 

 select * from v$sort_usage;

 

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

  ---找出引发temp的sql语句。

select /*+ rule */  distinct a.sid,a.process,a.serial#,

to_char(a.logon_time,'yyyy-mm-dd hh24:mi:ss'), 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;