Tuesday, November 16, 2010

Oracle fundamentals

If you are approaching Oracle (one of the biggest and widely used databases on the market) for the first time, there are some little hints you should (or, better, you MUST) know to start working on it.

First of all, you have to install an Oracle Client on your machine, in order to access your database(s).
No problems here, just launch the installer and press Next until it's over.

The second step is to add your specific database parameters to the Oracle configuration file, called TNSNAMES.ORA, and located under the path YOUR_ORACLE_DIRECTORY\network\ADMIN.

NOTE: Every time you will need to access a different Oracle database for the first time, you will need to add the data into this file, so it's importat to remember its name and location, and its configuration rules.



The configuration is quite simple, and well described in the Oracle Wiki:

# COMMENTS DESCRIBING THE INSTANCE BELOW (comments starts with # symbol)
<addressname> =

 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)
          (Host = <hostname>)
          (Port = <port>))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = <sid>)
 )
)


where addressname is the name of your database instance (i.e. MY_DATABASE), hostname is the physical address (i.e. "127.0.0.1" or "mydb.mydomain.com"), port is the port where database is listening (i.e. 1521), and sid is the System ID (i.e. MY_SID).

Note: When you will be asked to provide a Connection String to your database, that string will be composed in the form:

hostname:port:sid
, for example mydb.mydomain.com:1521:MY_SID

You will need that string every time you'll want to connect to your database directly through a software that doesn't depends on your computer settings, for example a Java web application (that runs on a server, not on your pc). In that case, according to your specific Application Server peculiarity, connection string will be something like jdbc:oracle:thin:@mydb.mydomain.com:1521:MY_SID.

But everytime you will need to connect to your database from your computer, through an utility like SQL*Plus or Quest Software's TOAD, you will refer the database using only its addressname, keeping the things easy, no need to remember stuff like port, sid or hostname, and no need to reopen the tnsnames.ora every time... just remember your addressname (MY_DATABASE in the example above).



Now you are ready to connect to your database.
Open the SQL*Plus utility, installed together with the Oracle Client and located (in Windows and by default installation) under the Programs Menu -> Oracle Folder -> Application Development.

It will ask you to enter the USERNAME, the PASSWORD and the HOSSTRING (addressname): enter them and press OK.
Congratulations, you are now browsing your database.

SQL*Plus has an owner syntax, you can find the details here; however, the simplest way to query your database is:
  1. Write your SQL statement, pressing Enter everytime you need a new line to keep the code clean (don't worry, it won't run);
  2. Press Enter once again on the last, empty row: this will end the command;
  3. Write RUN to execute the SQL statement (just recorded) in the SQL*Plus buffer.
Enjoy.

Note that SQL*Plus is an important, fast and powerfull tool, but you will use it mainly for debugging and forensic purposes.
It's very likely that you will be provided with a 3rd party softwares like TOAD or one of its freeware alternatives (take a look at Oracle's SQL Developer, SQLTools or TOra, the last available for linux too) in order to manage your database in a productive way.

No comments:

Post a Comment