tablespace sizing script for oracle 12c..

Consider only select statement inside the PLSQL...


create table PROD_DB_SIZE_DETAILS (INSTANCENAME                  varchar2(40),

                                   TABLESPACE_NAME               varchar2(40) ,

                                   MAX_SIZE_GB                   number(10,3) ,

                                   ALLOC_SPACE_GB                number(10,3) ,

                                   USED_SPACE_GB                 number(10,3) ,

                                   USED_PERCENT_ALLOC            number(10,3) ,

                                   USED_PERCENT_MAXSIZE          number(10,3) ,

                                   FREE_SPACE_TO_ALLOC_GB        number(10,3) ,

                                   FREE_SPACE_PERCENT_ALLOC      number(10,3) ,

                                   CON_ID                        number(10) ,

                                   RUN_DATE                      DATE

                                   );

                               

drop table PROD_DB_SIZE_DETAILS;

delete from PROD_DB_SIZE_DETAILS;

commit;

select * from PROD_DB_SIZE_DETAILS;





==============================================================

DECLARE

tab_count  number(10,2);

sql_stmt VARCHAR2(10000);

begin



sql_stmt := 'insert into PROD_DB_SIZE_DETAILS select INSTANCENAME,tablespace_name,MAX_SIZE_GB,ALLOC_SPACE_GB,USED_SPACE_GB,USED_PERCENT_ALLOC,USED_PERCENT_MAXSIZE,FREE_SPACE_TO_ALLOC_GB,FREE_SPACE_PERCENT_ALLOC,CON_ID,TRUNC(SYSDATE) from

(select a.TABLESPACE_NAME,round((a.max_size/1024/1024/1024),3) MAX_SIZE_GB,

  ROUND(a.TOTSIZE/1024/1024/1024,3) as ALLOC_SPACE_GB ,

  ROUND((b.used)/1024/1024/1024,3) as Used_SPACE_GB ,

  ROUND(((b.used)/1024/1024/1024)/(a.TOTSIZE/1024/1024/1024)*100,3) as USED_PERCENT_ALLOC,

  ROUND(((b.used)/1024/1024/1024)/(a.max_size/1024/1024/1024)*100,3) as USED_PERCENT_MAXSIZE,

  ROUND((((a.totsize)/1024/1024/1024)- ((b.used)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,

  ROUND((((a.TOTSIZE  - (B.USED)) / a.TOTSIZE) * 100),0) as  FREE_SPACE_PERCENT_ALLOC,a.con_id,

  case when  a.con_id=1  then (select name from v$database@DB_STRING)

  when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

FROM

  (SELECT tablespace_name,con_id,    SUM(bytes) totsize,sum(maxbytes) max_size

  FROM cdb_data_files@DB_STRING

  GROUP BY tablespace_name,con_id

  ) a,

  (SELECT tablespace_name,    SUM(bytes) used,con_id

  FROM cdb_segments@DB_STRING

  GROUP BY tablespace_name,con_id

  ) b

where a.TABLESPACE_NAME=B.TABLESPACE_NAME

and a.con_id=b.con_id



union



select  a.tablespace_name, (round((a.maxbytes/1024/1024/1024),3)) MAX_SIZE_GB, (round((a.bytes/1024/1024/1024),3))  ALLOC_SPACE_GB,

(round(((b.used_space*c.block_size)/1024/1024/1024),3))  Used_SPACE_GB,

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)) USED_PERCENT_ALLOC,

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)) USED_PERCENT_MAXSIZE,

 ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,

 ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),0) as  FREE_SPACE_PERCENT_ALLOC,a.con_id,

 case when  a.con_id=1  then (select name from v$database@DB_STRING)

 when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

from

cdb_temp_files@DB_STRING  a,cdb_tablespace_usage_metrics@DB_STRING b,cdb_tablespaces@DB_STRING c

where a.tablespace_name=b.tablespace_name

and b.tablespace_name=c.tablespace_name

and a.con_id=b.con_id

and a.tablespace_name  in (''TEMP'')

group by a.con_id,a.tablespace_name,(round((a.maxbytes/1024/1024/1024),3)),(round((a.bytes/1024/1024/1024),3)),

(round(((b.used_space*c.block_size)/1024/1024/1024),3)),

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)),

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)),

ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3) ,

ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),0)



union



select  a.tablespace_name, (round((a.maxbytes/1024/1024/1024),3)) MAX_SIZE_GB, (round((a.bytes/1024/1024/1024),3))  ALLOC_SPACE_GB,

(round(((b.used_space*c.block_size)/1024/1024/1024),3))  Used_SPACE_GB,

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)) USED_PERCENT_ALLOC,

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)) USED_PERCENT_MAXSIZE,

 ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,

 ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),3) as  FREE_SPACE_PERCENT_ALLOC,a.con_id,

 case when  a.con_id=1  then (select name from v$database@DB_STRING)

when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

from

cdb_data_files@DB_STRING  a,cdb_tablespace_usage_metrics@DB_STRING b,cdb_tablespaces@DB_STRING c

where a.tablespace_name=b.tablespace_name

and b.tablespace_name=c.tablespace_name

and a.con_id=b.con_id

and a.tablespace_name not like (''TEMP'')

and a.tablespace_name not in (select distinct tablespace_name from cdb_segments@DB_STRING)

group by a.con_id,a.tablespace_name,(round((a.maxbytes/1024/1024/1024),3)),(round((a.bytes/1024/1024/1024),3)),

(round(((b.used_space*c.block_size)/1024/1024/1024),3)),

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)),

(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)),

ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3) ,

ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),3))';



execute immediate  sql_stmt;

commit;

end;



=================================================================



Hope this helps....