Useful scripts for DBA perspective -

http://www.dbapool.com/scripts.php

More stuff on finding free space available in a database -

http://forums.oracle.com/forums/thread.jspa?threadID=624042

A sql that we found useful is here - (from the above link itself) -

SELECT
NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) NAME ,
mbytes_alloc mbytes ,
mbytes_alloc - NVL (mbytes_free, 0) used ,
NVL (mbytes_free, 0) free ,
((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100 pct_used,
100 - (((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100) pct_free
FROM
(
SELECT
SUM(BYTES) / 1024 / 1024 mbytes_free,
tablespace_name
FROM
SYS.dba_free_space
GROUP BY
tablespace_name
)
a,
(
SELECT
SUM(BYTES) / 1024 / 1024 mbytes_alloc,
tablespace_name
FROM
SYS.dba_data_files
GROUP BY
tablespace_name
)
b
WHERE
a.tablespace_name(+) = b.tablespace_name

UNION ALL

SELECT
f.tablespace_name ,
SUM (ROUND((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) ) "total MB" ,
SUM (ROUND(NVL (p.bytes_used, 0) / 1024 / 1024, 2)) "Used MB" ,
SUM (ROUND ( ((f.bytes_free + f.bytes_used) - NVL (p.bytes_used, 0) ) / 1024 / 1024, 2 ) ) "Free MB" ,
(SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
100 - (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)))
FROM
SYS.v_$temp_space_header f,
dba_temp_files d ,
SYS.v_$temp_extent_pool p
WHERE
f.tablespace_name(+) = d.tablespace_name AND
f.file_id(+) = d.file_id AND
p.file_id(+) = d.file_id
GROUP BY
f.tablespace_name
ORDER BY
5 DESC --&orderby
;

Compile all invalid packages in a schema : oracle

Came across this from sql developer, Looks like a very useful script, especially when its made available readymade

begin
FOR cur IN
(SELECT OBJECT_NAME, OBJECT_TYPE, owner
FROM all_objects
WHERE object_type in ('PACKAGE','PACKAGE BODY')
and owner = :OBJECT_OWNER AND status = 'INVALID' )
LOOP
BEGIN
if cur.OBJECT_TYPE = 'PACKAGE BODY'
then
EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile body';
else
EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile';
end if;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
end loop;
end;

And then another one to check as to what all objects are invalid -

select object_type, count(*)
from user_objects
where status = 'INVALID'
GROUP BY object_type

Oracle : count(1) vs count(*)

It seems we should prefer using count (*) over count(1)

read the article from asktom here...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40208915257337

there've been other discussions and debates on this as well.

Free Adobe Flex Video Training

Just found about it...

http://www.adobe.com/devnet/flex/videotraining/?sdid=EGMNU

not been through it yet, but it looks pretty good, combination of pdf ebooks, audio/video stuff and all that...

More Free ebooks on Oracle - Unix - Linux

Find free to download ebooks on this site -

http://www.itstudy8.org

Changing System name in Ubuntu

When I installed Ubuntu for the first time, I named my system to reflect the os name (gutsy) and the architecture (64 bit). However, whenever there were system upgrades, the name got irrelevant. For example, when I upgraded to Hardy, the name still remained stuck to the last version of OS.

Therefore, I now thought of renaming my system to separate it from the os versions. This is so very simple to achieve in Ubuntu -

just edit the file /etc/hostname (owned by root, therefore use sudo). Put in the new name, and restart the system.

However, the consequences of this name change will have to be reflected across the network (if you have one). Hostname files on all other systems or dns entries will have to be updated to reflect the new system name.

Useful Articles on Oracle

Useful Articles on Oracle Cast function -
http://www.psoug.org/reference/cast.html

Performing DML with Collections
1. http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref483
2. http://www.psoug.org/reference/collections.html

Answers to my question on whether or not its possible to perform DML on Oracle collections (in combination with Cast)
1. http://www.orafaq.com/forum/t/137485/133715/
2. http://forums.oracle.com/forums/thread.jspa?messageID=3176984

Some Notes on Joins & their syntaxes in Oracle
1. http://oreilly.com/catalog/orsqlpluspr2/chapter/ch01.html

Some articles on Oracle Explain Plan and how to generate/read/understand it
1. http://www.adp-gmbh.ch/ora/explainplan.html
2. http://www.adp-gmbh.ch/ora/sql/join/join_methods.html
3. http://download-uk.oracle.com/docs/cd/B12037_01/server.101/b10752/ex_plan.htm

Some articles on Oracle Global Temp Tables (GTT)
1. http://www.dbasupport.com/oracle/ora8/page2.shtml
2. http://www.dba-oracle.com/t_temporary_tables_sql.htm

On Oracle Memory Usage
http://www.dbasupport.com/oracle/ora11g/memory_usage.shtml

Useful mysql stuff...

Here's a compilation of my day to day use of mysql related stuff...

1. Resetting root password in mysql
   - Goto this page https://help.ubuntu.com/community/MysqlPasswordReset
   
   
2. To give privileges to a certain user in mysql
   - grant all on *.* to username
   
   
3. 
   

Finally... IE on Linux / Ubuntu...

Great..... It works... :)

Initially, when I needed to run a few IE specific sites on my Ubuntu installation, I spent quite some time searching around google, and some more prominent linux sites... But I did not hit success...

Today however, my search bore fruit... I found IEs4Linux project, and its so damned simple.

I am still running Hardy (not Interpid) on my desktop, and wine (the default). I picked up the instructions, kind of updated them myself to work in my hardy, and bingo.. it installed and works...absolutely.

Here's what I did -

1. Found this page for installation instructions on Ubuntu :
   http://www.tatanka.com.br/ies4linux/page/Installation:Ubuntu
   
   
2. Run the following commands -

3. sudo apt-get update
   sudo apt-get install wine cabextract

4. And then run this -
   

5. wget http://www.tatanka.com.br/ies4linux/downloads/ies4linux-latest.tar.gz
   tar zxvf ies4linux-latest.tar.gz
   cd ies4linux-*
   ./ies4linux
   

6. The last one would open a dialog box and would ask you basic questions about what version do you want, choose what u like and bingo.. it would start installation...
   
   
7. Within minutes, it automatically downloads some files from here and there, and is done.
   
   
8. It just works...

Agile : What is, Comparison, Transition...

Generally on Agile Methodology -
1. Agile methodology home page - http://www.agilemethodology.org/
2. Manifesto of Agile methodology - http://www.agilemanifesto.org/
3. Working with Agile methods - http://www.agileadvice.com/

Some links on Comparison of waterfall and Agile development methodologies.
1. http://agileintro.wordpress.com/2008/01/04/waterfall-vs-agile-methodology/
2. Youtube -http://www.youtube.com/watch?v=XokJLWp7icI
3. on Agile Advice - http://www.agileadvice.com/archives/2006/05/waterfall_vs_ag.html


Some links on the transition from Waterfall model to Agile methodology...
1. Agile Blog: Transitioning from Waterfall to Agile - Some tips
2. From Udayan
3. Scrum Alliance
4. A Presentation

Blogged with the Flock Browser

Some Oracle data dictionary help...

To get parameters information for your functions/procedures/packages etc. you can use all_arguments view. A sample query is attached...

select owner, object_name, package_name, argument_name, position, data_type, in_out
from all_arguments
where owner = (case when lc_user is null then user else lc_user end)
and package_name = (case when lc_pkg_nm is null then package_name else lc_pkg_nm end)
order by owner, package_name, object_name, position;

Further another view called all_source is also pretty useful if you have to look at the actual source code of a component.

Following links helped me on this -

1. http://www.eveandersson.com/writing/data-model-reverse-engineering#plsql
2. http://www.c-sharpcorner.com/Forums/ShowMessages.aspx?ThreadID=48498

My new quad core desktop system...

Recently managed to get a desktop system home, especially after coming back, it was necessary. The laptop just doesnt provide enough power.

This sure looks like a monster for a desktop system usage.

- Quad Core CPU (Core 2 Quad Q6600) at 2.4 GHz, 8 MB L2 Cache
- 2 GB RAM @800 MHz
- 250 GB Seagate Barracuda Hard Disk
- Here I was a bit disappointed. My local vendor just couldnt manage to get me a 10K rpm or higher hard disk from his supplier. He kept saying it would require a SCSI card and all that configuration. From my understanding it should be a simple SATA or SATA2 disk. But, well, this is the first one in place, may be we'd have more... :)

So, with this system in place, I plan to do some interesting things... e.g. running my long awaited local oracle server so that I can work with apex (http://apex.oracle.com)

Running Hardy on it. To say the least, hardy flies on this system. To start with, everything was detected normally on this configuration and I had no difficulty to make it work with Ubuntu Hardy. Even the live CD worked fine.

Now with the installed copy along with all the updates so far, the system literally flies... The system monitor shows me 4 CPUs and it adds so damned much to my confidence in using this power.... :)

The only issue I have found till now is with my power supply ratings/configuration. It somehow draws a lot of power (the rating on the PSU box says 450W) whereas the Display is not connected to the CPU power (its a TFT anyway). The UPS that I have is a APC with 500 VA rating. The UPS just doesnt hold.

All the power from the UPS comes to just this system and the display. Still it doesnt hold. About the power supply, I was told that the rating (450W) is an indication of how much can this provide, not how much does it need all the time. That might be wrong or right both. I am still figuring that out. No idea till now.

iPhone 3G in India...

Finally... finally... after such a long wait.. iPhone comes to India.. officially. But, despite all the hype and wait around it, the affordability of it really a huge question mark.

I didnt change my mobile phone for last 2 years, waiting for the iPhone to come to India. I booked an iPhone for me through both the prospective service providers in India, Airtel and Vodafone. It was all in anticipation that once its released in India, there would be huge queues for buying it, as was seen in other parts of world for iPhone 3G release.

However, the launch in India was not as expected. There was hype, but not as many customers. I am not a statistician and therefore cant give any numbers to prove my point, but I guess I have a feeling for the situation.

In my view, the pricing of the iPhone is a big reason for its slow launch. I cant say whether or not it would pick up in future, but right now, the price tag of 31000 and 36000 INR for iPhone is a very very high price. I mean, yes its a good phone, there are nice features, but thirty one thousand rupees... its just too much for a gadget in my view. And then, compare the price of the phone in US, 200 USD.. converts to around 8000 INR and so it shows that apple is selling that phone 4 times as costly as in US.. what kind of business strategy would that be ??? Especially in a cost sensitive market as India...

There would be people buying it, but not like me, not the average Indian IT guy, who knows the features, their meaning and usage and has a wish to own one. I dont know many who'd take up the phone at this price tag.

May be Apple will come back with a price cut in India as well, as they did in US... but will they offer people refunds for the difference.. we'll see...

Till the time apple decides to cut down on the price tag, my wait for owning an iPhone continues...

Data Warehousing on a Shoestring Budget : TDWI

Another interesting set of articles around low budget data warehousing...

Data Warehousing on a Shoestring Budget

Blogged with the Flock Browser

Ideas on DWH Testing...

Recently I was asked about strategy on data warehouse testing.  Realizing how rarely we talk about this, I went to google for this and spend about half an hour searching.

Found the following links, which still need to be researched/analyzed further.

DM Review : Where are the Articles on Data Warehouse Testing and Validation Strategy?

A Wordpress blog : Strategies for Testing Data Warehouse Applications « Business Intelligence and Datawarehousing

DM Review : Strategies for Testing Data Warehouse Applications

Blogged with the Flock Browser

Lookup Transformations in Informatica

Lookup is a transformation to look up the values from a relational table/view or a flat file. There are two types of Lookups in Powercenter, namely;

1. Connected Lookup 
2. Unconnected Lookup

Caching is an important facet of lookup transformation planning. You need to know what kind of data you are dealing with, how frequently do you call the lookup, how frequently does the data change, what is the size of your lookup table etc. among other things. Once you use cache, the trip to database can be avoided, thereby enhancing performance.

Different types of caches can be used with lookup like static, dynamic, persistent, and shared(The dynamic cache cannot be used while creating an un-connected lookup). Each of these has its own identification. For more details, refer to Informatica Transformation Guide.

Lookup is a passive transformation, and can be used either connected or unconnected.  Typically, connected lookup is used when you want to do the lookup for all rows. When you have selective lookup requirements, its normally better to go for unconnected lookup. Unconnected Lookup can be used as if its a function call.

To read more, here is a good article -
What is lookup transformation in informatica? - IT Community

Getting Started with ActiveScaffold

Recently I returned to my rails env to do some more experiments (which might be used for a future project)

To start with, I have seen a bit of active scaffold stuff and was very impressed with it originally already.  In the meantime, rails 2 have arrived.  Quite a few things have changed and I was thinking that it might have an effect on the overall picture of active scaffold also.

However, checking up on railsforum etc brought out that not much effect has gone down to active scaffold kind of plugins from rails 2.

Starting up active scaffold is really easy as they say on its website also...

try it here... www.activescaffold.com It turned out to be really simple to initialize yourself with it. 

Searching a bit more I came across a few tutorials -

Tutorial from Active Scaffold guys

Another one from someone like me, experimenting with activeScaffold...

AkillesBlog » Blog Archive » Ruby on Rails: Experimenting with ActiveScaffold   

Blogged with the Flock Browser

Open Solaris build May 2008 ( 2008.05 ) released

Today I came across a few news items and articles here and there that talked about the release of OpenSolaris projects' May 2008 release.

Though its available for download from the open solaris home page, Sun is offering Free CD shipments for the latest release here : https://www2.sun.de/dct/forms/reg_us_2307_228_0.jsp

I already ordered my copies :)

Also, there is a nice article about bit of unix OS history and open solaris 2008.05 release review here : http://blogs.zdnet.com/BTL/?p=8703

Installing Oracle 11g on Ubuntu server 64bit on vmware server 2 beta

I have this wish to install a hardy server version on my system. Since I did not want to add another partition, so I chose to do that inside a virtual machine (using vmware). vmware supports 64bit Guest Operating system, as opposed to virtual box which does not. :(

So, I have been able to install the server edition inside the vmware machine. I have never used nfs before, but the tutorial I was following was suggesting to use it.

http://www.pythian.com/blogs/654/installing-oracle-11g-on-ubuntu-linux-710-gutsy-gibbon

However, I am having problems with -

1. Trying to expand the virtual hard disk inside vmware. I tried it, but havent succeeded yet.
2. The Oracle installer exits with a strange error message.

I have posted my issues on the ubuntu forums, and havent got any solution yet. Lets see how it goes...

Using Base64 encoding with Oracle / Perl

For basic encryption purposes, base 64 encoding can be used. In fact its so basic that it would probably be incorrect to call it encryption. Its more encoding, basically preventing the subject text from appearing in plain text.

For example, if in some tool, all the parameters are stored in repository tables, and therefore, its not very nice if the passwords are stored there in plain text. They should at least be made illegible, so that they dont appear in plain text and there is some effort spent in decoding it.

Base64 encoding is an industry standard and almost all programming languages, development toolkits provide plugins for this. For Oracle, there are pre-existing packages providing the functionality.

We can use base 64 encoding on oracle side using Oracle supplied package UTL_ENCODE. On the perl side, we could use cpan package MIME:Base64. Other languages provide different kinds of plug-ins. For example, ruby on rails has a plug in for this, Javascript also allows this to be done relatively easily.

The idea is that, such sensitive data to be entered by customers, in encoded format. So, we also dont see the plain text version. And then the program uses the encoded version, right to the point before actual data needs to be used.

For achieving this, following way of working can be used :

1. On Oracle side, the sensitive data is encoded using a program call equivalent to the following will return an encoded/decoded string for a given input string.

FUNCTION fnc_encrypt(lv_str IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
   RETURN utl_raw.cast_to_varchar2(UTL_ENCODE.BASE64_ENCODE(utl_raw.cast_to_raw(lv_str)));
END;

FUNCTION fnc_decrypt(lv_str IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
    RETURN utl_raw.cast_to_varchar2(UTL_ENCODE.BASE64_deCODE(utl_raw.cast_to_raw(lv_str)));
END;

Its a design decision whether to put this code in a procedure/package somewhere.

2. On the other end of the processing chain, reverse operation would have to be performed. For example, in Perl, the implementation relies on the cpan package MIME:Base64

use MIME::Base64 ();

and then use the decode/encode function as per the need...

MIME::Base64::decode($PI_CAT_PWD);
MIME::Base64::encode($PI_CAT_PWD);