Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: how to get usage statistics for a temp file in oracle?

    Hello Oracle gurus,

    A simple question:

    Does anyone know how to get usage statistics for a temp file in oracle?

    Does anyone know how to move one of these files? I am transitioning back into Oracle from DB2 UDB. This is an Oracle 8i/9i database on Sun Solaris UNIX. Thanks!

    Scott

  2. #2
    Join Date
    May 2003
    Posts
    369

    V$TEMPFILE

    never mind found out

    select * from v$tempfile;
    union
    select * from v$datafile;
    union
    select * from v$session;
    union
    select * from v$database;

  3. #3
    Join Date
    May 2010
    Posts
    4

    Wink

    Run all these queries to get the answer:

    -- displays information about all sort segments in the database
    SELECT A.tablespace_name tablespace, D.mb_total,
    SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
    D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
    FROM v$sort_segment A,
    (
    SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
    FROM v$tablespace B, v$tempfile C
    WHERE B.ts#= C.ts#
    GROUP BY B.name, C.block_size
    ) D
    WHERE A.tablespace_name = D.name
    GROUP by A.tablespace_name, D.mb_total;
    ++++++++++++++++++++++++++++++++++++++++++++++++++ +++
    -- displays information about each database session that is using space in a sort segment

    SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
    S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
    COUNT(*) sort_ops
    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,
    S.program, TBS.block_size, T.tablespace
    ORDER BY sid_serial;


    ++++++++++++++++++++++++++++++++++++++++++++++++++ +++
    -- displays information about each statement that is using space in a sort segment.

    SELECT S.sid || ',' || S.serial# sid_serial, S.username,
    T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
    T.sqladdr address, Q.hash_value, Q.sql_text
    FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
    WHERE T.session_addr = S.saddr
    AND T.sqladdr = Q.address (+)
    AND T.tablespace = TBS.tablespace_name
    ORDER BY S.sid;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •