-
preventing unable to extend tablespace
I want to be able to monitor tablespace that is near 100% full.
But only tablespaces that has datafile that are not autoextend.
Also,
if the datafile is autoextend on, i want to see the tablespace only if it is near his maxsize value.
Complication:
The thing is that i might have more than one datafile related to my tablespace.
And i might have the following situation:
tablespace USERS
datafile 1: 99% autoextend on not near maxsize value
datafile 2: 99% autoextend off;
So i would not want to see that USERS tablespace in my query. Since it is not near his maxsize value.
But i would like to see this scenario:
tablespace USERS2
datafile 1: 99% autoextend on near maxsize value
datafile 2: 99% autoextend off;
Does any one has that kind of query?
This query is close to what i need but does not work well:
select d.tablespace_name "TABLESPACE",
sum(d.bytes)/1048576 "SIZE (M)",
100 -(round((FREESPCE/(sum(d.bytes)/1048576))*100)) "USED (%)"
FROM dba_data_files d,
( SELECT round(sum(f.bytes)/1048576,2) FREESPCE,
f.tablespace_name Tablespc
FROM dba_free_space f
GROUP BY f.tablespace_name)
WHERE d.tablespace_name = Tablespc
group by d.tablespace_name,FREESPCE
order by 1 asc
-
ah, been there, done that about 5 years ago. Let me look at the code and get back to you...
Jeff Hunter
-
Try this:
Code:
select tbs_name, file_name, file_id,
autoextensible, maxsize, cur_size_in_mb, free,
( case when autoextensible = 'YES'
then round( ( cur_size_in_mb-free)/greatest(maxsize,1) ,2)
else round( ((cur_size_in_mb -free) / cur_size_in_mb ), 2)
end
) "% utilization"
from (
select tablespace_name TBS_NAME,
file_name, file_id, autoextensible ,
trunc(maxbytes/1024/1024) maxsize,
bytes/1024/1024 cur_size_in_mb,
(select trunc(sum(bytes)/1024/1024)
from dba_free_space b
where b.file_id = a.file_id) free
from dba_data_files a)
You can add filter condition on "% Utilization" column on the outer query.
Tamil
Last edited by tamilselvan; 01-31-2007 at 03:48 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|