cancel
Showing results for 
Search instead for 
Did you mean: 

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

former_member69568
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member204746
Active Contributor
0 Kudos

try:

SELECT

ts.tablespace_name,

ts.size_m as Size_MB,

round(s.used_m) as Used_MB

FROM

(

select f.tablespace_name, sum(f.bytes)/(1024*1024) size_m

from dba_data_files f

group by f.tablespace_name

) ts,

(

select s.tablespace_name, sum(s.bytes)/(1024*1024) used_m

from dba_segments s

group by s.tablespace_name

) s

WHERE

s.tablespace_name(+) = ts.tablespace_name

ORDER BY

ts.tablespace_name

;

former_member69568
Participant
0 Kudos

I am getting output like

TABLESPACE_NAME SIZE_MB USED_MB MAX_MB

-


-


-


-


PSAPD10 58640 58615 is also required

I require another MAX_MB also ( as our tables spaces are in autoextend mode)

Please update the given script with desired info,

Regrets for troubling, but i am zero on oracle,

Some small requirement from client to send out daily alerts to some admins on db usage.,

I can explore, but bit short of time to complete this task by end of the day

Rgds

PR

former_member69568
Participant
0 Kudos

On oversight i missed thanking you

Markus Doehr & Eric Bruneller

Rgds

PR

former_member204746
Active Contributor
0 Kudos

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

/

Former Member
0 Kudos

Maybe you also want to give this command a try:

brspace -f dbshow -c tsinfo

Regards, Michael

Answers (1)

Answers (1)

markus_doehr2
Active Contributor
0 Kudos

The easiest way to find out the sql command used is to enable tracing and check the resulting trace:

29321 - Creating traces for BR Tools

Markus