Introduction to new Oracle users and developers at CERN

Are a new Oracle database user or developer at CERN? Please read on.
First of all, welcome to Oracle support services! The Physics Databases team is reachable at to help you. Below you will find a few references that we recommend to bring your development with CERN DB services up to speed: selected references to Oracle documentation, and how to connect to Oracle.

Links to Documentation

Oracle software comes together with extensive documentation. In case of RDBMS 10gR2 there are ~50k pages of documentation. It is well structured: there are books for both DBAs and developers.

For developers we recommend:

  • Application Developer's Guide - Fundamentals
  • Concepts
  • SQL Reference
  • PL/SQL User's Guide and Reference

Where to find Oracle documentation

Connect to Oracle DBs

There are several ways to connect to a database. The basic way is using SQLPlus, which is an application that comes with Oracle client installation and is also available from lxplus at CERN.

Basic concepts to know:

  • local_name - local name/db alias describing how to connect to database. Also referenced as the connect descriptor.
  • host - name of node(s) hosting the database
  • service_name - for clustered DBs, a service can be provided by one or more physical nodes

Using local naming (db aliases):

You have a environment variable $TNS_ADMIN pointing to a directory. In that directory you have a file tnsnames.ora with several entries in the format:
<local_name> = (DESCRIPTION=
                 (ADDRESS=(PROTOCOL=tcp)(HOST=<host1>) PORT=1521))
                 (ADDRESS=(PROTOCOL=tcp)(HOST=<host2>) PORT=1521))
                 (CONNECT_DATA= (SERVICE_NAME=<service_name>)))

You can connect by using:

   sqlplus user/password@<local_name>

The local_name can be different in any machine, as the name says, is a local name. You have to check in the $TNS_ADMIN/tnsnames.ora file of the local machine you are connecting from.

NOTE: local naming (tnsnames.ora) is the recommended method to connect to RACs

Using easy connect:

You don't need to have neither $TNS_ADMIN or tnsnames.ora file. However this only works if you use SQLPlus version >= 10.x . Another disavantage is that you cannot specify more than one host name (which can exist in case of Oracle RACs/clusters)

You can connect by using:

   sqlplus user/password@//<host>:<port>/<service_name>

More advices

This topic: PSSGroup > PhysicsDatabasesSection > BasicConceptsReference
Topic revision: r5 - 2007-11-23 - LucaCanali
This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback