Trained in AIX...

Last week I attended a training on AIX system administration from IBM (organized by company, obviously ;) It was a 7 days course covering all of the system administration stuff. There was a lot of new stuff to learn, LVM being the most number of times uttered word, once we did the chapter on LVM. It was a nice experience as a whole as for the first time I attended any training on Unix.

The sessions (specially after lunch) were sleepy also. I find this ppt method of training pretty boring. The trainer (most of) strictly, stupidly follows the slides and slide, I feel is a dumb sort of thing, makes you feel sleepy except at the moments when there are few eye opening bullet points.

There should be bare minimum number of slides and for rest of the things, trainer should use white board so that everybody follows that and doesn't sleep :)

Anyways it was really enjoying to be familiar with so many things in Unix.

Sidhu

Oracle from Eddie Awad...

Eddie Awad started a new series Oracle in 3 minutes on his blog. In the first post he has discussed about multi-versioning. Its a must watch for everyone who is working on Oracle. Hoping to get more of such stuff from Eddie.

Sidhu

Howard's decision...

Howard Rogers has done a post on his blog that is the site Dizwell worth it ? Do post your opinion. If he plans to close it down, we will miss a great resource for Oracle.

Sidhu

Asking Tom ? Read this !

Tom Kyte did a post on his blog about posting of reviews of questions on Asktom. In a nutshell the reviews not related to the original question will be ignored/deleted (not decided yet, as Tom said).

As other people said in the comments, personally I too like this idea very much. Earlier, many times, there were questions which started with someone asking about appropriate SGA size, then there were some other twists and discussions and then the thread ended in discussion about good or bad authors or something similar light years away from the original topic.

Now this action will make the discussions flow in a very controlled and neat & clean manner, all about the original topic. Hoping to see all the "great content" in a very orderly manner :)

Sidhu

Official Oracle wiki

Today I was checking Eddie Awad's blog. From there I came to know that Oracle has started Official Oracle Wiki hosted by Wetpaint. Check out. I just made my login. Hoping to contribute whatever little I can

Sidhu

UNDO tablespace in Oracle...

Today, I was following a thread on Oracle Forums. Someone asked a question about UNDO tablespace wrt to a scenario. The question was:
There is a database and its hot backup is taken on Friday. Now for Saturday, Sunday and Monday there are archive logs but no backups. Suppose the machine crashes on Monday. After we restore the database to Friday (from backup), recovery will happen. As UNDO tablespace is of Friday so it has no information related to transactions that happened on Saturday, Sunday and Monday. So in the end of recovery process when we need to rollback some transactions from where that required information will come ?

Howard did 3 beautiful follow ups of this post, explaining how UNDO works. Just saving it here for quick reference. Hope its no copyright mess :)

Follow up 1:
Yes, it can certainly be confusing, especially when you get told completely incorrect information! As has already (and thankfully!) been pointed out, redo logs contain redo change records from both committed and uncommitted transactions.

The answer to your question is that as we re-perform transactions by applying redo in a recovery session, we redo exactly what would have been done when the transactions were first performed. That is, we'd see a redo change record that says (in effect) update EMP set sal=900 where ename='Bob', so we'd find the Bob record in the restored Friday copy of the data file, and we'd lock that record. Then we'd store details of Bob's existing salary in an undo block. Then we'd store the new and old salaries in redo (yup, recovery generates redo!). Then we'd change the Bob record itself.

If that's all that's in the archives and online redo logs, that's all that happens: Bob's record is left locked and changed. At the end of the recovery process, we realise that a lot of re-performed transactions need rolling back, so SMON does that.... and it knows what to roll the stuff back to because in re-performing the transactions, we generated fresh undo.

So your question says, "how does the uncommited transactions rolled back with the Friday undo tablespace since they do [not] have latest uncommited trnsactions", but that's not right. The undo tablespace certainly STARTS at the state it was in on Friday. But as recovery proceeds, it gets 'freshened up', because the new transactions generate fresh undo.

(A slightly more accurate description would be to point out that when you generated undo on Saturday and Sunday, those changes to the undo blocks would themselves have generated redo. Therefore, your redo stream, archives and online alike, have the necessary information to recover the undo tablespace. Personally, I don't find that any more informative than thinking that applying redo generates fresh undo, but it's up to you which mental model you prefer to work with).

Follow up 2:
>does "recovery generates redo" mean that during recovery we regenerate the same amount >of redo that was generated since the last backup

No, it's not the same. If you do a simple test, you'll see that. Update EMP, commit, check with Log Miner that the redo is in the logs in analyzable form. The blow up your database, restore it and recover it. Your log sequence number will have moved on, redo will have been generated... but you can mine the logs till Christmas and you won't find a second 'update EMP' set of redo records.

That's why I mentioned the 'more accurately' bit further on in my original reply. Recovery is a bit more subtle than just sitting there issuing a lot of insert/update/delete statements as if from the keyboard of an incredibly fast typist! Metaphorically you can say, "We repeat transactions during recovery". But actually, it's "we apply redo change vectors"... and that doesn't generate a one-for-one amount of redo as the original transactions did.

>Will there be duplicate archive logs then?

No. Do the Log Mining test to see this for yourself. The updates you did before the blow-up will not be visible in the logs from after (or during) the recovery.

>Can we say that the undo tablespace starts from scratch with no undo at all

I'm not sure what you're getting at, but an undo segment is just a special sort of table, and it's got data stored in it, even if the transactions that placed them there are long-since finished. So when you restore that file on Monday, it comes back in the state it was in when it was backed up -with data in it. Leaving aside undo_retention for a moment, it follows from the fact that you're doing a database recovery that none of the stuff inside the rollback segments is related to "live" transactions, therefore all of it is over-writeable. So in that sense, yes, you could say the undo tablespace, just after restore and just before recovery, is 'clean'.

Follow up 3:
I didn't give "2 ideas". I gave just one. I just happened to give you the option of thinking of it in two different ways.

Applying redo causes transactions to be re-performed. That is the only, solo, number 1, all on its own, lonely idea being conveyed here.

Now you can describe the re-performance of transactions either as "re-doing the transactions" as if some virtual user were sitting there typing insert, update and delete statements maniacally fast. That's the way I usually think of it, largely because that's what it looks like when you use Log Miner to peer inside the logs. Or you can think of it in a slightly more technical and accurate way, of taking the description of changes to byte-data which the redo change vectors represent and re-applying those changes.

They're both descriptions of exactly the same process with the same outcome. They just happen to use different words to describe them because some people's mental models of what is happening work better with one than the other. It doesn't help, however, to start thinking that somehow I've described two different processes.

By way of a rather stretched analogy. Suppose you are a photographer. You take a lovely colour photograph of a landscape. You make a couple of copies of this photograph. The copy you framed and took especial care of one day gets damaged. Now, you can either repair that photo by going back to the scene, setting up the camera in the exact spot as before, waiting until the light conditions are exactly as they were, and then re-taking the shot. Or you can take the existing photo, compare it with another copy of the photo, and where you are lacking red/green/blue information in the damaged picture, you 'paint in' the corresponding red/blue/green values determined from the other print. Either way, you end up with a new photograph that looks like the old one did, and that's the important thing: you end up with a restored print to hang back on your wall.

Technically, Oracle recoveries apply deltas to existing data, rather than re-performing transactions as if done by a human being with very fast fingers. So that's the "truth" if you want to think in those terms. But the core fact is, recovery restores data and it doesn't really matter precisely HOW that's done: whatever description we give for the process will be "wrong", in either case, because only people who have seen the source code know *actually* how it's done. So, I just as happily thing of "very fast fingers" as "applying deltas", and in fact, I prefer that mental model. I just gave you the choice of models, in other words. But don't, please, confuse that with their being two mechanisms.

I have no idea why on Earth anyone participating in this thread is so hung up about the undo tablespace. The central question appears to be, 'How can an undo tablespace from Friday help in the recovery of a database the following Monday'. But I explained that the first time and 20 posts ago! Very simply, during a recovery, the undo tablespace gets rolled forward like any other part of the database. It gets redo applied to it just as much as the USERS tablespace does. At the end of the rolled forward process, your undo tablespace is effectively a Monday tablespace. It's fresh. It's got all the undo generated by the weekend's transactions in it. And there's a bunch of transactions which were uncomitted at the time the database blew up, so although recovery blindly replayed those transactions and therefore re-performed them, they are left there in an uncommitted state and SMON goes ahead and rolls them back (users also help, but SMON does the bulk of the work).

And SMON knows what to roll the uncommitted transactions back to because all the undo needed to do that was FRESHLY created by the recovery's roll forward phase.

You don't need to read anything into the sentence in bold that isn't there in plain English. You have to stop looking for deep and secret meanings and just read the words that are there: Your redo, whether it comes from the online logs or the archives, contains all the information necessary to recover the undo tablespace. Just as it has all the information necessary to recover ANY tablespace, in fact.

Think of an undo segment just as if it were the EMP segment, or the DEPT segment. You don't seem to be bothered about EMP or DEPT being restored from Friday and yet managing to be recovered to the state they were in on Monday. Neither should anyone be surprised that a Friday undo segment can be transformed into a Monday undo segment. That is, after all, what recovery does.

Recovery is the application of redo to a datafile to make it and the segments it contains -be they "ordinary" segments like tables and indexes, or more "exotic" segments like undo segments- more up--to-date. That means recovery is the "rolling forward in time of a datafile".

But when we start rolling forward, we can't predict the future. So when I see "update EMP set sal=900" in the redo stream, I do not yet know whether you managed to commit that or not. I can't see ahead. So I just blindly re-play that update and keep my fingers crossed. And I do that for every transaction recorded in the redo stream. And in the process of replaying that transaction, I also re-generate the fact that the original salaries in the EMP table were 800... which means I've just re-generated the undo for the EMP transaction.

Only at the end of the roll forward phase can I look around and see, 'Ah, that one wasn't committed; neither was that one; and this one wasn't even finished when the database blew up'. I therefore set SMON to work rolling those uncommitted transactions back... and it is at THAT point that the undo tablespace, storing that freshly-recreated undo, becomes vital for completing the recovery process.

In words of few syllables: every recovery requires a roll forward and a roll back phase. Redo lets us roll things forward. Undo allows us to do the roll back. The undo tablespace is vital to recovering a database, therefore, because without it, half the job couldn't be done.

Sidhu

Google friendliness of ...

Being a new kid on the block, I think, my post will not fire any serious and "scary" discussion as it has happened many times in the past. Just writing my experience. Whenever I search something related to Oracle in Google there are few sites that are bound to come up in the very first results ( No surety about the relevance and completeness of the content, though). Today I was searching about scheduling new jobs in oracle and I was, sort of, surprised to see the results [Though, I got from there, what I was looking for & I would like to say Thanks for that]. Many other times also, I have seen these websites pop up like anything.

When I had started my job and after that put the CV on few job sites. Everybody used to say: put more number of keywords in the CV as their search bots select the resumes on the basis of keywords only. Perhaps same thing applies here also. They have included each and every possible keyword in Oracle on their websites [& I think in a better way than it's been done in Oracle documentation ;)]. If its not one of their own websites then some books website (that also their own, obviously) will come up [& the keywords matched here are from table of contents or some portion from some chapter Ctrl+C'ed and Ctrl+V'ed there] with advertisement all around imitating the big bang universe theory. Some special experience, with making websites Google friendly, they have got ;)

Sidhu

Learning AWK...

I was searching for some good tutorials on awk. Found a very nice (brilliant indeed) article on Oracle website by Emmett Dulaney. A very good introduction for beginners. I searched for some other links as well. Have a read:

1. AWK: The Linux Administrators' Wisdom Kit

2. A Guided Tour Of Awk

3. AWK Programming

4. UNIX Utilities - awk

Happy awk'ing :)

Sidhu

Tracing Wikipedia...

Today Stumble gave me a link to an article on a website. That article talks about a guy Virgil Griffith who wrote a piece of software to track IP addresses of the people, editing the Wikipedia content. Some really interesting things came up: Apple attacking Microsoft and then Microsoft taking revenge. Have a look at the full article. It makes an interesting read. Read more about this guy on Wikipedia here.

Sidhu

Being an Oracle trainer...

Few days back, I had to give Oracle DBA training to a group of about 20-25 semi-technical people (Semi-technical, because most of them were not really DBA kinda folks doing all the techie stuff with Oracle, but in-fact having learned some bits & bytes of Oracle sometime back and these days looking into application functionality from technical perspective). I, having just about 10 months of experience with DBA profile, had to cover, everything about Oracle starting from creating database and up to performance tuning :) Its really an interesting job if the audience is good. But not an easy game. You have to know everything and have to be ready to answer people's queries (some stupid & dumb questions also :) I had to cover a total of about 350 slides in a day or less. So at times, really went fast and skipping some of things. It was a nice experience as a whole :)

& in the end when I finished it, was dead tired :(

Hats off to the trainers, who stand for the full day and that too for many days continuously :)

Sidhu

Importing a full database...

Many times, we are required to restore a database from an export dmp file. Its a simple task but sometimes there are some issues left like invalid objects or some objects missing, in the newly created database. Following steps, followed in order can help in creating an error free database:

1. Create a blank database: The very first step is to create a blank database which is to be used as the target database. That can be done using Database Configuration Assistant. (In last step of the DBCA, change redo log file sizes to 500 MB each (or some appropriate values depdening upon the size of the databaes), as during import, lot of redo will be generated, so large redo size helps in that scenario)
   
   
2. Extract DDLs and create tablespaces: Now run the import with show=Y option and create a log of all DDL statements. The main things to be looked for in the log are DDLs to create tablespaces and DB links. You may need to change the create tablespace statements according to the version of the Oracle you are using. If you have the export taken in an older version, where dictionary tablespaces were being used, you will need to change the statements accordingly, to create locally managed tablespaces.
   (If you have the dmp file in compressed (.Z) format check here, to run the import directly from compressed file)
   
   
3. Adjust the size of SYSTEM, TEMP, USERS and UNDO: As SYSTEM, TEMP, USERS and UNDO tablespaces will get created with the database itself, so you can alter the sizes as per the sizes in the old database.
   
   
4. Edit tnsnames.ora and create dblinks: Now edit tnsnames.ora to include all the databases used in the db links and create db links using the statements from DDL log.
   
   
5. Run the import: Finally, run the import with FULL=Y option and after the import finishes, look for any errors in the log. At last, compile all the invalid objects in the database. (If the import terminates with ORA-01435, then have a look at this post.)

To read about all the options with imp have a look at Original Import & Export Utilities chapter of Oracle Utilities guide.

Sidhu

@ my baby...

A very funny and interesting thing, a Chinese couple wants to name their baby "@".

Sidhu

Oracle 11g...

The day when Oracle 11g was made available for download on OTN, there was sort of, flood of posts in the Oracle blogsphere. Here is a quick recap of few of the posts (Whatsoever I could find through OraNA and my Netvibes)

Eddie Awad about 11g, I think he was the first one to post

Then Doug Burns here

Howard on installing 11g

Another interesting article from Howard

Tim Hall about 11g

Tim Hall on installing 11g

Then Laurent

An article on ADR by Virag Sharma on his blog

Jaffar about Active Standby database

A 11g PL/SQL article on AMIS blog

Well, if you don't want to get into any hassles and just want to download Oracle 11g, you can get it here. (As of now its available for Linux x86 only).

Sidhu

Why Linux cries about "1024 cylinders thing" at the time of installation...

I have been installing Linux for last 6 years and for more than half the number of times, came across a message something like "This partitions is beyond the 1024 cylinder boundary and may not be bootable". But never cared for it much and understood what exactly it meant to say ?

Yesterday I was reading System Admin guide to Linux by Lars Wirzenius (Thanks Howard for the link :) From there I came to know what exactly that message meant. Quoting from the guide itself:

Unfortunately, the BIOS has a design limitation, which makes it impossible to specify a track number that is larger than 1024 in the CMOS RAM, which is too little for a large hard disk. To overcome this, the hard disk controller lies about the geometry, and translates the addresses given by the computer into something that fits reality. For example, a hard disk might have 8 heads, 2048 tracks, and 35 sectors per track. Its controller could lie to the computer and claim that it has 16 heads, 1024 tracks, and 35 sectors per track, thus not exceeding the limit on tracks, and translates the address that the computer gives it by halving the head number, and doubling the track number. The mathematics can be more complicated in reality, because the numbers are not as nice as here (but again, the details are not relevant for understanding the principle). This translation distorts the operating system's view of how the disk is organized, thus making it impractical to use the all-data-on-one-cylinder trick to boost performance.
.
.
.
When using IDE disks, the boot partition (the partition with the bootable kernel image files) must be completely within the first 1024 cylinders. This is because the disk is used via the BIOS during boot (before the system goes into protected mode), and BIOS can't handle more than 1024 cylinders. It is sometimes possible to use a boot partition that is only partly within the first 1024 cylinders. This works as long as all the files that are read with the BIOS are within the first 1024 cylinders. Since this is difficult to arrange, it is a very bad idea to do it; you never know when a kernel update or disk defragmentation will result in an unbootable system. Therefore, make sure your boot partition is completely within the first 1024 cylinders.

Hope it clears the logic why Linux cries about 1024 cylinder issue at the time of installation.

You can read the guide online from the link above and download the pdf here. Its simple and concise and just too good. Small thing covering much :)

Sidhu

Plugged-in back to www...

Last to last week, we shifted to a new house. As there was no internet connection, so we were without any internet access for last 2 weeks. Today we got the new internet connection. Its a DSL one. And the guy who came to do the installation threw a little bit of technical jargon like rebooting the router and so on.

It feels so good to be back in the world of www :)

Sidhu

Moving datafiles,control files and log files-1

Many times you need to move datafiles from one location to another. The simplest approach for this is to take the tablespace offline, copy the datafiles to new location, rename the files with alter database rename file (Except that you dont have to move the SYSTEM and UNDO tablespace, as you can't take SYSTEM tablespace offline)

SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

Well lets try moving USERS tablespace.

SQL> select substr(file_name,1,50),tablespace_name from dba_data_files where tablespace_name='USERS';

SUBSTR(FILE_NAME,1,50)                             TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORADATA\ORCL1\USERS01.DBF                USERS

SQL>

The current location of the datafile is c:\oracle\oradata\orcl1. Suppose I have to move it to c:\oracle\oradata. First take the tablespace offline

SQL> alter tablespace USERS offline;

Tablespace altered.

SQL>

Now copy the datafile to the new location.

C:\oracle\oradata\orcl1>copy USERS01.DBF c:\oracle\oradata\USERS01.DBF
1 file(s) copied.

C:\oracle\oradata\orcl1>

Now make database aware of the new location of the datafile using alter database rename file.

SQL> alter database rename file 'C:\ORACLE\ORADATA\ORCL1\USERS01.DBF' to 'C:\ORACLE\ORADATA\USERS01.DBF';

Database altered.

SQL>

The last thing is the bring back the tablespace online. If everything has gone rightly the message like this will appear and you can view the new location of the datafile.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select substr(file_name,1,50),tablespace_name from dba_data_files where tablespace_name='USERS';

SUBSTR(FILE_NAME,1,50)                             TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORADATA\USERS01.DBF                      USERS

SQL>

In next post we will discuss moving datafiles, controlfiles and logfiles.

Sidhu

Batch file for ftp'ing files...

Today I came across a requirement where users needed to ftp files time and again. So ftp'ing again and again is not a very good option. I wrote a small batch file for the same. Just sharing the same over here. I created a folder ftp in C drive and a file get_file.bat
Contents of get_file.bat are:

set /p file_name=Enter the name of the file you want to ftp:
echo oracle>c:\ftp\param.cfg
echo oracle123>>c:\ftp\param.cfg
echo cd /home/oracle>>c:\ftp\param.cfg
echo lcd c:\ftp>>c:\ftp\param.cfg
bin
get %file_name%
ftp -s:param.cfg 127.0.0.1

It will create a file param.cfg having all the things like username, password and command to get the file in the same folder (c:\ftp). Then we invoke ftp with -s option with specifying the file param.cfg. It will ask the user to enter the file name and ftp the file from server to c:\ftp

Sidhu

Import ORA-01435: user does not exist...

One may encounter this error while importing from a dmp file from older versions of Oracle. Genereally this error is caused by some statement like alter session set current_schema=scott; And the simple reason is that the user scott doesn't exist. Yesterday I came across this error. And the reason was that user was not created. As in case of import we generally create tablespaces first (by creating the DDL using option show=Y) but creation of users is done by import itself. In older versions of Oracle, the temp tablespaces were no different from other tablespaces. But in newer versions temp tablespaces are different. So in dmp files from thoese older versions create user statements are written like create user t1 identified by t1 default tablespace temp temporary tablespace temp. This thing worked fine in older versions but in newer versions we cannot specify the TEMP tablespace as the default tablespace for a user. So the statement create user t2 identified by t2 default tablespace temp temporary tablespace temp throws ORA-12910: cannot specify temporary tablespace as default tablespace. In such cases the users (for which default statement is specified as TEMP) have to be created manually by specifying the appropriate tablespace as default tablespace and then the import should be run with ignore=Y.

Sidhu

Hats off...

Since I switched to this new job, my profile has changed. Here I work as a DBA. So my interaction with Oracle & anything related to Oracle has also increased. Started exploring Oracle related forums and websites specially OTN forums (http://forums.oracle.com) This is the only page thats almost always open on my Desktop in office & Laptop at home and I refresh more than my office Lotus Notes. Today I was reading APCs blog , that Jonathan Lewis has also started posting on OTN forums and being so busy man, from where he finds the time ? I too think the same. There are so many people having many years of experience in industry, answering the questions on OTN, uesnet groups and various other forums and everything is for free. They are not paid anything for the same thing. Its like taking time out of your time, understand somebody's problem, create same scenario your PC, try out and then post the answer ! I am, sort of new to the forums and sometimes for whole of week, I am unable to post any answers, even knowing something about the issue someone has posted. Just the "time" thing. These days I am very close to OTN forums, visit for whole of the day and also post answers to the questions I know something about. There are many people who are regular visitors and are answering questions on the daily basis. Hats off ! to all these "big bosses" of the technology !

Sidhu

Shell script to spool a no of tables into .xls files...

On OTN someone asked a question that how to spool data from a table into a xls file. Spooling a single table I discussed in one of the previous posts. We can use the same approach to spool data from more than 1 table also. Well here I will do it through a shell script and assume that you have a text file having list of tables to be spooled (Even if you don't have one, it can be easily made by spooling the names of tables into a simple text file) Here is the shell script that you can use to spool data to various xls files, table wise.

cat list.txt | while read a
do
echo "spooling $a"
sqlplus username/password@string <<EOF
set feed off markup html on spool on
spool /home/oracle/$a.xls
select * from $a;
spool off
set markup html off spool off
EOF
done

I didnt see any work around for Windoze as SQLPLUS << EOF thing doesn't seem to work in Windows. Will try to find some alternative. If you come across something, do let me know.

Sidhu

Think before you type...

Today morning, I was reading an article in The Times of India. The title "Your darkest secrets in office comp" was pretty interesting, article was too. Talk about monitoring of the things you do on your office computer. Well, the question is "is it right to monitor everything that they do ?" There is no denying the fact that there are people who are sort of "threat" to company policies, security and whatever...but the other side or opinion is that for people working in IT companies spend most of their time sitting in the offices (due to workload, end dates and all...) So the office life is not only their professional life, its personal also. Very less they interact with society (not like others, at least). Their friends, close ones are again the people working in IT companies. So if one sends a mail containing something personal sort of, to his friend from his office id and the same is being monitored (again by humans). This is definitely not fair. Not only this monitoring there are hell lot of other things too...

Sidhu

Is multiplication faster than division ?

An interesing post by Laurent. Check out http://laurentschneider.com/wordpress/2007/06/to-divide-or-to-multiply.html

My findings on 10gR2 on Windoze XP

SQL> var z number
SQL> var y number
SQL> exec :z := power(2,102)*2e-31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL> set timi off
SQL> print y

      Y
----------
9.988E-126





SQL> exec :z := power(2,-104)*2e31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> set timi off
SQL> print y

      Y
----------
9.988E-126

SQL>

Sidhu

Spool to a .xls (excel) file...

A small tip, I read on OTN about spooling to a .xls (excel) file:

It goes like this

set feed off markup html on spool on
spool c:\salgrade.xls
select * from salgrade;
spool off
set markup html off spool off

And the xls it makes shows up like:

Sidhu

Oracle concepts...

A very nice series of articles by Howard Rogers about Oracle Concepts. It includes all the basics like what a database, instance is ? Various types of files and all basic stuff. Read it here

Sidhu

Why girls don’t love software guys!!!

I really loved this one...
:)))

Sidhu

IMPORT & EXPORT from/to compressed files directly...

Well, a simple method to import(export) directly to(from) compressed files using pipes. Its for Unix based systems only, as I am not aware of any pipe type functionality in Windows. The biggest advantage is that you can save lots of space as uncompressing a file makes it almost 5 times or more. (Suppose you are uncompressing a file of 20 GB, it will make 100 GB) As a newbie I faced this problem, so thought about writing a post.

Lets talk about export first. The method used is that create a pipe, write to a pipe(ie the file in exp command is the pipe we created), side by side read the contents of pipe, compress (in the background) and redirect to a file. Here is the script that achieves this:

export ORACLE_SID=MYDB

rm -f ?/myexport.pipe
mkfifo ?/myexport.pipe
cat ?/myexport.pipe |compress > ?/myexport.dmp.Z &
sleep 5
exp file=?/myexport.pipe full=Y log=myexport.log

Same way for import, we create a pipe, zcat from the dmp.Z file, redirect it to the pipe and then read from pipe:

export ORACLE_SID=MYDB

rm -f ?/myimport.pipe
mkfifo ?/myimport.pipe
zcat ?/myexport.dmp.Z > ?/myimport.pipe &
sleep 5
imp file=myimport.pipe full=Y show=Y log=?/myimport.log

In case there is any issue with the script, do let me know :)

Update: If you are on Wintel, you can directly use a compressed folder as an export target. No need to create a pipe as the file system will automatically do it for you. ( Thanks Noons for the tip)

Sidhu

Burning your "OC", whyz and howz ...

The very first question that may come to your mind is: what the heck this "OC" is ? Well if PC stands for Personal Computer then OC stands for Office Computer ;) The story behind all this is:

One of my friend (He is working in a non-IT company) called me and asked how to burn a PC so that it no more works :) Why ? because the PC they are given is too slow & old and thats the only way they can get a new one. I thought for a while and 1 or 2 ideas came to my mind. But to dig into little more details I called my friend Vaibhav ( The guy I call to "discuss" and "know more about" all the techie things) He gave some really nice ideas. Combining all the thoughts, here is the summary of all the methods that you can use ;)

1. Water the motherboard: Add a bit of salt to water, fill a syringe and use it to spread water on the motherboard before booting. It will blow the motherboard.
2. Stop processor's fan: One more thing put something in the processer's fan to stop it so that processor gets heated and it stops :) but here chances are more that the system will shutdown or restart.
3. Break pin(s) of processor: Another interesting and simple thing that you can do is, break one or more pins of processor and put it back in the slot.
4. Remove one or more ICs: Another thing that you can do is remove one or more ICs from the board. As the board is already so clumsy, nobody can see that anything has been removed from there.
5. Use Pencil: Use a carbon pencil and rub it on the golden plating of RAM slots, any ICs near CPU or BIOS. Its gonna spoil that ;)
6. Cut some wire: A very simple method you can use is that cut some wires or data cables in such a way that nobody can see it ;)

One update from comments posted by Vaibhav: Most of u might not know abt the voltage selector switch at the back of the SMPS in the CPU tower. It is there to select between 110V(american) and 230V(indian). The trick is to just switch it to 110V :) The SMPS has no protection of over voltage at this high level and will burn instantly.

Advantages:
-No need to open PC
-No Smoke or sparks
-Nothing can be found on u if checked(like syringe, screw driver or wire cutter)
-Instant
-One Second Job
-Can be done again and again if system repaired

Disadvantages:
-Replacing SMPS makes the system operational

Well, few things you can try if you too have an old computer in the office. Best of OC Burning :P

Sidhu

Tom Kyte...

Well, a really old post on Google groups (uesnet:comp.databases.oracle.server). Someone posted a thread about Tom Kyte and then people responded with their thoughts (perfectly as expected). Read it here.

Sidhu

Run levels in *nix...

If you are crazy@*nix, 2 really interesting posts about run levels in *nix

• No runlevels??
• What is the use of runlevels?

Sidhu

Using NTLDR to boot Linux...

Sometimes, while installing Linux, installing LILO/GRUB to MBR makes you run into loads of issues, one of the most popular being that after reboot you are not able to boot into either of the OS ;) There is a way to use NTLDR to boot Linux, this way the MBR remains untouched and if you don't want to see the Linux option, what you need to do is, edit your boot.ini and your are done.

For this while installing GRUB, install it to the partition where you are installing Linux, instead of MBR. Now after rebooting, Linux will not boot as the partition on which you installed GRUB is not active. What actually we will do is, copy first 512 bytes of the partition where GRUB is installed, make a bin file, copy it to C drive and add the path of the same to boot.ini. So now, when you select Linux from the list of options displayed, NTLDR will call GRUB and then GRUB will boot Linux, just like normal.

There is a small utility called bootpart that does this all for us. Here is the direct link. Extract it and go to command prompt.

C:\bootpart>dir
Volume in drive C has no label.
Volume Serial Number is 08E8-E412

Directory of C:\bootpart

05/05/2007  04:19 PM              .
05/05/2007  04:19 PM              ..
08/01/2005  02:26 AM              32bits
08/01/2005  02:06 AM            44,544 bootpart.exe
08/01/2005  02:06 AM            12,055 bootpart.txt
08/01/2005  02:06 AM               119 bootpart.url
08/01/2005  02:06 AM               383 file_id.diz
           4 File(s)         57,101 bytes
           3 Dir(s)   4,188,106,752 bytes free

Run bootpart, it will show all the partitions on the disk like

C:\bootpart>bootpart
Boot Partition 2.60 for WinNT/2K/XP (c)1995-2005 G. Vollant (info@winimage.com)
WEB : http://www.winimage.com and http://www.winimage.com/bootpart.htm
Add partition in the Windows NT/2000/XP Multi-boot loader
Run "bootpart /?" for more information

Physical number of disk 0 : 282d282d
0 : C:* type=7  (HPFS/NTFS), size= 25599546 KB, Lba Pos=63
1 : C:  type=c  (Win95 Fat32 LBA), size= 11751547 KB, Lba Pos=208828935
2 : C:  type=d7 , size= 1052257 KB, Lba Pos=232332030
3 : C:  type=f  (Win95 XInt 13 extended), size= 78814890 KB, Lba Pos=51199155
4 : C:  type=7   (HPFS/NTFS), size= 25607578 KB, Lba Pos=51199218
5 : C:  type=5   (Extended), size= 35736592 KB, Lba Pos=102414375
6 : C:  type=7    (HPFS/NTFS), size= 35736561 KB, Lba Pos=102414438
7 : C:  type=5    (Extended), size= 15366172 KB, Lba Pos=173887560
8 : C:  type=83     (Linux native), size= 15366141 KB, Lba Pos=173887623
9 : C:  type=5     (Extended), size= 2104515 KB, Lba Pos=204619905
10 : C:  type=82      (Linux swap), size= 2104483 KB, Lba Pos=204619968

Now the one in Red is my Linux partition. Now run bootpart 8 c:\linux.bin (Here 8 is my Linux partition number and linux.bin is the name of the file which it will create in C drive) It automatically adds the entry to boot.ini So now you are ready to go. Just reboot and you will see 2 options there. Windows & Linux :)

Happy NTLDR'ing...

Sidhu

Command line history in SQL (for Linux)...

Found a very interesting article on Dizwell's blog. It was about keeping history of the SQL commands in SQL Plus on Linux. It is almost very simple. Just need to download a small utility called rlwrap from here. Its a tar.gz file. Download it, un-tar using

tar -xvf rlwrap-0.28.tar.gz

It will create a directory with the same name. cd to the directory and run

./configure

Now do

make install

(I was logged in as oracle user, then did su, but it gave some errors, finally I logged in as root and it worked fine)

Now what is left to be done is make an alias for sqlplus as

alias sqlplus='rlwrap sqlplus'

Using up/down arrows, commands can be scrolled up and down just like windows. Have a look at full article here.

Cheers

Sidhu

Reading explain plan of a SQL query...

Well, some information about how to go about generating and reading explain plan of a SQL query. Explain plan gives information about the access path that Oracle is going to follow to execute your query [explain plan doesn't actually execute the query, it just tells about the access path that Oracle will follow to execute the query, that also in current session, with all the current settings, it may be totally different in another session on the very same database] So after reading the explain plan for a particular query, we can see that is it executing efficiently ? are the indexes being used ? and so on. Let us go through the basic setup required to use explain plan.

First of all we need to create a table called PLAN_TABLE where explain plan will store the query plan. To create this table run $ORACLE_HOME/rdbms/admin/utlxplan.sql [Run it from sys, then grant all on PLAN_TABLE to public, so that everybody can use the same table]

Now we are ready to use it. Well, lets use a query based on emp and deptno tables.

select * from emp,dept
where emp.deptno=dept.deptno;

To see explain plan of this query we write

SQL> explain plan set statement_id='q1' for
  2  select * from emp,dept
  3  where emp.deptno=dept.deptno;

Explained.

SQL>

This is the syntax for using explain plan. set statement_id we use to store multiple plans in the plan table. Then we write for and the SQL query we want to see explain plan for. SQL Plus will prompt "Explained" and we are back to SQL prompt. Now the plan has been stored in PLAN_TABLE table and we need to read it. For that Oracle provides us with a script called utlxpls.sql (in $ORACLE_HOME/rdbms/admin/). [I generally create the same script in bin folder, so that to run it I can simply write @utlxpls]

SQL> @utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |       |
|   1 |  NESTED LOOPS                |             |       |       |       |
|   2 |   TABLE ACCESS FULL          | EMP         |       |       |       |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |       |       |       |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT     |       |       |       |
----------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note: rule based optimization

17 rows selected.

SQL>

These are the details that explain plan has provided us. Now there comes interpretation of this information. First of all to figure out what happens first, second and so on ? Well this is shown by the indentation of various steps in plan_table_output. The rightmost step (the most indented one) is executed first and then the 2nd most indented and so on. So in our case step with id 4 is executed first, then 2 and 3, and then 1. In predicate information we can see that.......

to be continued... ;)

Utilities I cant survive without...

