vertemp

scripts>vertemp

 

set echo off

COL SEGMENT_NAME FORMAT A30
col tablespace_name format a20
col tablespace format a20
col username format a20
col file_name format a50
set lines 120

col FILE_NAME format a50
col TABLESPACE_NAME format a15
col BYTES format 999,999,999,999
col MAXBYTES format 999,999,999,999
set linesize 1000

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;

select FILE_ID,FILE_NAME,TABLESPACE_NAME, BYTES, MAXBYTES from dba_temp_files;

select t.file_name,h.BYTES_USED/1024/1024, h.BYTES_FREE/1024/1024
from dba_temp_files t, V$TEMP_SPACE_HEADER h
where t.file_id=h.file_id
order by 1;

SELECT tablespace_name,
extent_size,
total_extents,
used_extents,
free_extents,
max_used_size,
used_blocks
FROM v$sort_segment;

SELECT s.sid,
s.serial#,
s.username,
u.tablespace,
u.contents,
u.extents,
u.blocks
FROM v$session s,
v$sort_usage u
WHERE s.saddr=u.session_addr
order by s.sid;
Ttitle ‘ [ General usage — v$sort_usage, dba_tablespaces, v$session ses]’ skip 2

set pages 999 lines 100
col username format a15
col mb format 999,999
select su.username
, ses.sid
, ses.serial#
, ses.status
, ses.osuser
, su.tablespace
, ceil((su.blocks * dt.block_size) / 1048576) MB
from v$sort_usage su
, dba_tablespaces dt
, v$session ses
where su.tablespace = dt.tablespace_name
and su.session_addr = ses.saddr
/

 
set echo on

Comment

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 )

Google+ photo

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

Connecting to %s