here is a script that I came across this morning written for (table)space monitoring..
select a.tablespace_name, trunc(sum(maxbytes/1024/1024),2) maxsize_mg,
trunc(((b.bytes-c.bytes)/1024/1024),2) used_mg,
trunc(sum(maxbytes/1024/1024) - ((b.bytes-c.bytes)/1024/1024),2) free_mg
from sys.dba_data_files a, sys.sm$ts_avail b, sys.sm$ts_free c
where b.tablespace_name=c.tablespace_name
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.bytes, c.bytes
order by 1;
yeah yeah yeah, its there in production already..
given that sql, how many "oops" can you find?
I managed a find a few
a) sm$ts_avail & sm$%ts_free - those are actually views that were there before Christ. sm stands for server manager, I guess. I have never seen these views anytime in the last 8 years..dont even know what version was it available and from when it was depricated. (note: the above script was developed to run on 10.2.0.3)
so thats my first "OOPS"
b)
>>group by a.tablespace_name, b.bytes, c.bytes
yi ..yi..yikes!!!
"group by bytes" - to get the sum of bytes?
that makes it second & interesting "oops"
c)
since I am neither exposed nor used these views, I curiously checked out what the view definitions are in db.
SQL> select text from dba_views
2 where view_name='SM$TS_FREE'
3 and owner='SYS';
TEXT
------------------------------------------------------------
select tablespace_name, sum(bytes) bytes from dba_free_space
group by tablespace_name
SQL> ed
Wrote file afiedt.buf
1 select text from dba_views
2 where view_name='SM$TS_AVAIL'
3* and owner='SYS'
SQL> /
TEXT
------------------------------------------------------------
select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name
forget about what its calculating..but just looking at it, tells the output is one row per tablespace (group by tablespace)
now check the original query..it joins dba_data_files and these views..
oops & a big OUCH!!!!
update: I checked this page after a few of the visitors complained about the missing text. I probably over-used/abused the pre tag. IE shows the post perfectly right, but for some reasons firefox doesnt. Hey , I am not a HTML person. This is one other reason why "standardization" is so badly needed
