DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, April 24, 2012

To check the Segments Approaching Max extent in specific tablespaces


set linesize 160
set pagesize 9999
alter session set nls_date_format='dd-mon-RR hh24:mi:ss';
col tbs format a12
col owner format a8
col segment format a30
col type format a12

select tablespace_name tbs,owner, segment_name segment, segment_type type,
initial_extent "init B",next_extent "next B",extents EXTENTS, pct_increase pctinc
from dba_segments
where tablespace_name in ('SHARED','BISD','GMDD','GMDX','WSHD','GLD')
and (next_extent/1024/1024)> (select max(bytes/1024/1024) from dba_free_space where
tablespace_name in ('SHARED','BISD','GMDD','GMDX','WSHD','GLD'))
order by tablespace_name,segment_name;

select tablespace_name tbs,owner, segment_name segment, segment_type type,
initial_extent "init B",next_extent "next B",extents EXTENTS, pct_increase pctinc
from dba_segments
where tablespace_name in ('APPS_TS_SEED')
and (next_extent/1024/1024)> (select max(bytes/1024/1024) from dba_free_space where
tablespace_name in ('APPS_TS_SEED'))
order by tablespace_name,segment_name;

No comments: