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.

Posted in Oracle, Technical | No Comments »

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!

Posted in General Computing | No Comments »

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

Posted in Oracle, Technical | 1 Comment »

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

Posted in Oracle, Technical | No Comments »

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

Posted in General Computing | No Comments »