works great, thx!
regards Robert
jsfromynr schrieb:
> 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 Wed Apr 26 2006 - 02:15:10 CDT
