Tablespace query

All posts relating to Oracle database administration.

Moderator: Tim...

Tablespace query

Postby Ninadgarude » Thu Oct 11, 2012 5:39 am

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
Ninadgarude
Member
 
Posts: 22
Joined: Thu Aug 09, 2012 11:09 am

Re: Tablespace query

Postby Tim... » Thu Oct 11, 2012 7:45 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Tablespace query

Postby Ninadgarude » Thu Oct 11, 2012 9:12 am

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
Member
 
Posts: 22
Joined: Thu Aug 09, 2012 11:09 am

Re: Tablespace query

Postby Ninadgarude » Thu Oct 11, 2012 12:55 pm

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.
Ninadgarude
Member
 
Posts: 22
Joined: Thu Aug 09, 2012 11:09 am

Re: Tablespace query

Postby Tim... » Thu Oct 11, 2012 5:44 pm

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Tablespace query

Postby Ninadgarude » Fri Oct 12, 2012 10:42 am

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
/
Ninadgarude
Member
 
Posts: 22
Joined: Thu Aug 09, 2012 11:09 am

Re: Tablespace query

Postby Tim... » Fri Oct 12, 2012 10:49 am

OK. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Tablespace query

Postby Tim... » Sat Oct 13, 2012 5:20 pm

Hi.

Here's my take on it.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest