TOAD for Oracle Freeware

There is a free version of TOAD, an old, restricted version. It can’t do some of the stuff the commercial version can (I’m currently looking for migration tools) but it looks to be a much nicer way of talking to your Oracle DB than the Oracle tools.

You can get hold of it here.

I’m still having problems with Migration between Access and Oracle using ANY of the Oracle tools, especially Oracle SQL Developer, which throw all kinds of exceptions even when I try to do something as simple as import .csv data.


Get used to seeing “Feature available in commercial version” as I did on “Menu -> Database -> Import”. Why not hide these features instead of dangling the functionality in front of us! (obviously it’s to make you go out and buy the commercial version, but its still a bit annoying).

Starting Local Oracle & Enterprise Manager on Solaris

I couldn’t figure out why my Oracle wasn’t auto starting when I rebooted Solaris. I tried the “dbstart” script but kept getting permissions errors no matter who I logged in as (probably my fault for setting ownership wrong somewhere). The solution was a bit basic but will do for now until we get auto starting working;

Log on as “oracle”, the user created during the pre-installation of Oracle 10g if you followed the installation documents. Open a terminal and start SQL*Plus from the Oracle “bin” directory by running:

sqlplus /nolog

If you are like me and hate unnecessary typing you added this bin directory to the “$PATH” environment variable. The “/nolog” means you can enter your connection info after SQL plus starts running, something I found I had to do in order to get it to connect to my local machine but not for remote machines.

Now you have a “SQL>” prompt connect as the “sys” account by typing in:

connect sys/password as sysdba

Now if you connected successfully you get a prompt back that says “Connected to an idle instance.” Next up type:


And that’s it, after a while it should start up your Oracle database and finally say “Database opened.” which means the database is now up and running and can be connected to remotely.

Now the Enterprise Manager Database Control (web interface) might have started up correctly when you started the database but for me it just didn’t. To get the status of the Enterprise Manager you open a terminal and type:

emctl status dbconsole

And more than likely it will tell you it is already running. I found out it wasn’t running by trying to start it, where it told me everything was fine and already started but I couldn’t reach the web page. Solution: turn it off and on again, if only everything worked like that.

emctl stop dbconsole

emctl start dbconsole

This fixed it for me, but now I have to figure out why Oracle is reporting it running at startup without it actually running. If the listener service isn’t running you will need to start this as well:

lsnrctl start


I tend to do everything in just 3 steps now, just to make things faster:

sqlplus sys/password as sysdba


then “exit” to quit out of sqlplus then the following line:

emctl stop dbconsole; emctl start dbconsole; lsnrctl start

Remote Connection to Oracle from Linux (Ubuntu) Using SQL*Plus

I followed instructions from the Oracle documentation to connect to our remote server with a public IP address from home and check that everything was running ok. It is easy when you know how, as usual!

First off, make sure you have enough swap space (over 1GB) to install the Oracle Express Client. I didn’t have enough so had to use a livecd and a third party partition manager to resize my swap partition to a reasonable size.

Next up I downloaded the Oracle Express Client from Oracle themselves (click here to download it) and installed it using the ubuntu package manager. Easiest way, double click on it, click install!

Now the oracle client is installed you have SQL*Plus, a command line interface to your remote and local databases. It isn’t great to use, no command completion, no remembering previous commands but it is the quickest way to get a SQL command line interface.

Open up SQL*Plus (in the main menu > Oracle Client 10g Express Edition > Run SQL Command Line) and now you have a SQL> prompt. This is where you enter “connect” and your connection string and user details etc. For example, connecting as “sys”:

connect sys/ as sysdba

The oracle default instance name (if you installed the example database) is “orcl”. So if your password is “foobar” and your ip address is “″ then you need to type into the command prompt:

connect sys/foobar@ as sysdba

Of course you can change the login to whoever you want and if you look at the documentation you will  find a better way to do this in future using a connection identifier. If you are successful then SQL*Plus will say “Connected.” and you now have a connection, it’s just like being there.

To test out your connection as “sys” try typing the following (thanks Viktor) into the terminal to list all the current schemas/users in the database:

select username from all_users;

You should get a list back and the number of rows selected (about 27 rows if you installed the example database). Now you know how to connect you can run any SQL commands (inc PL/SQL etc) you want plus all the SQL*Plus commands, which I am just beginning to get to grips with.

Creating Users and Schemas in Oracle

According to all the documentation when you create a user in Oracle it automatically creates a schema for that user. This is true but the schema (and all its associated object types) is not visible in the Enterprise Manager Console until you create a table as that user.

