Problem solve Get help with specific problems with your technologies, process and projects.

Find out about TEMP/SORT area usage

This script will give you an idea about the existing usage of the TEMP/SORT area.

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.

Dig Deeper on Oracle DBA tools

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.