To calculate size in MB
================
select sum(bytes / (1024*1024)) "DB Size in MB" from dba_data_files;
---------------------------
To calculate size in GB
==================
select round((sum(bytes)/1048576/1024),2) from v$datafile;
To calculate size in GB
==================
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from
v$controlfile) "Size in GB"
from
dual;
to check name from database;
------
select name from v$database;
To calculate Reserved size+used size+free size
================================
select
"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)"
"Used_Space(MB)","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
);
####Oracle_DB||How large is the database||#####
===================================
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
--------------------------------------------------
Segment Size Query (FREE SPACE)
===============
select ds.owner,ds.segment_type,
case
when trunc(sum(ds.bytes)/1024) < 1024 then
to_char(round(sum(ds.bytes/1024),2),'99999999999999.99')||' KB'
when trunc(sum(ds.bytes)/1024/1024) < 1024 then
to_char(round(sum(ds.bytes/1024/1024),2),'99999999999999.99')||' MB'
when trunc(sum(ds.bytes)/1024/1024/1024) < 1024 then
to_char(round(sum(ds.bytes/1024/1024/1024),2),'99999999999999.99')||'
GB'
end segment_sizes
from dba_segments ds
group by ds.owner,ds.segment_type
order by 1,2
To calculate table_space details and their sizes:
================================
col "FILE_NAME" format a50
col "TABLESPACE_NAM" format a40
col " FREE_MB" format a40
col "ALLOCATED_MB" format a40
col "CAPACITY AE" format a40
SELECT a.file_name,
substr(A.tablespace_name,1,14) tablespace_name,
trunc(decode(A.autoextensible,'YES',A.MAXSIZE-A.bytes+b.free,'NO',b.free)/
1024/1024) free_mb,
trunc(a.bytes/1024/1024) allocated_mb,
trunc(A.MAXSIZE/1024/1024) capacity,
a.autoextensible ae
FROM (
SELECT file_id, file_name,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes) maxsize
FROM dba_data_files
GROUP BY file_id, file_name,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes)
) a,
(SELECT file_id,
tablespace_name,
sum(bytes) free
FROM dba_free_space
GROUP BY file_id,
tablespace_name
) b
WHERE a.file_id=b.file_id(+)
AND A.tablespace_name=b.tablespace_name(+)
ORDER BY A.tablespace_name ASC;
---------------------------------------------
redo logs
--------
select group#,thread#, (bytes/1024/1024) size_mb, MEMBERS, status from v$log order
by 1,2;
set lines 190
col member format a50
col bytes format a10
select a.group#, a.member, b.bytes/1024/1024
from v$logfile a, v$log b where a.group# = b.group# order by 1;
