Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Single line command to find out BR* (or) SQL to find Table space info.

Dear experts

in a single line we would like to know one table space complete info

like we see when we try to extend the table space thru BR* tools like below.

I

Tablespace Files/AuExt. Total[KB] Used[%] Free[KB] MaxSize[KB]

PSAPP10 144/132 1301307392 99.99 151616 1325776896.

What is the command of BR* tools (or) SQL command

to get the info like above .

regards

PR

Former Member
replied

this may not be perfect, but give it a shot:

select

a.tablespace_name,

sum(trunc(a.bytes/1024/1024)) size_mb,

sum(trunc(decode(a.ae,'YES',a.maxsize-a.bytes+b.free,'NO',b.free)/1024/1024)) free_mb,

sum(trunc(a.maxsize/1024/1024)) maxsize_mb,

max(ae) auto,

count(a.nbfile)

from

(select

file_id,

tablespace_name,

autoextensible ae,

bytes,

decode(autoextensible,'YES',maxbytes,bytes) maxsize,

1 NBFILE

from dba_data_files

group by file_id,

tablespace_name,

autoextensible,

bytes,

decode(autoextensible,'YES',maxbytes,bytes)) a,

(select file_id,

tablespace_name,

sum(bytes) free

from dba_free_space

group by file_id, tablespace_name) b

where a.file_id=b.file_id(+)

and a.tablespace_name=b.tablespace_name(+)

group by a.tablespace_name

order by a.tablespace_name asc

/

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question