Wednesday, November 24, 2010

Dropping and Recreating the Temporary Table Space

TABLESPACE usage details:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP                             52428800   52428800

True free space within the used portion of the TEMPFILE

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;

 To find who is using the temporary segment

select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from   sys.v_$session s, sys.v_$sort_usage u
where  s.saddr = u.session_addr
/

select s.osuser, s.process, s.username, s.serial#,
       sum(u.blocks)*vp.value/1024 sort_size
from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
where  s.saddr = u.session_addr
  and  vp.name = 'db_block_size'
  and  s.osuser like '&1'
group  by s.osuser, s.process, s.username, s.serial#, vp.value
/

Create New Temporary Tablespace:
Create new temporary tablespace
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/U01/ORADATA/ TEMP2.DBF' SIZE 200M;
Assign all users to new Tablespace:
Assign all users to this new tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
Find Inactive Sessions:
Now you will find inactive sessions in V$SORT_USAGE table by running below command.
SELECT A2.TABLESPACE, A2.SEGFILE#, A2.SEGBLK#, A2.BLOCKS, A1.SID, A1.SERIAL#, A1.USERNAME, A1.OSUSER, A1.STATUS FROM V$SESSION A1,V$SORT_USAGE A2 WHERE A1.SADDR = A2.SESSION_ADDR;
Kill Inactive Sessions:
Kill the inactive sessions by using SID and SERIAL# from previous resultset.
ALTER SYSTEM KILL SESSION 'SID_NUMBER, SERIAL#';
Drop Previous Tablespace:
Finally drop the previous table space.

No comments:

Post a Comment