SQLcl: MCP Server & SQL Prompt

GOOGLE!!!!!!!

Hello,

Before moving forward, you would need to note following to clear your concepts

1. If you have a request to create a tablespace, you don't need to check existing free space in the database because somehow, you have to create a new tablespace.

2. You can get a query to check free space in the database, but not for all databases in your environment, You would need to run that query in each database individually to find out free space in it.

See following scripts from my blog, these would give you highlight of allocated and free space in your database and some more information as well

Salman - Oracle DBA: Tablespace Growth History and Forecast

http://salmandba.blogspot.sg/2015/01/database-growth-history-and-forecast.html

Salman

salmandba.blogspot.com

Hi, you need to check your freespace on level tablspaces:

select  a.tablespace_name,

       round(a.bytes_alloc / 1024 / 1024) megs_alloc,

       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,

       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,

       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,

       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,

       round(maxbytes/1048576) Max,

      c.status, c.contents

from  ( select  f.tablespace_name,

               sum(f.bytes) bytes_alloc,

               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

        from dba_data_files f

        group by tablespace_name) a,

      ( select  f.tablespace_name,

               sum(f.bytes)  bytes_free

        from dba_free_space f

        group by tablespace_name) b,

      dba_tablespaces c

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name = c.tablespace_name

union all

select h.tablespace_name,

       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,

       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,

       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,

       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,

       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,

       round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1048576)) max,

      c.status, c.contents

from   sys.v_$TEMP_SPACE_HEADER h,

       sys.v_$Temp_extent_pool p,

       dba_temp_files f,

      dba_tablespaces c

where  p.file_id(+) = h.file_id

and    p.tablespace_name(+) = h.tablespace_name

and    f.file_id = h.file_id

and    f.tablespace_name = h.tablespace_name

and f.tablespace_name = c.tablespace_name

group by h.tablespace_name, c.status, c.contents

ORDER BY 1

good luck...

HI

Are using ASM??, I use the following at database level.

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,

fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,

(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,

fs.free_space_mb FREE_SPACE_MB,

ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE

FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,

      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB

      FROM dba_data_files

      GROUP BY tablespace_name) df,

     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,

       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB

       FROM dba_free_space

       GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

ORDER BY fs.tablespace_name;