Wednesday, April 9, 2008

how many "OOPS"

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,$ts_avail b,$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
so thats my first "OOPS"

>>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"

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';

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> /

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 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


Venkat said...

update:: I happened to check metalink a few days ago and found out the sm$ views are related to storage manager and NOT server manager as I thought before.

Anonymous said...


Anonymous said...


Anonymous said...


Anonymous said...

加油啦!要繼續發表好文章喔! ..................................................

Anonymous said...

That is a very good tip especially to those new to the blogosphere.

Short but very precise info… Thanks for sharing this one.
A must read post!

my website ... Garcinia Cambogia

Anonymous said...

Hello colleagues, how is the whole thing, and what you want to say
concerning this piece of writing, in my view its truly amazing for me.

My webpage :: Pure GCE REviews

Anonymous said...

This post provides clear idea designed for the new viewers of blogging, that in fact
how to do running a blog.

Here is my blog;

Anonymous said...

Hi there! I just want to offer you a big thumbs up for
your great info you have got right here on this
post. I'll be returning to your blog for more soon.

Look at my site; Beta Force

Anonymous said...

These are truly wonderful ideas in concerning blogging. You have
touched some nice factors here. Any way keep up wrinting.

Look into my website; Power Precision Muscles

Anonymous said...

I visit day-to-day a few web sites and information sites to read articles or reviews,
but this weblog gives feature based content.

My website: Garcinia Cambogia

Anonymous said...

I am extremely inspired with your writing talents as neatly as
with the layout in your blog. Is this a paid topic or did you
modify it your self? Anyway keep up the excellent quality writing, it's uncommon to see a great weblog like this one today..

my site -

Anonymous said...

Howdy! This article could not be written much better!

Looking through this post reminds me of my previous roommate!

He always kept talking about this. I am going to forward this post to him.
Pretty sure he's going to have a very good read. I appreciate you for sharing!

Check out my web-site :: LeParfait Review