oracle 9i前 中的排序受sort_area_size這個(gè)參數(shù)的影響,。
oracle 9i后 pga 管理則主要受pga_aggregate_target 這個(gè)參數(shù)的影響。
如果內(nèi)存無(wú)法容納排序操作,，則oracle使用臨時(shí)表空間作為臨時(shí)空間進(jìn)行排序,，
使用臨時(shí)表就可的排序在oracle中稱為磁盤排序（sort disk），磁盤排序的性能遠(yuǎn)遠(yuǎn)底于內(nèi)存排序（sort memory）
數(shù)據(jù)庫(kù)的排序信息可以通過(guò)動(dòng)態(tài)性能視度v$sysstat查詢得到,。
select name,value from v$sysstat where name like 'sort%';
sorts (memory)634672911
sorts (disk)183
sorts (rows)16008072120
排序使用臨時(shí)表空間的方式和永久表空間不同,，但第一個(gè)使用臨時(shí)表空間的排序開始后，臨時(shí)段被創(chuàng)建,，
區(qū)間被分配到這個(gè)臨時(shí)段中供排序操作使用,，但排序完成后，這個(gè)臨時(shí)段并不回刪除,，oracle會(huì)將這個(gè)臨時(shí)段中的區(qū)間標(biāo)記為free。
其他排序操作可以繼續(xù)使用這個(gè)臨時(shí)段,，也就是說(shuō),，臨時(shí)段中的區(qū)間是一次分配，循環(huán)使用,。
oracle 根據(jù)排序的空間需求,，逐漸分配區(qū)間加入到這個(gè)臨時(shí)段中，
增加的區(qū)間可以通過(guò)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;
-----這樣的一條語(yǔ)句測(cè)試,，千萬(wàn)不要在正式的生產(chǎn)庫(kù)去做。
當(dāng)前正在排序的的用戶信息可以通過(guò)v$sort_usage 視圖得到,。
select * from v$sort_usage;
select * from v$sort_segment;
可以通過(guò)這個(gè)條語(yǔ)句查詢到是那個(gè)用戶,。
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;
----當(dāng)前正在使用temp表空間的的用戶和sql語(yǔ)句,。
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;
-------------
查看當(dāng)前默認(rèn)的temp表空間
select* from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'
我們通過(guò)轉(zhuǎn)儲(chǔ)控制文件可以得到相關(guān)命令,。轉(zhuǎn)儲(chǔ)控制文件的創(chuàng)建語(yǔ)句是
alter database backup controlfile to trace;
在進(jìn)入這個(gè)目錄 user_dump,就可以得到如下語(yǔ)句。
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/oracle/oradata/xjcskfdb/temp104.dbf'
SIZE 10240M REUSE AUTOEXTEND OFF; 等,。,。。,。
-------創(chuàng)建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表空間的數(shù)據(jù)文件
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表空間的數(shù)據(jù)文件,。
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;
一樣可以刪除臨時(shí)表空間，但數(shù)據(jù)文件一般沒(méi)有刪除,，
可以用
drop tablespace temp1 including contents and datafiles;
這樣的話,，數(shù)據(jù)文件也就刪除了。。
------有關(guān)v$sort_usage和v$tempseg_usage
其實(shí)從oracle 9i 開始,，v$sort_usage 視圖就基本不用了,，取而代之的是用v$tempseg_usage 視圖。
這一改變因?yàn)閟ort一詞可能引起誤解,，雖然排序是使用臨時(shí)段的主要操作,，但除了排序外，很多其他操作也會(huì)用到臨時(shí)段,。
使用用v$tempseg_usage更確切些,。
select * from v$tempseg_usage;
select * from dba_objects where object_name=upper('v$tempseg_usage')
可以看出v$tempseg_usage其實(shí)就是一個(gè)同義詞。
select * from dba_synonyms t where t.synonym_name=upper('v$tempseg_usage')
---------------------------------------------
lob 對(duì)象與臨時(shí)段
lob對(duì)象的處理機(jī)制,，對(duì)lob對(duì)象的操作過(guò)程中,，oracle會(huì)生成臨時(shí)的lob數(shù)據(jù)，這部分也會(huì)使用臨時(shí)段,。
-----測(cè)試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;
-------------------------------------------------------
---找出引發(fā)temp的sql語(yǔ)句,。
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;
