SQL for total database size please
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQL for total database size please

Hybrid View

  1. #1
    Join Date
    Jun 2001
    Location
    NY
    Posts
    226
    Would someone please give me the sql to query the total size of a database please?
    roukie-dba

  2. #2
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Try this it show in Giga bytes

    select sum(bytes)/(1024*1024*1024) from V$datafile

    Regards
    Santosh

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    hi,

    select sum(bytes)/(1024*1024*1024) from V$datafile
    will give you total available physical storage space

    select sum(bytes/1024/1024) from dba_segments
    will give you total physical storage space used.



  4. #4
    Join Date
    Jun 2001
    Location
    NY
    Posts
    226
    Santosh, Sureshe - a big thank you to both of you!!!
    roukie-dba

  5. #5
    Join Date
    Jul 2001
    Posts
    45
    Hi

    select sum (bytes/1024/1024/1024) from v$datafile;
    will give you the size of your database

    Hope this help

    Regards
    Richard

  6. #6
    Join Date
    Feb 2001
    Posts
    128
    SELECT TO_CHAR(SUM((a.logfile_size+b.tempfile_size+c.datafile_size)/1024/1024/1024), '999999990.00')"Database Size in GB"
    FROM
    (SELECT SUM(bytes) logfile_size FROM v$log) a,
    (SELECT NVL(SUM(BYTES),0) tempfile_size FROM v$tempfile) b,
    (SELECT SUM(BYTES) datafile_size FROM v$datafile) c;

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    all these SQL just show you the assigned space for your database, for example you have 10GB in datafiles but actually you probably only have 500MB data, so what is the database size are you looking for? The assigned or the real data?

  8. #8
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi Suresh,

    This script will tell you something about the filling of your database

    Tycho

    select a.tablespace_name,
    a.ts_size_in_MB,
    b.ts_free_space,
    round(((a.ts_size_in_MB-b.ts_free_space)/a.ts_size_in_MB)* 100,2) percentage
    from (select tablespace_name,
    round(sum(bytes)/(1024*1024)) ts_size_in_MB
    from dba_data_files
    group by tablespace_name) a,
    (select tablespace_name,
    round(sum(bytes)/(1024*1024)) ts_free_space
    from dba_free_space
    group by tablespace_name) b
    where a.tablespace_name = b.tablespace_name (+)
    order by b.ts_free_space

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