Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    29

    Unanswered: Tablespace query

    Does anybody have tablespace freespace query which will give output in terms of maxsize and not size allocated ?
    I had the query but somehow lost it somewhere.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Something like:
    Code:
    select tablespace_name, max(bytes)/1024 as "Largest Free (Kb)", sum(bytes)/1024 as "Total Free (Kb)"
    from dba_free_space
    group by tablespace_name
    Last edited by MCrowley; 10-11-12 at 10:38. Reason: Fixed code tag, fixed quote in query

  3. #3
    Join Date
    Aug 2012
    Posts
    29
    No MCrowley.

    I am looking for one which will use Maxbytes to calculate free percentage

  4. #4
    Join Date
    Aug 2012
    Posts
    29
    created below script ... hope it helps many DBAs ...

    Code:
    column dummy noprint
    column  pct_used format 999.9       heading "% Used" 
    column  name    format a19      heading "Tablespace Name" 
    column  Kbytes   format 999,999,999    heading "MB_Allocated" 
    column  used    format 999,999,999   heading "Used_MB" 
    column  free    format 999,999,999  heading "Free_MB" 
    column  max_size format 999,999,999 heading "Max_MB"
    break   on report 
    compute sum of MB_Allocated on report 
    compute sum of Free_MB on report 
    compute sum of Used_MB on report 
    
    select (select decode(extent_management,'LOCAL','*',' ') || 
                   decode(segment_space_management,'AUTO','a ','m ')
    	      from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name, 
    			 nvl(a.tablespace_name,'UNKOWN')) name, MB_Allocated, 
    	   MB_Allocated-nvl(Free_MB,0) Used_MB,
    	   nvl(Free_MB,0) Free_MB, 
    	   ((MB_Allocated-nvl(Free_MB,0))/nvl(Max_MB,MB_Allocated))*100 pct_used,
    	   nvl(Max_MB,MB_Allocated) max_size
    from ( select sum(bytes)/1048576 Free_MB,
    			  tablespace_name
    	   from  sys.dba_free_space 
    	   group by tablespace_name ) a,
         ( select sum(bytes)/1048576 MB_Allocated, 
    			  sum(maxbytes)/1048576 Max_MB,
    			  tablespace_name 
    	   from sys.dba_data_files 
    	   group by tablespace_name 
    	   union all
          select sum(bytes)/1048576 MB_Allocated, 
    			  sum(maxbytes)/1048576 Max_MB,
    			  tablespace_name 
    	   from sys.dba_temp_files 
    	   group by tablespace_name )b
    where a.tablespace_name (+) = b.tablespace_name
    order by 1
    /
    Last edited by Ninadgarude; 10-12-12 at 08:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •