What Operating System for Oracle?

What Operating System should I choose for my Oracle installation?

There is no convincing some people. They assume and believe that if you throw enough hardware at a problem all performance issues will be miraculously resolved. This means only buying the best iron and the most configurable operating system to get the most blazing performance ever seen on the planet.

This may be the case for the top 5 percent of companies where multi-processor machines, gigabytes of memory and terabytes of storage are just for the development machine (as if). Most companies can quite happily get by on a dual processor machine of adequate performance.

Then the arguments start. Which operating system should they use? The *nix diehards will choose their favourite Unix, lots will consider Linux and some will go with Windows. Now, I have worked with Oracle databases on all three (or should that be two) types of operating systems, and lately I have come to the conclusion that Windows is not the evil beast many consider it to be.

Before a flame war starts, consider this, many smaller companies may not have access to a Unix System Administrator / DBA / Developer. They are run on tight budgets and usually on systems where someone with a little knowledge is left in charge to get on with it. A scary situation but all too true in many circumstances.

This is where windows makes sense. Just about everyone can use windows. There are minimal configuration issues and the installation and maintenance of Oracle is relatively pain free. On Linux and Unix this is not always the case, there may be kernel parameters to change and rpms to install.

Surprisingly the performance differences are not all that great, and I anticipate more features and more control with the upcoming release of Oracle 11g. The limitations of the Windows operating system are well known, and for the majority of developers and users of Oracle technology the issue of what OS lies behind the database shouldn't even be a worry.

After all, Oracle on Windows behaves exactly like Oracle on Linux or Unix.

How to Interview.

DBA Interviews

I have just been involved in a series of interviews for a new Oracle DBA and I was quite upset by some of the candidates lack of knowledge or superficial knowledge. Unfortunately the interviews were a combined effort and I had to cram as many technical type questions as I could into the 30-45 minutes available while working through each applicants CV and then a quick question or two to determine their grasp of the fundamentals.

The role was advertised as Production DBA with some development experience, and buried in the job spec there was mention of PL/SQL and SQL. Over half of the candidates confessed to not "really knowing" PL/SQL because they are DBAs. Some went as far as saying that their SQL skills weren't all that good. What!!!!

After sifting through the huge pile of CVs and selecting those I thought best matched the role regardless of amount of experience. The theory being that someone more junior or with less experience will train into the job or, if they are determined enough, ask around and beg for work, anything to get experience. This is a large organisation and the chances for advancement are yours to make.

But I was shocked, shocked by the lack of preparedness for interview by most of the candidates and, more shockingly, lying about their skill set and experience. OK, I can understand inflating things by a small margin, but to change a passing familiarity with a subject into 5 years worth of experience on a CV is blatant lying. Needless to say, despite my best efforts at CV sifting, some blaggers got through. This annoyed me because I don't like giving up 2 hours of my work time, when I could be productive, to listen to someone out and out lie to me. Seeing as this all happens at the beginning of the interview then leaves me another hour or so to stew without revealing boredom or displeasure.

In the end we settled on a pleasant guy who was not the strongest technically, but crucially most impressed when it came to presenting himself. We actually interviewed someone who spent most of the time answering to the table, and this is for a role involving constant communication. He was open to all ideas, and was able to demonstrate to me the difference between what he knew (solidly) and what he knew (passingly). So we all know where we stand.

So take note if you are about to apply for a position. If you feel your lack of experience will let you down work on your presentation skills. As an industry we are deficient in people skills, but the people interviewing you either are technical themselves and understand the mindset or are in personnel and just don't understand coyness.

Yes it is very different being on the other side of the table for a change and enlightening. I would recommend that technical people take time to prepare well for the basic questions, the obvious questions, those are the ones that stand out.

Functions

SQL functions are broken down into character functions, number functions, and date functions. Be sure you know how to use these functions for the Oracle Certified Partner (OCP) exam:

Text Functions

