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;
