Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    58

    Unanswered: tablespace free space

    I have a tablespace that has two data files. The second data file is 2gb. Initially it is 500 mb and is autoextensible. The following script takes the initial 500 mb only to give free space details.

    Can somebody give a script which takes the entire 2gb into consideration?


    Code:
    SELECT
    fs.tablespace_name TABLESPACE_NAME,
    df.totalspace TABLESPACE_TOTAL_SIZE,
    (df.totalspace - fs.freespace) MB_USED,
    fs.freespace MB_FREE,
    round(100 * (fs.freespace / df.totalspace),2) 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(+)
    order by MB_FREE ASC

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please post EXPLAIN PLAN for this convoluted collection of SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Rhetorical question....
    Why are THREE different SELECT statements needed to get values from two tables?
    ================================================== ========
    something similar to the SQL below should get you closer to a desired solution

    SELECT
    df.tablespace_name TABLESPACE_NAME,
    sum(df.bytes) TOTAL_SIZE,
    (sum(df.bytes) - sum(fs.bytes)) USED,
    sum(fs.bytes) FREE
    FROM dba_data_files df, dba_free_space fs
    where df.tablespace_name = fs.tablespace_name
    group by df.tablespace_name
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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