Wednesday, August 18, 2010

Show all autoextendable tablespaces

In Oracle how to find all the autoextendable tablespaces:

set pages 10000
col tablespace_name format a20
col file_name format a45
col file_mb format 999999
col data_mb format 999999
col free_mb format 999999
col max_mb format 999999
select files.tablespace_name, files.file_name, ceil(files.mb) file_mb, ceil(files.mb - nvl(free.freemb,0)) data_mb,
nvl(free.freemb,0) free_mb, files.maxmb max_mb, autoextensible
from
(select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible,
decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb
from dba_data_files) files,
(select file_id, sum(bytes)/1024/1024 freemb
from dba_free_space
group by file_id) free
where files.file_id = free.file_id (+)
and files.autoextensible='YES'
order by 1,2;

No comments: