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

Thread: Database size details

  1. #1
    Join Date
    Feb 2001
    Posts
    103
    Hi All,

    I want to calculate the total database size. I also wanted to know how much database space is used and how much is free.
    Can somebody help me with this?


    Thanks in advance..
    When the going gets tough, the tough gets going

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    This script will give you the total space used and free by tablespace, you would then need to add the size of redo logs and controlfiles to get your overall size.

    SET PAGESIZE 66
    COLUMN pct_free FORMAT 999.99 HEADING "% Free"
    COLUMN name FORMAT A16 HEADING "Tablespace Name"
    COLUMN mbytes FORMAT 99,999,999 HEADING "Total MBytes"
    COLUMN used FORMAT 99,999,999 HEADING "Used Mbytes"
    COLUMN free FORMAT 99,999,999 HEADING "Free Mbytes"

    BREAK ON REPORT
    COMPUTE SUM OF mbytes ON REPORT
    COMPUTE SUM OF free ON REPORT
    COMPUTE SUM OF used ON REPORT

    SELECT
    fs.tablespace_name name,
    df.totalspace mbytes,
    (df.totalspace - fs.freespace) used,
    fs.freespace free,
    100 * (fs.freespace / df.totalspace) pct_free
    FROM
    (SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
    FROM
    dba_data_files
    GROUP BY
    tablespace_name
    ) df,
    (SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1048576) FreeSpace
    FROM
    dba_free_space
    GROUP BY
    tablespace_name
    ) fs
    WHERE
    df.tablespace_name = fs.tablespace_name(+);

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    what is your oracle database version ?

    jovery : i think dba_temp_files should also be included in teh sql statement .
    siva prakash
    DBA

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    prakashs43 your quite right, for my purposes I deliberatly leave temp segments out of the script but in this case you would need to include them.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

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