Tablespaces with autoextend parameter

Your primary All Database information source.

23 November 2009 765 views No Comment

Some tablespaces can be autoextensible, in this case, when necessary, Oracle automatically allocate new space for the tablespace; so you have not to add space to tablespaces. Use this query to see list of tablespaces with used and free space and to see if each tablespace is autoextensible:

SELECT
  a.file_id,
  a.tablespace_name,
  trunc(decode(a.autoextensible,'YES',a.maxsize-a.bytes+b.free,'NO',b.free)/1024/1024) free_mb,
  trunc(a.bytes/1024/1024) size_mb,
  trunc(a.maxsize/1024/1024) maxsize_mb,
  a.autoextensible ae,
  trunc(decode(a.autoextensible,'YES',(a.maxsize-a.bytes+b.free)/a.maxsize*100,'NO',b.free/a.maxsize*100)) free_pct
FROM
  (SELECT
     file_id,
     tablespace_name,
     autoextensible,
     bytes,
     decode(autoextensible,'YES',maxbytes,bytes) maxsize
   FROM dba_data_files
   GROUP BY file_id, tablespace_name, autoextensible, bytes,
     decode(autoextensible,'YES',maxbytes,bytes)
  ) a
  RIGHT OUTER JOIN
  (SELECT
    file_id,
    tablespace_name,
    sum(bytes) free
   FROM dba_free_space
   GROUP BY file_id, tablespace_name
  ) b
  ON (a.file_id=b.file_id AND a.tablespace_name=b.tablespace_name)
ORDER BY a.tablespace_name ASC;