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.


This was first published in July 2003

Dig deeper on Oracle DBA tools

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close