Database Blog

IDENTIFYING the Activities done in the Tablespace

Find the creation dates of datafiles for the reported tablespace set pagesize 100  select FILE#||’   ‘||CREATION_TIME||’    ‘||TS#||’    ‘||BYTES||’    ‘||NAME from v$datafile where TS# = (select TS# from v$tablespace  where name…

Read More

To check Tablespaces Approaching Max Extents

Approaching Max extents different formula (less than 500 extents to extend) select owner, SEGMENT_NAME, segment_type, tablespace_name, NEXT_EXTENT,PCT_INCREASE, INITIAL_EXTENT, EXTENTS, MAX_EXTENTS  from dba_segments  where tablespace_name= ‘APPLSYSD’ and extents-max_extents<500; select count(*) from…

Read More

To get the Segment Information

select owner, SEGMENT_NAME, segment_type, tablespace_name, NEXT_EXTENT,PCT_INCREASE, INITIAL_EXTENT, EXTENTS, MAX_EXTENTS from dba_segments where tablespace_name= ‘APPLSYSD’ ;

Read More

To check if tablespace is dictionary managed or locally managed

select tablespace_name, extent_management from dba_tablespaces where tablespace_name = <Tablespace_name> order by 1

Read More

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,…

Read More

To Identify all segments for a tablespace

select * from dba_segments where tablespace_name =’ENIX’ and next_extent > (selectmax(BYTES) from dba_free_space where tablespace_name =’ENIX’) order by next_extent;

Read More

To identify the Segments Unable to extend

Segments in TS unable to extend set linesize 150 select tablespace_name||’   ‘||owner||’  ‘||segment_name||’  ‘||next_extent||’   ‘||segment_type||’  ‘||pct_increase from dba_segments where tablespace_name =’&tbs’ and next_extent > (select max(BYTES) from  dba_free_space where tablespace_name…

Read More

To check the SEGMENTS APPROCHING MAX EXTENTS

Identify all objects in the instance that do NOT have maxextents set to “UNLIMITED”Applicable to all database versions –col segment_name format a30 col owner format a20 col segment_type format a20…

Read More

Scripts related to TEMP TABLESPACE

To check instance-wise total allocated, total used TEMP for both rac and non-rac set lines 152 col FreeSpaceGB format 999.999 col UsedSpaceGB format 999.999 col TotalSpaceGB format 999.999 col host_name…

Read More

To find Percentage Usage of Undo Tablespace which considers Expired Space

SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes – a.bytes))/1048576+ u.exp_space),2) as max_free_mb, round(((a.bytes – (NVL(f.bytes,0)+ (1024*1024*u.exp_space)))*100/a.maxbytes),2) used_pct FROM   sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a,…

Read More