• lpad(x,y[,z]) and rpad(x,y[,z]) Return data in string or column x padded on the left or right side, respectively, to width y. The optional value z indicates the character(s) that lpad() or rpad() use to pad the column data. If no character z is specified, a space is used.
• lower(x), upper(x), and initcap(x) Return data in string or column x in lowercase or uppercase characters, respectively, or change the initial letter in the data from column x to a capital letter.
• length(x) Returns the number of characters in string or column x.
• substr(x,y[,z]) Returns a substring of string or column x, starting at the character in position number y to the end, which is optionally defined by the character appearing in the position z of the string.
• instr(x,y) Determines whether a substring y given can be found in string x.
• trim() A single-row function that behaves like a combination of ltrim() and rtrim(). Trim() accepts a string describing the data you would like to trim from a column value using the following syntax: trim([[keyword] 'x' from] column). Here keyword is replaced by leading, trailing, or both, or it's omitted. Also, x is replaced with the character to be trimmed, or it's omitted. If x is omitted, Oracle assumes it must trim whitespace. Finally, column is the name of the column in the table to be trimmed.

Arithmetic Functions

• abs(x) Obtains the absolute value for a number. For example, the absolute value of –1 is 1, whereas the absolute value of 6 is 6.
• round(x,y) Rounds x to the decimal precision of y. if y is negative, it rounds to the precision of y places to the left of the decimal point. This can also be used on DATE columns.
• ceil(x) Similar to executing round on an integer, except ceil always rounds up.
• floor(x) Similar to ceil, except floor always rounds down.
• mod(x,y) The modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced.
• sign(x) Displays an integer value corresponding to the sign of x: 1 if x is positive, -1 if x is negative.
• sqrt(x) The square root of x.
• trunc(x,y) Truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point.
• vsize(x) The storage size in bytes for x.

List Functions

• greatest(x,y,…) Returns the highest value from the list of text, strings, numbers or dates.
• least(x,y,…) Returns the lowest value from the list of text strings, numbers, or dates.
• decode(column name, val1, sub1, val2, sub2,…) Works on the same principle as the if-then-else statement does in many common programming languages.

Date Functions

• add_months(x,y) Returns a date corresponding to date x plus y months.
• last_day(x) Returns the date of the last day of the month that contains date x.
• months_between(x,y) Returns a number of months between dates x and y. If date x is earlier than y, the result is negative; otherwise, the result is positive. If dates x and y contain the same day of different months, the result is an integer; otherwise, the result is a decimal.
• new_time(x,y,z) Returns the current date and time for date x in time zone y as it would be in time zone z.
• next_day(x) Identifies the name of the next day from given date, x.

Several conversion functions are available for transforming data from text to numeric datatypes and back, numbers to dates and back, text to ROWID and back, and so on.

Conversion Functions

• to_char(x) Converts the value x to a character or converts a date to a character string using formatting conventions.
• to_number(x) Converts nonnumeric value x to a number.
• to_date(x[,y]) Converts the nondate value x to a date using the format specified by y.
• to_multi_byte(x) Converts the single-byte character string x to multibyte characters according to national language standards.
• to_single_byte(x) Converts the multibyte character string x to single-byte characters according to national language standards.
• chartorowid(x) Converts the string of characters x into an Oracle ROWID.
• rowidtochar(x) Converts the ROWID value into the string of characters x of VARCHAR2 datatype.

Execute Immediate

This is one of those utilities that you use on a day to day basis without giving a second thought once you learn just enough to be dangerous.

The syntax is fairly straightforward:

EXECUTE IMMEDIATE {sql stmnt}
[INTO var.....]
[USING [IN | OUT | IN OUT] {bind}....]
[{RETURNING | RETURN} INTO {bind} ];

This parses and executes a sql statement in a single step. This is for any SQL statement except multi-row queries - for that you use a cursor.

Please note that the EXECUTE IMMEDIATE statement ends with a semi-colon but the SQL statement being executed does not.