This script will give you an idea about the existing usage of the TEMP/SORT area. If you are frequently getting outputs for the following SQL, you have a shortage of sort area in memory or you need to fine tune application SQL. To increase sort area, increase the 'sort_area_size' parameter in init.ora file.
set pagesize 10000 set linesize 133 column tablespace format a15 heading 'Tablespace Name' column segfile# format 9,999 heading 'File|ID' column segblk# format 999,999,999 heading 'Block|ID' column blocks format 999,999,999 heading 'Blocks' column username format a15 select b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status from v$session a, v$sort_usage b where a.saddr = b.session_addr order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, PL/SQL, Oracle, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
This was first published in July 2003