DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Free space in a datafile ?

  1. #1
    Join Date
    Feb 2002
    Posts
    267
    Hi folks,
    Is there any way to find out how much
    space is occupied and how much space is
    free in a Datafile.

    Regards
    sonia

  2. #2
    Join Date
    Jun 2001
    Posts
    88
    Hi,
    You can use dba_free_space, dba_data_files views to
    see the free space in Tablespace and space in Data files.
    Regards,
    Akash

  3. #3
    Join Date
    Aug 2002
    Posts
    35

    Cool

    This will give u all info about space in each and every tablespace also u can query dba_datafile ,dba_free space in similar way to find about individual datafiles

    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(+);

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