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 LD_LIBRARY_PATH
    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

    unzip patch.zip
    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

    startup

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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: