0 Replies Latest reply: Feb 17, 2012 6:45 AM by 880923 RSS

    Query to find a tablespace is autoextensible r not

    880923
      Hi all,

      Can you please help me with a query to get tablespace detail which are autoextensible.

      Actually with the below query am able to get results but it is including datafiles that are not autoextensible for a particular tablespace. Therefore it is giving me two results for a particular tablespace. Help me.

      SQL> select df.TABLESPACE_NAME,
      2 round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct,
      3 round(decode(df.MAXBYTES, 34359721984, 0, (df.BYTES - fs.BYTES) / df.MAXBYTES * 100)) max_pct,
      4 df.AUTOEXTENSIBLE
      5 from
      6 (
      7 select TABLESPACE_NAME,
      8 sum(BYTES) BYTES,
      9 AUTOEXTENSIBLE,
      10 decode(AUTOEXTENSIBLE, 'YES', sum(MAXBYTES), sum(BYTES)) MAXBYTES
      11 from dba_data_files
      12 group by TABLESPACE_NAME,
      13 AUTOEXTENSIBLE
      14 )
      15 df,
      16 (
      17 select TABLESPACE_NAME,
      18 sum(BYTES) BYTES
      19 from dba_free_space
      20 group by TABLESPACE_NAME
      21 )
      22 fs
      23 where df.TABLESPACE_NAME=fs.TABLESPACE_NAME
      24 order by df.TABLESPACE_NAME asc
      25 /

      TABLESPACE_NAME USAGE_PCT MAX_PCT AUT
      ------------------------------ ---------- ---------- ---
      CORE_DATABASE_DATA 95 0 YES
      CORE_DATABASE_DATA 96 96 NO
      V2_DATA 100 0 YES
      V2_DATA 100 100 NO
      XDB 94 0 YES