Oracle Database Size


  • Database Size of Container Database
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB" from ( select sum(bytes)/1024/1024 data_size
from dba_data_files) a,( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

  • Database Size of Pluggable Database
select a.data_size+b.temp_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b;

  • Database  size of cdb with all pdbs

select con_id,name,SUM(SIZE_MB) from 
(
select  c.con_id,nvl(p.name, 'CDB') name, sum(bytes)/1024/1024 SIZE_MB from  cdb_data_files c,  v$pdbs p where c.con_id=p.con_id(+) GROUP BY c.con_id,name
UNION
select  c.con_id,nvl(p.name, 'CDB') name , sum(bytes)/1024/1024 SIZE_MB from  cdb_temp_files c,  v$pdbs p where c.con_id=p.con_id(+) GROUP BY c.con_id,name
)group by con_id,name
order by con_id;

  • Database used and free space 

select
"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)",
"Free_Space(GB)"
from(
select 
(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",
(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"
from dual
);

  • Table size in mb 
select   segment_name,sum(bytes)/(1024*1024) "TABLE_SIZE(MB)" from dba_extents where  segment_type='TABLE' and owner=upper('&OWNER') and segment_name =upper( '&SEGMENT_NAME') group by segment_name;

  • Each Table Size under a Schema
select 
   segment_name           table_name,       
   sum(bytes)/(1024*1024) table_size_meg 
from   
   dba_extents 
where  
   segment_type='TABLE' 
and    
   owner='GLOGOWNER'  group by segment_name  order by 2 desc;
  • Redolog size
SELECT
   a.group#,
   substr(b.member,1,30) name,
   a.members,
   a.bytes/1024/1024 as mb , a.bytes/1024/1024/1024 as gb,
   a.status
FROM
   v$log     a,
   v$logfile b
WHERE
   a.group# = b.group#
;

  • Tablespace growth size 

set lines 132
set pages 200
select min(creation_time) "Create Time", ts.name, round(sum(df.bytes)/1024/1024) curr_size_mb, 
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)),1) growth_per_day_MB, 
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 30,1) growth_30_days_MB 
from v$datafile df ,v$tablespace ts where df.ts#=ts.ts# group by df.ts#,ts.name order by df.ts#;
  • Archive generation per hour
    •  
      set pages 999 lines 400
      col h0 format 999
      col h1 format 999
      col h2 format 999
      col h3 format 999
      col h4 format 999
      col h5 format 999
      col h6 format 999
      col h7 format 999
      col h8 format 999
      col h9 format 999
      col h10 format 999
      col h11 format 999
      col h12 format 999
      col h13 format 999
      col h14 format 999
      col h15 format 999
      col h16 format 999
      col h17 format 999
      col h18 format 999
      col h19 format 999
      col h20 format 999
      col h21 format 999
      col h22 format 999
      col h23 format 999
      SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
       COUNT (1) "Total",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
       SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
       ROUND (COUNT (1) / 24, 2) "Avg"
      FROM gv$log_history
      WHERE thread# = inst_id
      AND first_time > sysdate -30
      GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
      ORDER BY 1,2;

No comments:

Post a Comment