I did spend a while trying to figure out why the Schema wasn’t appearing but I guess it’s just another quirk of Oracle I’ll have to get used to.

TMP TMPDIR Temp Directories and Solaris Not Starting

If you set the TMP and TMPDIR environment variables in /etc/profile to a directory that does not exist Solaris will NOT load the desktop environment and you will not be able to log in. There is no error or warning, you just get booted back to the login screen.

Spotted this in an Oracle install when I was testing all the environment variables before actually creating the directories. The oracle directory I set in ORACLE_BASE does not need to actually exist when you log in as it is not used by Solaris itself but the TMP/TMPDIR directory does.

At some point I will write a step by step guide to installing Oracle 10g on Solaris x86 inside VirtualBox, at least then I will have something to follow myself without making mistakes like this!

Connecting to Remote Oracle Server with SQL*Plus in Windows

This was a huge pain at first but it turns out you just have to include the user and the connection string when you run sqlplus.exe from the command line eg:


And make sure your connection string is in the correct format in the “TNSNAMES.ORA” file (located in the same directory as sqlplus.exe) and your “TNS_ADMIN” environment variable is set to the directory the “TNSNAMES.ORA” file is located in. An example connection string is:

(SID = orcl)

Using TOAD for Oracle With a Remote Oracle Installation

I couldn’t figure out how to connect to my remote Oracle install on a VMWare Solaris install using TOAD until I found this page at trap17. Turns out you need to install the Oracle Instant Client (or at least have it installed somewhere) with the correct environment variables for TOAD to pick up your Oracle install. I followed this guide and it worked straight away:

Here’s how you do it (Although I’ve done my best to present an accurate procedure, I can’t guarantee this will work for everyone. Messing around with your environmental variables is not for the weak, proceed with caution):

1) Prepare your mind for information that should be used once and then quickly discarded. Otherwise the following information may stick to your brain and take up valuable space. You are warned.

2) Install the oracle instant client. I don’t know why…I kinda wish I did. You probably can get away with installing just the basic package. Unzip the files to a directory, and then copy the path of this directory to the clipboard.

3) Add this directory to the PATH environmental variable. To do this, right click on My Computer, go to the Advanced tab, and click ‘Environment Variables’. Under System variables, find ‘Path’. Select it, and click edit. ctrl-v to paste the directory into the string, and say OK. e.g.:

Before: %SystemRoot%system32;%SystemRoot%;C:Program FilesATI TechnologiesATI Control Panel;
After: %SystemRoot%system32;%SystemRoot%;C:Program FilesATI TechnologiesATI Control Panel;C:instantclient;

4) In the same window, under ‘User variables for …’, Click the New button. Call the variable ‘TNS_ADMIN’. ctrl-v to paste the directory path into the ‘variable value’ field. Say OK, and close out the ‘My Computer’ properties windows.

5) Create a file called ‘TNSNAMES.ORA’ in your instant client installation directory. In this file designate the connection parameters for your database. Mine looked something like this:

(SID = orcl)

replace the DB_ALIAS,, port, and SID with your settings. If you don’t know what any of this means, good for you, you’ll live longer.

6) Reset your computer, fire up Toad, and then open a new connection. You should see DB_ALIAS as an option under the database field. Set your username and password, hit connect, and you should be in business.

I guess Toad looks in certain places for the drivers and configuration files it needs to connect to oracle. Setting the environmental variables tells Toad where to look. If you had a local install of Oracle I imagine you could fire up Toad and all the files it needed would already be in the right place, but you can follow these steps instead. Woot!

Fixing ORA-27102 Out Of Memory Errors in Oracle Install on Solaris 10

Found a page on the java forums which explains how to get around this error:

Unlike earlier releases of Solaris, most of the system parameters needed to run Oracle are already set properly, so the only one you need is the maximum shared memory parameter. In earlier versions this was called SHMMAX and was set by editing the /etc/system file and rebooting. With Solaris 10 you set this by modifying a “Resource Control Value”. You can do this temporarily by using prctl, but that is lost at reboot so you will need to add the command to the oracle user’s .profile. The other option is to create a default project for the oracle user:

projadd -U oracle -K “project.max-shm-memory=(priv,2048MB,deny)”

what this does:
- makes a project named “” with the user oracle as it’s only member.
- because the name was of the form “user.username” it becomes the oracle user’s default project.
- the value of the maximum shared memory is set to 2gb, you might want to use a larger value here if you have more memory and swap.
- no reboot is needed, the user will get the new value at their next login.

The Oracle install on Solaris is a pretty long and complicated series of steps. It really is worth reading all the documentation from Oracle themselves before you even start to install..