on 10-15-2008 2:54 PM
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
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
;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
/
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.