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

13 comments:

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

小遊戲,情色視訊聊天室,4u成人,pc交友,尋夢園聊天聯盟,玩美女人影音秀,666成人,免費視訊,聊天,情色論壇,視訊,成人文學,成人電影,漫畫貼圖,情色自拍,美女遊戲,情色a片,成人遊戲,上班族聊天室,巨乳,成人論壇,嘟嘟貼圖區,美女寫真,ut聊天室,同志聊天室,色情小遊戲,貼圖區,哈比寬頻成人,嘟嘟成人,玩美女人,視訊交友,自拍貼圖,正妹計時器,情色a片,成人遊戲,上班族聊天室,巨乳,成人論壇,嘟嘟貼圖區,美女寫真,ut聊天室,

Anonymous said...

欣賞是一種美德~回應是最大的支持^^..................................................

Anonymous said...

洪爺色情漫畫洪爺ˊ洪爺觀賞影片洪爺論檀洪爺貼片洪爺貼圖網洪爺貼圖自拍洪爺做愛貼圖洪爺免費色情觀賞洪爺圖片洪爺圖庫洪爺城洪爺密碼洪爺寫真洪爺性感影片貼影片洪爺情色綱洪爺色情文章洪爺色情影片洪爺色情小說洪爺網紙洪爺線上a片洪爺線上直播網洪爺線上免費情色影片洪爺dvd洪爺自拍網洪爺自拍色情貼圖區洪爺色站洪爺色網洪爺色妹妹貼片洪爺色情片免費觀賞洪爺色情網站無碼貼片洪爺色情網站免費洪爺色情論壇洪爺色情貼圖區洪爺色情圖片洪爺情色自拍洪爺情色館洪爺情色貼圖流連忘返流行網路美女視訊

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; http://slimlipoplusdiet.com

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 - http://musclerevxtremereview.com

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