The following is a very useful script to monitor temp space usage inside the database.
#!/usr/bin/ksh
#set up the Oracle environment.
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=/oracle/app/oracle/product/9.2.0; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export
LD_LIBRARY_PATH
tmp_output=/tmp/temp_usage.log
script_name=${0##*/}
echo “”
echo “Script: $script_name”
echo ” started on: `date`”
echo ” by user: `id`”
echo ” on machine: `uname -n`”
echo “”
echo “This script is designed to capture temp space usage information “
echo “”
${ORACLE_HOME}/bin/sqlplus -s <$ORACLE_PASSWD
whenever sqlerror exit 3 rollback
whenever oserror exit 4 rollback
SET SERVEROUTPUT ON
SET FEEDBACK OFF
insert into temp_usage
select (select instance_name from v\$instance) instance_name ,
s.sid ,
s.username,
u.tablespace,
substr(a.sql_text, 1, 1000) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb ,sysdate
from v\$sort_usage u,
v\$session s,
v\$sqlarea a,
v\$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = ‘db_block_size’
/
exit;
EOF_TEMP
if [[ $? -ne 0 ]]; then
echo “*** ERROR: The ${script_name} did not complete successfully.”
exit 1
else
echo “The ${script_name} script appears to have completed successfully on `date`.”
exit 0
fi
