How to connect to database

Required information about connection

In order to establish connection with database you need to have DB user's credentials:

username
password

Moreover, you need to know database server's TNS. It concerns such information as:

host address
port number
SID or Service name (one of these two)

Note: usually TNS information is provided in the following format:

<db<host_name>=(
         DESCRIPTION=
         (ADDRESS= (PROTOCOL=TCP) (HOST=<host address>) (PORT=<port number>) )
         (ENABLE=BROKEN)
         (CONNECT_DATA=
                 (SID=<SID>)
         )
)

Using Oracle sqldeveloper

  1. Run Oracle sqldeveloper
  2. In the "Connections" panel on the left side click "New connections..."
  3. Enter database user's credentials and TNS server information.
  4. Click "Test", check if everything is allright.
  5. "Save" connection.
  6. Finally, "Connect" to the database.
Adding new connection with Oracle sqldeveloper

Using SQL*Plus

Description

SQL*Plus is a command line SQL and PL/SQL language interface. It is advised method of accessing the database whenever you are having some troubles with database connection, when you need to run SQL commands in shell scripts and also when you need to change expired passwords. SQL*Plus is available e.g. on lxplus and virtual machines hosted at cerntsnew.cern.ch.

SQL*Plus availability check

You may check if sqlplus is avaible for you by simply running the following command in your console:

sqlplus -V

If you see something like:

-bash: sqlplus: command not found

then you have no sqlplus installed.

Desired output should be something like:

SQL*Plus: Release 11.2.0.3.0 Production

Establishing connection

In order to establish connection with your database, run the following command:

sqlplus <USER>/<PASSWORD>@<HOSTNAME>:<PORT>/<SERVICE_NAME>

Missing <values> should be obtained from TNS information.

For example:

sqlplus johndoe/secretpassword@databasehost.cern.ch:12345/databaseservice.cern.ch

After successfull connection you should see a welcome message and a command prompt that looks like this:

SQL>  

You can close the connection and SQL*Plus at any time by running the following command:

quit

Executing commands

After establishing a connection you are able to just simply type SQL commands and execute them by pressing <enter> key, i.e.:

SQL> select count(*) from your_table_name;

Executing scripts

You are also able to run SQL scripts from files, by running following statements:

@<FILEPATH>

For example:

@/afs/cern.ch/user/j/johndoe/yoursqlfile.sql

Relative filepath

Filepath may also be relative to the location where you run SQL*Plus.
For example, if you run sqlplus command in the following location:

/afs/cern.ch/user/j/johndoe/

then you can simply run the following command in SQL*Plus:

 @yoursqlfile.sql

SQL*Plus output

When you run large SQL scripts sqlplus output may slow down SQL commands execution. In order to disable the output you should run following command in SQL*Plus before executing your SQL script:

set termout off;

It may also be convenient to log all the output from SQL*Plus. In order to do that you should put following commands at the beginning of your script (your log file will be stored in <FILEPATH>):

set serveroutput on
set echo on
spool on
spool <FILEPATH>

and

spool off

at the end of your script - spool off ends the logging process.

-- MichalMarciniec - 14-Aug-2013

-- KamilMielnik - 27-Aug-2013

Topic attachments
I Attachment History Action Size Date Who Comment
JPEGjpg sqldeveloper.jpg r1 manage 134.6 K 2013-08-14 - 16:08 MichalMarciniec Adding new connection with SQL developer
Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r2 - 2013-08-27 - KamilMielnik
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    TOTEM All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2021 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