Database size

[habeebksa]

Hi, how can we know the size of the database. Is it the total sum of all datafiles in a database or is there any query to find the size.


thanks,

[davey23uk]

that is the size of the database, plus the control files and redo log files

[craigdba]

REM #################################################################################################
REM # File Name : free.sql #
REM # Author : #
REM # Created : #
REM # Modifications : #
REM # : #
REM # #
REM # This scripts displays freespace within all of the tablespaces #
REM #################################################################################################

SET PAUSE OFF
SET FEEDBACK OFF
SET PAGESIZE 66
SET TERMOUT OFF
SET TRIMSPOOL ON

TTITLE SKIP 2 CENTER 'FREE - Free space by Tablespace' skip 2

COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
COLUMN name FORMAT A16 HEADING "Tablespace Name"
COLUMN bytes FORMAT 9,999,999,999,999 HEADING "Total Bytes"
COLUMN used FORMAT 9,999,999,999,999 HEADING "Used"
COLUMN free FORMAT 999,999,999,999 HEADING "Free"

BREAK ON REPORT

COMPUTE SUM of bytes ON REPORT
COMPUTE SUM of free ON REPORT
COMPUTE SUM of used ON REPORT

SPOOL free.lst

SELECT a.tablespace_name name, b.tablespace_name dummy,
SUM(b.bytes)/COUNT( DISTINCT a.file_id||'.'||a.block_id ) bytes,
SUM(b.bytes)/COUNT( DISTINCT a.file_id||'.'||a.block_id ) -
SUM(a.bytes)/COUNT( DISTINCT b.file_id ) used,
SUM(a.bytes)/COUNT( DISTINCT b.file_id ) free,
100 * ( (SUM(b.bytes)/COUNT( DISTINCT a.file_id||'.'||a.block_id )) -
(SUM(a.bytes)/COUNT( DISTINCT b.file_id ) )) /
(SUM(b.bytes)/COUNT( DISTINCT a.file_id||'.'||a.block_id )) pct_used
FROM sys.dba_free_space a, sys.dba_data_files b
WHERE a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name, b.tablespace_name;
SPOOL OFF
SET TERMOUT ON
/
EXIT

[davey23uk]

lovely, but that is only tablespaces -a database also consists of redo log files, control files and temp files

[Dhananjay]

Dear All,

Which Database size u want ?
Physical size or Actual size or Logical Size?

Total Size of Database :

Select sum(bytes)/1024/1024/1024 "Physical Database Size" ,' GB '
from dba_data_files ;


Actual Size of the database :

select sum(bytes)/1024/1024/1024 "Actual Database Size", ' GB '
from dba_segments ;


If u want to know the Logical size of the database , take full export of the database and check the size of the export dump file.


I hope my suggestion will help.....

[craigdba]

I was aware it is only tablespaces, just trying to help him out.