Month: July 2009

Using /nolog

A colleague of mine recently asked me why I always started Oracle programs using a “/nolog” and then explicitly connecting rather than just passing in the username and password in one go.  Some years back, I worked with an Oracle guru who showed me exactly why you shouldn’t, just by using one simple command in UNIX:

ps -ef
UID   PID  PPID C    STIME TTY TIME CMD
oracle    4325  5180 0 12:00:04 ?   0:00 sqlplus system/manager
appluser  3118  3012 0 12:00:03 ?   0:00 sqlldr scott/tiger
appluser 26332 24101 0 11:00:15 ?   0:00 imp matt/matt full=y

As you can see – if you pass in the username and password, then anyone can easily find it out using ps!

So, how go you get round it?  If you can’t connect from within the tool (as you can in SQL*Plus), you can use a password file, which you should then delete once you have done the job, e.g. “sqlldr parfile=$PWDFILE….” where the file contains “userid=matt/matt”.

In order to make it even more secure, then you could use temporary files for your password file, e.g.

PWDFILE=$(mktemp)
echo "userid=matt/matt" > $PWDFILE
sqlldr parfile=$PWDFILE ....
rm $PWDFILE

mktemp is a utility which will create a temporary file with a unique name.

A need for updated documentation?

I was looking at the MySQL website recently to see whether they had updated much on there about the Oracle takeover of Sun, and consequently MySQL.  It seems, looking here that they haven’t 🙂

MySQL Enterprise Unlimited

Deploy an unlimited number of MySQL Enterprise Servers for the price of a single CPU of Oracle Enterprise Edition

They might want to have a think about updating that soon!

When does 1 != 1?

I was playing about recently with some basic calculations and rounding within the Oracle database, using release 11.1.0.6 (and I’m sure it’s the same in earlier databases releases).  I’d been busy with Dr. Kawashima’s Brain Training: How Old is Your Brain? on my Nintendo DS and doing the mental calculations, and was thinking about the basic calculation 1/3*3.

Now, following my PEMDAS (I had two years in an American school when I was younger – for those in the UK, think BODMAS) – the sum would be calculated as (1/3) * 3 so would give 1 as the result – try it in any calculator and you should get the same result.

Try it in Oracle, and you get the same:

SQL> SELECT 1/3*3 FROM DUAL;
1/3*3
-----
1

That’s all fine – now try a slight variation:

SQL> SELECT 'OK' FROM DUAL WHERE 1/3*3 = 1;
no rows selected

Hmmmm – so even though it returns 1, it doesn’t equal 1….  Is it true for all values?

SQL> SELECT * FROM DUAL WHERE 1/3*3=1;
no rows selected

SQL> SELECT level, CASE WHEN 1/level*level = 1 THEN 'OK' END ok FROM DUAL CONNECT BY level <= 20;
L O
———- -
1 OK
2 OK
3
4 OK
5 OK
6 OK
7 OK
8 OK
9
10 OK
11
12
13
14
15 OK
16 OK
17 OK
18 OK
19
20 OK

So, there you have it – a handful (and there are more) of examples where 1 !=1 in Oracle!!

Tom Kyte’s Interview Questions

I read this recently on Tom Kyte’s blog about three questions that he asks at interviews:

I have a table:

create table t ( …..,  month number, ….. );

Month is always a number between 1 and 12.

I ask three questions about this table:

1) how many rows are in the table

2) how many rows BY MONTH are in the table (i want to  know how many rows for month one, month two
and so on)

3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambiguous)

At an interview for a role that I did at some stage, I was asked the following:

I have a table EMP with columns NAME, SALARY, DEPTNO.  How would you write a query to give me the top three earners in each department?

I’m not sure if I got the answer right, but I got the gig 😀

Quick update

Well, it’s been AGES since I last blogged about anything, either here or on my other personal blog – apologies, I’ve just been really busy over the past few months.  I’m also working on a government project where I have no internet access, so my chances to catch up on anything out there have been limited, too.

Anyway, just a quick post to say that I’ve not forgotten about my blog, and to give you a couple of links to places that I have managed to read recently:

Tom Kyte’s blog @ http://tkyte.blogspot.com

Oracle WTF @ http://oracle-wtf.blogspot.com

I’ve also been reading some Ask Tom threads on “Top-N queries” which I was asked about in my interview for the current role.  Tom has some great points about the futility of doing a COUNT(*) to indicate how many records a query would return for allowing users to page through the results, and what a waste of resources it is.

Anyhoo – must dash 🙂

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close