Database size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Database size

  1. #1
    Join Date
    Aug 2007
    Posts
    54

    Database size


    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,

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    that is the size of the database, plus the control files and redo log files

  3. #3
    Join Date
    Feb 2003
    Posts
    63

    Script

    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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    lovely, but that is only tablespaces -a database also consists of redo log files, control files and temp files

  5. #5
    Join Date
    Aug 2007
    Posts
    1
    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.....

  6. #6
    Join Date
    Feb 2003
    Posts
    63
    I was aware it is only tablespaces, just trying to help him out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width