Oracle For SQL Server Developers, Vol. 1

When I first started working with the Oracle database back in 2001, I remember how difficult it was to figure ANYTHING out.  I had plenty of experience managing SQL Server, designing databases with SQL Server, and implementing applications with SQL Server but getting even the most basic tasks done in Oracle was a major feat.

Over the years I’d been thinking that I’d like to do something with what I learned about Oracle.  This year I got a talk accepted to VSLive New York 2008.  This forced me to brush up on Oracle in order to write my talk and I couldn’t believe that it was STILL nearly impossible to find any halfway decent information on how to do even the most basic tasks with Oracle.  To add to the pain, the Oracle community was spectacularly unwilling to help to the point of being almost openly hostile to questions.  “If you don’t know, then I’m not going to tell you” seemed to be the motto.  I also tried to *BUY* an Oracle support contract so that I could get my questions answered.  I called at least 10 different numbers on the Oracle website and no one at Oracle wanted to take my money.  Amazing. 

Well, kids — for those of you SQL Server people who have to develop and maintain applications with Oracle — this blog post series is for you. 

Tip #1: Oracle 11g runs at 100% processor usage on Windows 2008. 

I ran into this problem after a fresh install of 11g on a fresh, patched install of Windows 2008.  There were no users logged in to Oracle and therefore absolutely no load on the server and yet the processor was pegged. 

The workaround for this problem was to disable the Oracle “Console” service.  On my server with a default installation, this service was called “OracleDBConsoleorcl”. 

This fixed the problem of the processor being pegged but it disables the Oracle Enterprise Manager web site.  (shrug) I wish I had a better solution.

Tip #2: “Error: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.”

If you get this error, you haven’t installed the Oracle client software on your local machine yet. 

Go here and download the “Oracle Database 11g Release x Client for Microsoft Windows”. 

Extract the files and run setup.exe.

Eventually, you’ll see the following screen.

For a server that doesn’t need much other than to just connect, choose “InstantClient”.  For a developer workstation, choose “Administrator”.

Tip #3: How do you configure the Oracle Client?

Two files are important: sqlnet.ora and tnsnames.ora.  Sqlnet.ora configures global settings for how the Oracle client talk to the database.  Tnsnames.ora is kind of like the “hosts” file in Windows and sets up the networking details for all the known instances of Oracle that you’ll be addressing from the local machine. 

On my machine with a default install of the client, these files are located at
C:appusernameproduct11.1.0client_1networkadmin

If one of these files are missing, there are sample versions located at

C:appusernameproduct11.1.0client_1networkadminsample

Tip #4: After doing a default install of the Oracle client and adjusting Tnsnames.ora, you can’t connect to the database.

I’m not sure why this is the case or what this really means but I had to go to Sqlnet.ora and comment out the following line:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

To comment out the line, put a “#” at the start of the line.

# NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Tip #5: What is the SCOTT schema?

The SCOTT schema is a demo schema in Oracle like Northwind or AdventureWorks. It’s handy for doing demos and just banging around in the Oracle database. 

By default, the “scott” username is disabled.  To enable it, you can go into the Oracle Enterprise Manager and edit the user account.  On my server, using a default install of Oracle, Enterprise Manager was available by going to

https://servername:1158/em

The username for the SCOTT schema is always “scott” and the password is “tiger”. 

Tip #6: How do I create a new schema?

In Oracle, a schema is roughly equivalent to what we SQL Server devs think of as a “database.”  In SQL Server, in order to create a new database we issue the following command:

CREATE DATABASE databaseName

In Oracle, the simplest way to create a new schema is to create a new user.  Oracle Enterprise Manager is probably the easiest way to create a new user. 

Click here to read the next Oracle For SQL Server Developers post.

-Ben