Well, here I am listing down the important tools and utilities I seem to use on the daily basis and without them life on internet & laptop really seems to be stuck, sort of :(

1. Winamp: No need to say anything. Winamp is possibly the best and lightest mp3 (these days videos also) player. Just run Winamp in the system tray and enjoy

2. Winrar: Utility to uncompress compressed files. Earlier winzip was my favorite but recently I swichted to Winrar as Winzip doesn't support rar format. I am pretty happy using it.

3. Mozilla: I have almost stopped using Internet Explorer (except with some of the websites which say flat NO, sort of, to Mozilla). It is really good. With this I have stopped using any standalone download manager (DAP and Flashget have been among my favorites) also as Mozilla has one inbuilt and there are many addons also available.

4. Messengers: The latest communication channel: messengers. Latest versions of Yahoo, Gtalk and Skype.

5. uTorrent: Recently I started using torrents also. uTorrent, I am using as torrent client. It is light weight and pretty heavy to use. The only issue I have with it is that there is no feature like automatic bandwidth management. I have manually set a speed for download and upload as assiging the full badwidth to utorrent screws up normal surfing. And with manual settings when I am not surfing some of the bandwidth goes wasted ;)

6. Realplayer Alternate: I was a typical user who would stick with Real Player though its pretty heavy and eats a lot of resources. Sometime back my friend Vaibhav suggested me to go for Real Player alternate. I am happy using it now. very simple, small and precise :)

7. Replay Music: Just a new thing in my life too. A tool to record streaming audio, infact any audio coming out of sound card. Good one, saves as mp3 and has a very simple interface, just start, stop buttons and you are done.

8. RSS reader: Again a new item I am experimenting with. Till now I havn't been able to find a good one. Generally the interface is clumsy and you don't enjoy reading in that small window. I used RSS Reader first and using Omea Reader these days.

9. FSL Super finder: A replacement of windows search. I never liked (I seem to hate, indeed) windows search after Windows 2000. In XP it is totally screwed up. This one is a free utility with good interface and speed.

Use of all these tools makes me a happy user & surfer :)

Cheers
Sidhu

NO_DATA_FOUND...

I was going through Eddie Awad's blog. There was one post about an article about NO_DATA_FOUND exception in Oracle. Both the posts you can find here and here. Do read. It is really interesting.

Sidhu

{ Height of } Helplessness...

There are times when you just can't do anything, I repeat, you just can't do anything. In the evening I was working on my laptop and everything was fine. Next day in the morning when I got up, switched on the laptop and clicked on IE icon, it happily gave a beautiful error message "This application failed to start because msvcrl.dll was not found. Re-installing the application may fix the problem." So it was the time for Googling about the error. Thank God I had Opera installed. After 2-3 hits I came to know that it was a trojan. Finally, there was one dll which needed to be deleted to get rid of this error message :) But after that there was some problem with IE in opening some of the sites. One option was to upgrade to IE 7 but don't know why, I havn't started liking IE 7 yet. So no other option left than restoring windows (I have Windows XP Media Center Edition installed).

On all the laptops HP creates a partition where they store all the crap to restore windows back to original condition. They call it recovery partition (It eats upto 11-12 Gigs of space and recovers nothing really :( In the recovery tool provided by HP, there are 2 options one is called normal recovery which will restore the OS and won't touch your data (They say so but I read many posts where people talked about having lost everything to this so called normal recovery :( another is called destructive recovery (I just love the name :-* ) which erases everything on the hard drive and restore back the system to factory shipped condition (Term courtesy HP). Obviously I didn't want to lose any data. So I tried with normal recovery and it didn't work actually. So I contacted HP for the same and asked about any other option. They said NO in a very stylish manner as technical people are taught to do.

Now the only option left was to go for destructive recovery. I got some blanks DVDs and burned the data so as to free my hard drive. Finally after a few hours of work my hard drive was ready to face destructive recovery. I ran the tool and it worked out something for around 40 mins showing me a progress bar for which I waited to complete as a lover would wait for his watch to strike the moment when his sweet heart has to come :) Finally it was 100% complete and system rebooted. You know to find everything intact as if not even touched. So started looking for why the hell this 12 GB recovery partition if it has to do nothing ?

I also had Fedora Core installed so one thought struck that might be possible it is not able to do because of those Linux partitions, so deleted those partitions also and then tried but of no use. Finally only one thing was left that delete all the partitions and give the whole space to one partition and then try. Thank God it worked and my laptop was back with a fresh installation of windows. (Later on I came to know that existing of more than one partition was the reason why normal recovery also didn't work and few more interesting things - in all the cases it didn't give any error that was not able to restore or something and in case of normal recovery they delete all the softwares installed later but their shortcuts will still be there ? ridiclous, foolish, stupid ). I already had made my mind to make Norton Ghost image of the C drive to avoid this operation again in future. Before making the image, many things had to be done like configuring internet connection, updating windows, installation of some utilities & uninstalling Norton Internet Security that I got pre-installed along with the OS.

I use Sify broadband for internet. So installed the sify dialer and when I tried to connect it gave one strange bloody,out of hell message that "You don't have any anti-virus installed, click here to download updated antivirus from Sify". I just can't understand why the hell Sify is worried about anti-virus on my system. As I said at sometimes you just can't do anything, had to install Norton Internet Security again to be able to connect to internet. Later on I got another command based dialer Supersify for Sify from internet developed by an individual (Many thanks to him (using it I can connect to internet with AVG Free anti-virus installed which is not recognised by Sify's dialer as a good anti-virus) & one of my friend Vaibhav who googled this thing for me and was with me on voice during whole of this story which lasted for a day, a good short story it could make on a TV channel broadcasting peoples' frustrations with the technology) So after sweating out for one full day my laptop was back to normal :)

PS: If you just got a laptop and Windows is in fine condition, please make an image of your C drive using some tool. Other wise probably you also have to write a blog ;)


Sidhu

Wikipedia...

