Re: Tablespace usage

From: jsfromynr <jatinder.1975_at_gmail.com>
Date: 24 Apr 2006 05:43:50 -0700
Message-ID: <1145882630.043993.189880@g10g2000cwb.googlegroups.com>

Hi Robert,

See if this can help you

SELECT t.tablespace_name,

       nvl(u.bytes,0)/1048576 megs_used,
       nvl(f.bytes,0)/1048576 megs_free,
       t.bytes/1048576 megs_total,
       nvl(f.max_bytes,0)/1048576 MAX_BYTES,
       nvl(n.next_extent,0)/1048576 MAX_NEXT_BYTES,
       (nvl(u.bytes,0)*100)/t.bytes pct_used,
       count(*) FRAGMENTS

FROM
(SELECT tablespace_name, sum(bytes) bytes
FROM dba_segments GROUP BY tablespace_name) u,
(SELECT tablespace_name, sum(bytes) bytes,
max(bytes) MAX_BYTES FROM dba_free_space GROUP BY tablespace_name)f,

(SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files a WHERE EXISTS (SELECT 'x' FROM dba_tablespaces b

   WHERE b.tablespace_name = a.tablespace_name and b.status = 'ONLINE')
GROUP BY tablespace_name
UNION
SELECT ts.name,sum(bytes) bytes FROM v$tempfile tm,sys.ts$ ts WHERE tm.ts#=ts.ts# GROUP BY ts.name ) t,
(

SELECT tablespace_name, max(next_extent) next_extent FROM
(

SELECT table_name segment_name,

       'TABLE' segment_type, owner,tablespace_name, initial_extent, next_exten

       min_extents,max_extents,pct_increase FROM dba_tables UNION
SELECt index_name segment_name, 'INDEX' segment_type, owner,tablespace_name,

       initial_extent,next_extent,min_extents,max_extents,pct_increase FROM dba_i
UNION
SELECT segment_name,'ROLLBACK' segment_type,owner,tablespace_name, initial_extent,

       next_extent, min_extents, max_extents,pct_increase FROM dba_rollback_segs
)v_segments
GROUP BY tablespace_name)
n, sys.ts$ a
WHERE t.tablespace_name = u.tablespace_name(+)

  AND t.tablespace_name = f.tablespace_name(+)
  AND t.tablespace_name = n.tablespace_name(+)
  AND t.tablespace_name = a.name

GROUP BY
t.tablespace_name,nvl(u.bytes,0)/1048576,nvl(f.bytes,0)/1048576, t.bytes/1048576,nvl(f.max_bytes,0)/1048576, nvl(n.next_extent,0)/1048576,t.bytes/1048576,(nvl(u.bytes,0)*100)/t.bytes ORDER BY pct_used DESC
/

With Warm regards
Jatinder Singh Received on Mon Apr 24 2006 - 07:43:50 CDT