DBA Extents Segments - Sample SQL

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


SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name


select s.osuser, s.process, s.username, s.serial#, sum(u.blocks)*vp.value/1024 sort_sizefrom sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vpwhere 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

-- Free Table Space

SELECT /* + RULE */ df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,sum(fs.bytes)/(1024*1024) free_ts_size,nvl(round(sum(fs.bytes)*100/df.bytes),1) ts_pct,round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1 FROM dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) dfWHERE fs.tablespace_name(+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytesunion all

SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes/(1024*1024) tot_ts_size, sum(df.bytes_free)/(1024*1024) free_ts_size, nvl(round((sum(fs.bytes)- df.bytes_used) *100/fs.bytes),1) ts_pct, round((sum(fs.BYTES) - df.BYTES_free )*100/fs.bytes) ts_pct1FROM dba_temp_files fs , (select tablespace_name, bytes_free, bytes_used from V$temp_space_header group by tablespace_name, bytes_free, bytes_used ) dfWHERE fs.tablespace_name(+) = df.tablespace_nameGROUP BY df.tablespace_name, fs.bytes, df.bytes_free,df.BYTES_usedorder by 4 desc

--Table spaces

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

--Rows for each statement using sort segment space:

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