Today when I reached office and opened my Lotus Notes there was a mail from Yahoo ! Groups Team that Yahoo will no longer support the use of IFrames to customize the home pages of yahoo groups. With IFrames there was a reference to Wikipedia. Just a thought hit me that if Yahoo is giving reference of Wikipedia to illustrate something it means Wikipedia is becoming a standard, a place to look for information about anything. Not only Yahoo, there are many other places where we see references of Wikipedia. A quick search in Google for definition of Wikipedia shows:

A free content, multilingual encyclopedia written collaboratively by contributors around the world. The site is a Wiki - anybody can edit and add to an article. Offers quick understanding on controversial issues. Strong in current affairs.

About its history Wikipedia itself says:

Wikipedia began as a complementary project for Nupedia, a free online encyclopedia project whose articles were written by experts and reviewed by a formal process. Nupedia was founded on March 9, 2000, under the ownership of Bomis, Inc, a web portal company. Its principal figures were Jimmy Wales, Bomis CEO, and Larry Sanger, editor-in-chief for Nupedia and later Wikipedia. Nupedia was licensed initially under its own Nupedia Open Content License, switching to the GFDL before Wikipedia's founding at the urging of Richard Stallman.

I see Wikipedia as one of the greatest resource on WWW. As usual there are some controversies and issues related with Wikipedia also, one of the major thing being reliability of the information.

Say whatever, Wikipedia rocks :)

Sidhu

Viruses, Intrusion attempts and Norton...

When I got my Laptop, along with it also got Norton Internet Security and 2 months subscription for updates & all. Whenever I would boot up the laptop Internet Security would open the window for configuration. I kept on cancelling it for 2 weeks or so. But one fine (read not fine :( day I configured it. It was very happy. Said Thank You at the end. But the best was yet to come. After 10-15 days Windows (XP media center is installed on my Laptop) started showing signs of strain ;) It won't start properly, after saying loading your settings it will stuck. Then I had to kill explorer.exe and start it again. With this also one strange problem that no startup items will be loaded :( I was discussing the same with one of my friend. He said that Norton must be the culprit. So I thought about uninstalling it. When I tried to uninstall it gave some strange error. Anyways it also told about one URL from where one could check details of the error. Finally when the uninstall started it gave one beautiful message "You still have 39 days of subscription left, if you uninstall the product you will not be able to reuse the subscription" :D

After uninstalling when I rebooted, Windows was perfectly fine. Then I enabled windows firewall and installed AVG Free Antivirus (One of my favorite when it comes to using resources and loading at startup). Everything in place now :)

Cheers !
Sidhu

Intelligent people, geeks and nerds...

I was checking Tom Kyte's blog. It was about an article titled A Note To Employers: 8 Things Intelligent People, Geeks and Nerds Need To Work Happily. Well first of all meaning of all three words from "define: * in Google"

1. Intelligent: having the capacity for thought and reason especially to a high degree

2. Geek: In computers and the Internet, a geekis a person who is inordinately dedicated to and involved with the technologyto the point of sometimes not appearing to be like the rest of us (non-geeks).Being a geek also implies a capability with the technology.

3. Nerd: A computer expert by aptitude and not mere training. Usually male, under the age of 35 and socially inept; a person whose tremendous skill with operating or designing computer hardware or software is exceeded only by his, rarely her, passionate love of the technology.

So all the three words refer to some exceptional class of people, not everybody ;) Here are my views about all the points:

1. Yes flexible timings is a big thing. Different classes of people are there. One that can work in morning, another late night so and so and one that can't anytime (but this article is not for those ;)

2. Yes everybody likes different kind of environment around. I like greenry and natural things, if possible :) & the nap thing i strongly agree to ;)

3. Except while sleeping and listening to some classical genre of music, I need light :)

4. Nice idea. & I ALMOST HATE those people talking on the phone everytime, specially sales people & managers.

5. Yes. No suits and formals. It is upto you whatever you like. Reid & Taylor Suits are ok for James Bond in 007 series but are not going to help in bringing up a server from crash or catching an exception that has propagated to 5th-6th calling program because somebody didn't handle it at proper place earlier :)

6. Not a big issue. Just need the company of like minded people and it is fine :)

7. Yes. No meetings just for the sake of meetings. We need work not meetings.

8. It hurts the soul.

So those are all my views about this. Do post what you think ?

Cheers !

Sidhu

Running Linux from inside Windows...

Running Linux from right inside Windows, just like a normal application !!! Idea looks cool. I was also searching for the same thing. After little bit of Googling got one link http://www.lifehack.org/articles/technology/beginners-guide-run-linux-like-any-other-program-in-windows.html This article explains everything about making it possible. (Thanks Kyle Pott :)

I had downloaded Fedore Core 7 Live CD. Tried same with this method. It is working perfectly fine. Rocking Infact !!! I have 1 Gigs of RAM in my laptop. It is giving good performance but will definitly rock with 2 Gigs.This is how it looks like on my laptop .

Cheers !
Sidhu

RSS Feeds...

For the last 4-5 months I was almost without a PC and internet. This problem was solved when I got my HP laptop last month and then internet connection this month. One thing I had heard much about was RSS feeds and also wanted to try the same because there were many blogs which I used to check regularly. I had an idea about the concept used by RSS feeds that you will need to install a piece of software on your PC, give link of the page you want it to monitor and it will inform you when there is something new since your last visit.(period) Nothing more I knew.

So as I generally do started looking for some good RSS reader(possibly the best ;). From the very first search in Google I opened the first link and downloaded RSS Reader and installed it. Then after installing, had to give the link of RSS feed for the page that you wanted it to monitor. It was very simple copy-paste kind of job. Just took around 2 mins. Now I have added many websites and blogs to it. Every time when there is something new it rings the bell :)
Some of my favourite links:

http://www.gurdasmaan.com/sforum/main.php Official Forum of Gurdas Maan
http://tkyte.blogspot.com/ Blog of Tom Kyte
http://asktom.oracle.com/ Hompage of Asktom
http://jonathanlewis.wordpress.com/ Homepage of Jonathan Lewis
http://www.jlcomp.demon.co.uk/ Website of Jonathan Lewis

Cheers !!!
Sidhu