0 Replies Latest reply: Feb 27, 2012 10:42 PM by 880923 RSS

    Query Modification

    880923
      Dear all,

      I require your help to modify the below query. When you check the output am finding that tablespace name column having repeatition of certain tablespace names . In that case of repeated tablespaces I want the one with whose autoextensible column value is 'Yes'. Kindly assist me .

      select df.TABLESPACE_NAME,
      round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct,
      round(decode(df.MAXBYTES, 34359721984, 0, (df.BYTES - fs.BYTES) / df.MAXBYTES * 100)) max_pct,
      df.AUTOEXTENSIBLE
      from
      (
      select TABLESPACE_NAME,
      sum(BYTES) BYTES,
      AUTOEXTENSIBLE,
      decode(AUTOEXTENSIBLE, 'YES', sum(MAXBYTES), sum(BYTES)) MAXBYTES
      from dba_data_files
      group by TABLESPACE_NAME,
      AUTOEXTENSIBLE
      )
      df,
      (
      select TABLESPACE_NAME,
      sum(BYTES) BYTES
      from dba_free_space
      group by TABLESPACE_NAME
      )
      fs
      where df.TABLESPACE_NAME=fs.TABLESPACE_NAME
      order by df.TABLESPACE_NAME asc
      /

      Current output:

      TABLESPACE_NAME USAGE_PCT MAX_PCT AUT
      ------------------------------ ---------- ---------- ---
      CORE_DATABASE_DATA 95 0 YES
      CORE_DATABASE_DATA 96 96 NO
      CORE_DATABASE_INDEX 9 9 NO
      DRSYS 24 0 YES
      INDX 0 0 YES
      NCO_DATA1 26 26 NO
      NCO_DATA2 54 54 NO
      NCO_DATA3 93 0 YES
      NCO_GENERAL 5 0 YES
      NCO_GENERAL 5 5 NO
      NCO_IDX1 16 16 NO

      TABLESPACE_NAME USAGE_PCT MAX_PCT AUT
      ------------------------------ ---------- ---------- ---
      NCO_IDX2 13 13 NO
      NCO_IDX3 95 0 YES
      STATS 83 2 YES
      SYSTEM 100 0 YES
      TOOLS 1 0 YES
      UNDOTBS1 0 0 YES
      USERS 1 0 YES
      V2_DATA 100 0 YES
      V2_DATA 100 100 NO
      XDB 94 0 YES