Tablespace query

[Ninadgarude]

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

[Tim...]

Hi.

I have this:

http://www.oracle-base.com/dba/script.p ... s_full.sql

It's not exactly what you want, but you could amend it I guess.

Cheers

Tim...

[Ninadgarude]

Thanks Tim. I think my requirement is important and logical than the query we normally use for tablespace freespace. I searched on internet and its not available. AT least I couldn't find a query as per my requirement.

[Ninadgarude]

Hi,

Maxsize is the total of maxsize of datafiles in that particular tablespace. That is total of Megabytes allocated to the datafiles.


for example,

alter database datafile 'xyz' size 10G autoextend on maxsize 32G;
alter database datafile 'abc' size 15G autoextend on maxsize 32G;

Normal tablespace scripts including the one on this site is having total of size of datafiles (that is 10g + 15g = 25g).

I am looking for script which will use maxsizes of datafiles (that is 32g + 32g = 64g).

Hope this makes things clearer.

and as autoextend is ON, datafile will grow till its maximum allocated value that is maxsize.

This is more logical as it will give actual percentage of freespace in the tablespace.

[Tim...]

Hi.

Like I said, you can modify it if you want...

Cheers

Tim...

[Ninadgarude]

created below script ... hope it helps many DBAs ...

Code: Select all

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,
      MB_Allocated-nvl(Free_MB,0) used,
      nvl(Free_MB,0) free,
      ((MB_Allocated-nvl(Free_MB,0))/
                    nvl(Max_MB,MB_Allocated))*100 pct_used,
      nvl(Max_MB,MB_Allocated) Max_Size,
      decode( Max_MB, 0, 0, (MB_Allocated/Max_MB)*100) pct_max_used
from ( select sum(bytes)/1048576 Free_MB,
           max(bytes)/1048576 largest,
           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
/

[Tim...]

OK.

[Tim...]

Hi.

Here's my take on it.

http://www.oracle-base.com/dba/monitoring/ts_full.sql

Cheers

Tim...