Pavan DBA's Blog

The DBA Knowledge Store

Temp segment usage per session

Posted by Pavan DBA on November 3, 2009

The below is a query which is very useful in knowing how much temp usage is being done by each session connected to the database.

SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

4 Responses to “Temp segment usage per session”

  1. I discovered your blog when I was browsing for something entirely different, but this post showed up at the top of Bing your website must be incredibly popular! Keep up the awesome job!

  2. jagat mohanty said

    Steps for patching ORACLE_HOME

    list the opatch lsinventory
    $ORACLE_HOME/OPatch/opatch lsinventory

    shutdown ORCL
    shutdown immediate

    shutdown listener
    lsnrctl stop LISTENER_ORCL

    backup the orainventory
    export ORACLE_HOME=/oracle/app/oracle/product/10.2.4

    cd /oracle/backup_ora/

    tar -cvf orainv_ORCL.tar /oracle/app/oracle/oraInventory

    unset the following
    unset TNS_ADMIN

    set the ORACLE_HOME
    export ORACLE_HOME=/oracle/app/oracle/product/10.2.4
    export PATH=$ORACLE_HOME/bin:$PATH

    –check PATH is set correctly echo $PATH
    –check ORACLE_BASE echo $ORACLE_BASE

    apply the patch
    cd patch_dir

    — echo which opatch

    cd patch

    $ORACLE_HOME/OPatch/opatch apply

    list the opatch lsinventory
    $ORACLE_HOME/OPatch/opatch lsinventory

    start the db and listener
    lsnrctl start LISTENER_ORCL


  3. bharadwaj said

    Can you tell me regarding

    How to create a job manually for analyzing the tables which were modified greater than 10% of rows

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: