Tag Archives: size

Database

Oracle Database size in GigaByte, MegaByte, KiloByte and Byte

Oracle Database size in GigaByte, MegaByte, KiloByte and Byte

 

View the size of an Oracle database (partial or total in GB, MB, KB and B), run the SQL statements below:

Size of TABLESPACES in GB

select NAME_TABLESPACE tablespace_name, sum (bytes) / 1024/1024/1024 SIZE_GB from group by tablespace_name DBA_SEGMENTS;

Total size of the database in GB

select sum (bytes) / 1024/1024/1024 SIZE_GB from DBA_SEGMENTS;

TABLESPACES size in MB

select NAME_TABLESPACE tablespace_name, sum (bytes) / 1024/1024 SIZE_MB from group by tablespace_name DBA_SEGMENTS;

Total size of the database in MB

select sum (bytes) / 1024/1024 SIZE_MB from DBA_SEGMENTS;

TABLESPACES size in KB

select NAME_TABLESPACE tablespace_name, sum (bytes) / 1024 SIZE_KB from group by tablespace_name DBA_SEGMENTS;

Total size of the database in KB

select sum (bytes) / 1024 SIZE_KB from DBA_SEGMENTS;

Size of TABLESPACES B

select NAME_TABLESPACE tablespace_name, sum (bytes) from SIZE_B group by tablespace_name DBA_SEGMENTS;

Total size of the database in B

select sum (bytes) from SIZE_B DBA_SEGMENTS;

 

Back to previous menu

Database

Size Oracle Database for Data File Type

Size Oracle Database for Data File Type

 

To select the size of Oracle database by type of data file, simply turn the SQL below:

on September serveroutput;
declare
dbf number;
tmpdbf number;
lgf number;
ctl number;
sum number;
begin
select trunc (sum (bytes / 1024/1024), 2) into DBF from v $ datafile;
select trunc (sum (bytes / 1024/1024), 2) into tmpdbf from v $ tempfile;
select trunc (sum (bytes / 1024/1024), 2) into lgf from v $ log l, v $ logfile lf where l.group # = # lf.group;
select trunc (sum (block_size * file_size_blks / 1024/1024), 2) into ctl from v $ controlfile;
select trunc ((DBF tmpdbf + + + lgf ctl) / 1024.2) into sum from dual;
DBMS_OUTPUT.PUT_LINE (chr (10));
DBMS_OUTPUT.PUT_LINE (‘Datafiles:’ dbf || || ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘tempfiles:’ || || tmpdbf ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Logfiles:’ || || lgf ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Controlfiles:’ || || ctl ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Total Size’ || || sum ‘GB’);
end;

 

Back to previous menu