Home » Archive

Articles tagged with: Tablespaces autoextend

Oracle Code »

[23 Nov 2009 | No Comment | 1,372 views]

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,