This ‘Oracle Database Hosting’ article lists companies who specialize in hosting oracle databases.
You will need to perform your own due diligence research into their services as information could have changed since this article was posted.

Background

There seems to be a great need for Oracle database hosting services,
either for Oracle dedicated hosting or Oracle shared hosting solutions.

It was surprising to find only a limited number of companies offering Oracle hosting solutions,
but the hefty price tag of an Oracle database license is probably the reason for it not being the database of choice for hosting companies.

Below are a list of companies,varying greatly in location, web presence, database version offered and price tag.

Each link will take you to a page which lists the company’s offering in greater detail.

Tip: once you have decided on a company, do a test-recovery before you transfer all your valuable data.

No need to spring this on your hosting company by surprise:
making your hosting company restore your data for you will force them to test their own backup and recovery routines.

Nothing is ever as important in the Oracle database world as the safety of your data in the form of backups and the ability to restore them.

Some of the companies listed below state that a restore is an added service that needs to be paid for separately.

It will be worth the expense,
whether your are a single developer with your own tablespace in a shared database or a high-level corporation outsourcing its database storage to an external service provider.

Oracle Hosting Companies

Update: here’s a link to an OTN (Oracle Technology Network) page which lists APEX (former HtmlDB) solution providers:
(You’ll need to log in with your (free) OTN account )

OTN Apex Community: Hosting companies

The best way to keep up to date with Oracle’s world is to subscribe to Oracle Magazine.

Incredibly, it is still possible to subscribe to this informative magazine and have it delivered anywhere in the world for free.

Good news for us, Oracle professionals, of course, as we get a glossy magazine related to our work and interests delivered to our doorstep for that all-important bedtime reading.

You can subscribe online by clicking on the link below.

Remember that you must be an Oracle professional to qualify, so make sure to select the correct, relevant job description in your application.

Oracle Magazine Online Subscription

Other Free IT Magazine Subscriptions

If you find that the monthly limit of Oracle Magazine subscriptions has been reached,
bookmark this page and come back at the beginning of the next month.

In the meantime you can subscribe for free to many other interesting magazines.

Some of these are limited to USA subscribers only.

• Dr Dobb’s Journal
• Systems Management News
• CRM Magazine
• Better Software Magazine
• Information Week
• eWeek
• Website magazine
• Various other IT Magazines

Interesting Oracle Magazine Articles

	March/April 2008 Issue This issue’s focus is on legacy systems and how Oracle helps companies preserve legacy investments while modernizing their IT infrastructure. Support for SuperHeroes : Fun article about Marvel Entertainment’s (SpiderMan, Iron Man) strategy for growth and integration using Oracle’s E-Business suite on 10G RAC databases. Performing through changes : Arup Nanda shows us how to use 11G’s SQL Performance Analyzer to establish what impact a tuning change could have on a set of SQL statements.
	Jan/Feb 2008 Issue In this issue, 11G is touted as the ideal solution to host and manage the diverse types of data floating around in the Web 2.0 world. On Tuning by Tracing : Oracle’s own super hero Tom Kyte solves a tuning mystery where setting SQL_TRACE=TRUE causes an SQL query to perform better. Succeeding with Failure : Ari Kaplan makes an excellent case for Oracle Data Guard, not only as a disaster recovery solution, but also as a way to ease the backup or reporting load on your primary database.
	Nov/Dec 2007 Issue Editor’s choice awards 2007: Top CIO, CTO, IT Manager, DBA, Application Implementer, Developer and Architect of the year are awarded. Working with PL/SQL : Sue Harper showcases Oracle SQL Developer and how to edit, compile, debug and run PL/SQL stored procedures, step by step. More on Oracle Database 11G : New PL/SQL features in 11G touched upon by Tom Kyte : PL/Scope and DBMS_SQL’s ability to accept and return Ref Cursors.

There are a number of errors that you can face when working with database links in Oracle.

Below are a list of some of the more common ones and suggestions on how to troubleshoot oracle database link errors .

• Oracle10 Tip
• ORA-02021
• ORA-02080
• ORA-02085
• ORA-12154
• ORA-12224
• ORA-12305

Background

What happens during database link usage ?
Some information to help you troubleshoot oracle database link errors.

SQL> select * from dual@remotedb;

D
-
X

SQL> alter table dept@remotedb add column
     (manager varchar2(30));
alter table dept@remotedb add column
     (manager varchar2(30))
                 *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on
a remote database

SQL> alter session close database link remotedb;
ERROR:
ORA-02080: database link is in use

SQL> commit;

Commit complete.

SQL> alter session close database link remotedb;

Session altered.

SQL> connect scott/tiger
Connected

SQL> create database link remotedb connect to scott
     identified by tiger using 'otherremotedb';

Database link created.

SQL> select * from dual@remotedb;
select * from dual@remotedb
                   *
ERROR at line 1:
ORA-02085 : database link REMOTEDB connects to
            OTHERREMOTEDB

SQL> show parameter global_names

NAME             TYPE        VALUE
---------------- ----------- ----------
global_names     boolean     TRUE

SQL> alter system set global_names=false
     scope=both;

System altered.

SQL> connect scott/tiger

Connected.
SQL> select * from dual@remotedb;

D
-
X

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
REMOTEDB

SQL> create database link remotedb
      using 'remotedb';
Database link created.

SQL> select * from dual@remotedb;
ORA-2085 "database link REMOTEDB.WORLD.COM
         connects to REMOTEDB.WORLD.NET"

SQL> drop database link remotedb;
Database link dropped.

SQL> create database link remotedb.world.net
     using 'remotedb';
Database link created.

SQL> select * from dual@remotedb;

D
-
X

SQL> connect / as sysdba
Connected

SQL> select * from global_name;

GLOBAL_NAME
------------
REMOTEDB

SQL>alter database rename global_name
    to remotedb.world.com;
Database altered.

SQL> select * from global_name;

GLOBAL_NAME
-------------------
REMOTEB.WORLD.COM

SQL> drop database link remotedb;
Database link dropped.

SQL> create database link remotedb
      using 'remotedb';
Database link created.

SQL> select * from dual@remotedb;

D
-
X

Once you have created and made use of a database link in Oracle,
you might want to keep an eye on the number of concurrent open database links
in your database so you can tune the open_links initialization parameter.

Read more here on limiting concurrent open links.

You will need to query v$dblink to see how many links are open in your session:

SQL> select in_transaction, count(*) from v$dblink
     group by in_transaction;

IN_   COUNT(*)
--- ----------
YES          1

Here are some interesting columns and descriptions of v$dblink:

db_link           Db link name
owner_id          Owner name
logged_on         Is the database link currently logged on?
protocol          Dblink's communications protocol
open_cursors      Are there any cursors open for the db link ?
in_transaction    Is the db link part of a transaction which
                  has not been commited or rolled back yet ?
update_sent       Was there an update on the db link ?

To gather information on all database links in your database, query dba_db_links.
You will need dba privileges to see this view, the alternatives are user_db_links and all_db_links.

A user who does not have dba privileges can query all_db_links to see
which db links are available to him.

SQL> select * from dba_db_links;

OWNER      DB_LINK     HOST       CREATED
---------- ----------  ---------- ----------
SCOTT      REMOTEDB    remotedb   23-feb-2008 23:59:05

Some interesting columns:

owner           User who owns the db link,
                will state 'PUBLIC' if it is a public database link.
db_link         Db link name.
username        The username that was specified if it was hardcoded
                during the create statement, null if not specified
                during the create statement.
host            The tnsnames alias specified during the create
                statement.
created         Date and time of link creation.

Recommended reading:

Each session in your database has a limited number of database links that can be opened at the same time.

This number is determined by a database-wide initialization parameter: OPEN_LINKS.

The default value is set to 4.

If it is set to 0, distributed transactions are not allowed.

If you are expecting your transactions to have a maximum of 3 database links open concurrently, set this parameter to 3 or higher.

Do not set it too high, it is better for the application to close database links when no longer in use than to change the parameter to a high number.

OPEN_LINKS cannot be modified in real-time, so you will have to change it in the spfile or pfile and bounce the database.

SQL> alter system set open_links=10 scope=both;
alter system set open_links=0 scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter
cannot be modified

SQL> alter system set open_links=10 scope=spfile;

System altered.

If you are not sure how many database links are opened up concurrently by your session’s database application, you can query v$dblink.

SQL> select in_transaction, count(*) from v$dblink
     group by in_transaction;

IN_   COUNT(*)
--- ----------
YES          1

Recommended reading:

There are 2 ways you can hide the complexity involved with Oracle database links from your user:
create a synonym or a view.

This will allow your user to access the data she needs without worrying
about the syntax requirements of database links.

Your user may even be unaware of the fact that she is accessing some data in a remote database.

Note:
Database links are notoriously slow, not an optimal solution for applications needing real-time response.
Expect the database link to cause a delay in accessing the remote data.

Here is an example of creating a synonym for scott’s database link.

SQL> create  synonym rem_emp for emp@remotedb;

Synonym created.

SQL> select count(*) from rem_emp;

  COUNT(*)
----------
        14

The other method is to create a view on your remote database’s data.

SQL> create view v_emp as select * from emp@remotedb;

View created.

SQL> select count(*) from v_emp;

  COUNT(*)
----------
        14

Recommended reading:

Dropping a database link from your Oracle database is as easy as doing the following:

drop database link remotedb;
or
drop public database link remotedb;

You will need no other system privilege other than the ‘create database link’ privilege granted directly
to your username or granted via a role to drop your own database link.

It is not possible to drop a database link belonging to another user.

If you try to specify another schema’s database link by qualifying it with a name,
Oracle will just look for a private database link in your schema with a name
which includes the other schema’s name and will not find it.

To drop a public database link, you will need the system privilege ‘drop public database link’.

For example:

SQL> connect / as sysdba
Connected.

SQL> drop database link scott.remotedb;
drop database link scott.remotedb
                   *
ERROR at line 1:
ORA-02024: database link not found

SQL> connect scott/tiger
Connected.
SQL> drop database link scott.remotedb;
drop database link scott.remotedb
                   *
ERROR at line 1:
ORA-02024: database link not found

SQL> drop database link remotedb;

Database link dropped.

Recommended reading:

The syntax is :

select <column list> from <table>@<dblink name>;

For example:

SQL> select * from dept@remotedb;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

The database link is opened during the select (or other DML transaction) and remains open for the duration of the session.
After you close a session, the links that were active in the session are automatically closed.

Close a db link

To explicitly close the database link , use the command below:

SQL> alter session close database link remotedb;

Session altered.

Using db links in other DML statements

You can use insert/update/delete statements just as easily with database links

SQL> select * from dept@remotedb;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> insert into dept@remotedb (deptno,dname,loc)
  2  values (50,'MARKETING','BOISE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept@remotedb;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      BOISE

SQL> update dept@remotedb set loc = 'LONDON'
     where deptno = 50;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept@remotedb;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      LONDON

SQL> delete from dept@remotedb
     where dname = 'MARKETING';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from dept@remotedb;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Note that DDL operations are not allowed through a database link:

SQL> alter table dept@remotedb
     add column (manager varchar2(30));

alter table dept@remotedb add column
(manager varchar2(30))
                 *
ERROR at line 1:
ORA-02021: DDL operations are not allowed
           on a remote database

Recommended reading:

CREATE {PUBLIC} DATABASE LINK
{CONNECT TO
IDENTIFIED BY }
USING ‘ ‘;

Privileges needed

When creating an oracle database link, you can decide to create it privately,
for use only by the owner of the database link,
or publicly, for use by all other database users who have the right privileges.

If you do not specify the word ‘PUBLIC’ in the create statement, the database link will be created as a private one by default.
Once a public database link has been created, it will be owned by a special user named ‘PUBLIC’.

The second decision is to create it with a hard-coded username and password, or without access credentials.
If you create a public database link without access credentials, any user accessing it will do so with his own username and password.

Take care when creating a public dblink with a hardcoded username and password, it is a security risk.

Creation

Now that you are ready to create it, you must provide a database link name
(this is what your db link will be called).

One common problem with dblinks is that Oracle will not allow you to create a db link whose name is different from the global name
of the remote database if the local database’s ‘GLOBAL_NAMES’ initialization parameter is set to ‘TRUE’.

Lastly, you must specify a valid tnsnames alias which Oracle will use to connect to the remote database.
To test if your tnsnames.ora alias is valid, try to access it through sql*plus on your local server.

To be able to create a database link, you will need to have the system privilege
‘CREATE DATABASE LINK’
or
‘CREATE PUBLIC DATABASE LINK’
granted to you.

The remote user will need to have at least ‘CREATE SESSION’ privileges.

Examples:
Let’s create a private database link using a hard-coded username and password.

SQL>Create database link remotedb connect to scott identified by tiger using ‘remotedb.world.com’;
Database link created.

The same db link without a username/password:

SQL>create database link remotedb using ‘remotedb.world.com’;
Database link created.

Here’s an example to create a public database link with a username and password:

SQL> create public database link remotedb connect to scott identified by tiger using ‘remotedb.world.com’;
Database link created.

Here’s a public database link without a username and password.

SQL>create public database link remotedb using ‘remotedb.world.com’;
Database link created.

Recommended reading:

The connection can be made to a database hosted on the same machine or remotely on another server.

When you query data on a different database, you are essentially using a distributed query.
It is also possible to execute distributed transactions through database links like insert, update and delete.

The authentication used in a database link is either the link’s owner or a hard-coded username and password, depending on the way the link was created.

A database link in oracle can be either private (for use only by the link’s owner) or public (accessible to any user with the correct privileges).

Connectivity is established via Sql*Net, using an alias defined in the local database server’s tnsnames.ora

Have a look at the tutorials on this site  to find out more about oracle database links :

Dblinks category

Standby databases are no longer very complex to set up.
By using Data Guard Broker, all the hard work is done for you in the background,
and all you have to do is click a few buttons.

If you would rather get your hands dirty, this is the place for you.

The tutorials below will take you through all the steps needed to set up and maintain an Oracle standby database.

The examples are all related to a physical standby database, not a logical one.

Standby databases are really Oracle’s gift to the DBA community.

Gone are the days where hardware failure meant days of restoring backup files from tapes,
which were probably far away in an offsite vault,
while hoping that the tapes were not corrupt and that no bug had crept into your backup routine.

With a standby database in place, you can be up and running in no time,
minimizing the impact on your users and their businesses.

Quick reference to monitor, start and stop a physical standby database

Starting a physical standby database:

Starting the managed recovery:

Stop the managed recovery:

Shutdown the physical standby database:

Monitor primary’s archive destination status:

Check if archived logs are synching OK, compare both results:

• on primary db

• on standby db:

Check Dataguard processes status:

• on standby db:

Recommended reading:

For example, if you want to kill all processes associated with one particular SID, but have multiple SIDs running on your server,
don’t grep for ‘oracle’ as the list that is returned is for ALL oracle processes on the server.

In this example, I need to kill all processed associated with the Oracle management agent (emagent) on a server.

I do not need to worry about being careful as I only have one agent home on the server and can not accidentally kill irrelevant ones.

$ps -ef | grep emagent

oracle     395 14244  0 15:10 pts/1    00:00:00
 grep emagent
oracle    3905     1  0 13:45 pts/1    00:00:00
/home/oracle/product/agent10g/perl/bin/perl
/home/oracle/product/agent10g/bin/emwd.pl
agent /home/oracle/product/agent10g/sysman/log/emagent.nohup
oracle    3922  3905  0 13:45 pts/1    00:00:08
/home/oracle/product/agent10g/bin/emagent
oracle    3928  3922  0 13:45 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    3929  3928  0 13:45 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    3930  3928  0 13:45 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    3931  3928  0 13:45 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    3932  3928  0 13:45 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4019  3928  0 13:45 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4233  3928  0 13:46 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4235  3928  0 13:46 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4236  3928  0 13:46 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4237  3928  0 13:46 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4238  3928  0 13:46 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4239  3928  0 13:46 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4240  3928  0 13:46 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle    4241  3928  0 13:46 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent
oracle   14736  3928  0 14:41 pts/1    00:00:00
/home/oracle/product/agent10g/bin/emagent

$ps -ef | grep emagent| awk '{print $2}' | xargs kill -9
$
$ps -ef | grep emagent
oracle   27931 14244  0 15:26 pts/1    00:00:00 grep emagent

Oracle creates implicit cursors for all SQL DML (data manipluation language) statements, even for select statements returning one row.

If your query returns more than one row, you can explicitly declare a cursor in PL/SQL to process the rows one by one.

Here is a simple example of using a cursor in a ‘cursor for loop’ (which automatically opens, fetches from and closes the cursor for you) :

Your client application may hit the following error :

This means that your application’s session has hit the database-wide limit of the maximum number of open cursors which one session is allowed to have.

The database parameter which governs this limit is : open_cursors

You can change this parameter in real-time, but you should ask yourself the question if the value is really too low, perhaps the application code should be rewritten, to either do better housekeeping (close cursors when they are done), or rewrite the code so that many DML statements can be recoded to be used in one cursor.

Never set this to an excessive value, you want to keep open cursors in check.
Rather let a new, untuned application hit this error so that the code can be tuned.

Setting this value higher does not cause performance overhead on the database.

If you do decide to change the value , this is how to do it :
(the default value of 50 is usually too small, so you will probably change it sooner or later).

How to find out which cursors are open for a session ?

Get the session’s sid from v$session, then get the sql_text from v$open_cursor :

Recommended reading for more information on oracle cursors :

Oracle online documentation: Oracle® Database Application Developer’s Guide - Fundamentals

Use top on the unix command line to see which process is the main culprit:
(use shift+M to sort the list in order of highest memory consumption)

Make sure the top process is a client connection shadow process:

Inside the database, find out who this is :
(formatted result)

Kill the user:

The user will receive this message either immediately or at the next attempted SQL statement:

Recommended reading:

Warning, it is unsupported by Oracle, so don’t base your mission-critical alerts on this method.

Let’s say you are creating a stored proc as user scott, and instead of writing your
trapped error message to a table, an error file or sending an email, you would like it to be reflected
in the database’s alert.log.

The steps below show you how.

If not documented, this is guaranteed to make you unpopular with DBAs who manage your database in your absence…
First, grant execution privileges to scott:

as sys:

Now run and test a procedure which will write to the alert.log

This is how it reflects in the alert.log (ORA-0000 because there is no error.)

Lastly : implement it in your procedure’s exception handler section:

The solution is to use the dbms_utility package as a DBA user.

Example, if you want to recompile all procedures,functions, packages and triggers belonging to scott
here is how to do it:

If you only want to recompile all invalid options, execute it like this :

If you want to use each object’s session settings instead of the calling user’s settings:

To see if any errors were generated
(you must run this command immediately after executing dbms_utility) :

Another way to see if the compilations were successful :

Recommended reading:

You can use it to share disk space amongst servers, so that each server ’sees’ the shared filesystem as if it is local.

If your network is fast enough, you can backup your oracle database to a shared filesystem, or just keep
any required software on the shared filesystem so that you do not need to install the software
numerous times on different servers.

In this example, we are sharing a filesystem called orabackup on server share01.world.com, and then
we will mount that on a database server called prod01.world.com.

Steps to take on share01:

Log in as root.

Add prod01’s IP to /etc/exports
If there is more than one entry, make sure there is a space between IP addresses.

Send a hangup to the mountd process to force it to reread the /etc/exports file.

Steps to take on prod01:

Log in as root.

Create /orabackup

Edit /etc/fstab, add line to the bottom:

Mount the filesystem :

Once that is completed, you will be able to access it like a local filesystem.

I am using sql*plus bind variables because I needed to connect to 2 different databases
and compare their results.
The standby database is in mounted mode, so this precludes the idea of trying to get this information
across a database link.

The sql script is then called by a shell script. (check_stby.sh) which gets called by
a host-user-defined-metric in Enterprise Manager’s Grid Control system.

If the returned value exceeds the metric’s threshold, an alert is triggered and I receive an
email warning me to check the status of the standby database.

check_stby.sql

set echo off
set feedback off
set termout off
VARIABLE prim_seq NUMBER
variable stby_seq number
variable diff number

connect sys/xxxx@primarydb.world.com as sysdba
begin
select max(sequence#)into :prim_seq from v$archived_log;
end;
/

connect sys/xxxx@standbydb.world.com as sysdba
begin
select max(sequence#) into :stby_seq from v$log_history;
end;
/

begin
:diff := :prim_seq - :stby_seq   ;
end;
/
exit :diff;

check_stby.sh

#!/bin/ksh

export ORACLE_HOME=/home/oracle/product/10.2.0

$ORACLE_HOME/bin/sqlplus -s /nolog @check_stby.sql

echo "em_result="$?"\n"

Recommended reading:

Save it to a file on your server or PC and run it to complete dropping the Oracle demo tables.

Demo_drop.sql

====== start of section to cut ======

--
-- Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.
--
--
-- DESCRIPTION
--   This script drops the SQL*Plus demonstration tables created by
--   demobld.sql.  It should be STARTed by each owner of the tables.
--
-- USAGE
--   From within SQL*Plus, enter:
--       START demodrop.sql

SET TERMOUT ON
PROMPT Dropping demonstration tables.  Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

SET TERMOUT ON
PROMPT Demonstration table drop is complete.

EXIT

====== end of section to cut ======

Drop scott user

If you want to drop the scott user, do the following (login as DBA ):

Save it to a file on your server or PC and follow the instructions in the
oracle sqlplus demo table article
to complete building the Oracle demo tables.

Demobld.sql

====== start of section to cut ======

--
-- Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.
--
-- NAME
--   demobld.sql
--
-- DESCRIPTION
--   This script creates the SQL*Plus demonstration tables in the
--   current schema.  It should be STARTed by each user wishing to
--   access the tables.  To remove the tables use the demodrop.sql
--   script.
--
--  USAGE
--    From within SQL*Plus, enter:
--        START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables.  Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
        (DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

EXIT

====== end of section to cut ======

Recommended reading:

Scott is the standard example schema used to run queries and scripts to show how Oracle works.

Scott schema creation.

Create the scott user on your database if it does not already exist.

Connect to the database as a dba user, like sys or system.

Demobuild execution.

The script to create the tables (demobld.sql) is located in
%oracle_home%\sqlplus\demo\demobld.sql
or
$ORACLE_HOME/sqlplus/demo/demobld.sql,
for either windows or unix.

Depending on which version of Oracle you use, it will create the following tables:

• EMP
• DEPT
• BONUS
• SALGRADE
• DUMMY

How to run it:

To drop the demo tables, run the demodrop.sql script in the same directory.

There are 5 kinds of constraints :

• Check
• Not-Null
• Unique
• Primary Key
• Foreign Key

Check Constraints

A check constraint allows you to define a function which determines whether the data is allowable or not.

Example: Let’s add a check constraint to employees.surname (table Employees, column Surname)
which will only allow characters to be entered in upper case:

If you try to add a surname in lower case , you will receive the following error:

Now let’s add it the proper way:

Not-Null Constraints

This simple constraint determines whether your column is allowed to contain null data.
By default, a column can contain null data, unless you add a not-null constraint.

Example : Let’s add a not-null constraint to Employees.Surname

Now try to insert a null value.

Unique Constraints

These constraints enforce uniqueness in your column’s data : it will stop duplicated data from being inserted.

Example:

We know there is already an entry named ‘JONES’ in the table, so let’s see what happens when we try to insert it again:

Primary Key Constraints

A primary key constraint is a combination of a unique and a not-null constraint.
It allows you to use a column in your table to uniquely identify each row by your predetermined key.
Creating a primary key automatically creates a unique index of the same name on the table.
This is how Oracle enforces the uniqueness.

Example: Before we can create a primary key, let’s drop the previous not-null and unique key constraints.

Either inserting a pre-existing surname or attempting to insert a null value into the surname column
will now result in an error.

Foreign Key Constraints

Another name for these is relational integrity constraints.
They enforce logical relationships between parent and child tables.

Let’s use a geographical example.

We have a parent table called Countries and a child table called Cities.
A row in the Cities table must be able to find its parent Country in the Countries table
before you are allowed to insert the City.

Parent table Countries :

Child table Cities:

To implement this is slightly more complicated than the other constraints:
First you create the primary key on the parent table:

Now we create the foreign key constraint on the child table which references the primary key on the parent table:

Time to test: let’s try to add a row in Cities with a non-existent Countryid.

Recommended reading:

Below is a list of some commonly used sql functions, with an example of each:

• add_months
• avg
• ceil
• chr
• concat
• count
• decode
• dump
• floor
• greatest
• initcap
• instr
• last_day
• least
• length
• lower
• lpad
• ltrim
• max
• min
• mod
• months_between
• next_day
• nullif
• nvl
• nvl2
• replace
• round
• rpad
• rtrim
• sqrt
• substr
• sum
• sysdate
• to_char
• to_date
• to_number
• translate
• trim
• trunc
• upper

add_months

Returns the supplied date with the requested number of months added to it.
If the supplied date has a day value which is larger than the maximum number of days
in the resulting month, the result is the last day of that month.

avg

Returns the average of a series of numbers.

ceil

Returns the smallest integer larger than the supplied argument.
Opposite of sql function: floor.

chr

Returns the character equivalent of the supplied integer.

concat

Returns the first argument concatenated with the second argument.
The result’s dataype will depend on the datatype of the arguments.
I prefer to use the two pipe symbols (||) to concatenate.

count

Returns the number of rows as requested by the query, either all rows or only distinct values.
By specifying *, it will return all rows including duplicates and nulls.
If no rows match your query, count will return 0. (never null)

decode

The oracle decode function takes up to 255 argument sets and returns the relevant result depending on the value found.
This is similar to the ‘case’ statement in other languages.
The last argument is what will be returned if no match is found in the decode statement.

dump

Returns a string representing the argument’s datatype code, byte length and internal representation.
Very handy command if you are hunting for unexpected or hidden characters in your data.

floor

Returns an integer equal to or 1 less than the argument.
Opposite of oracle function : ceil.

greatest

Returns greatest ranking argument out of a list of arguments.
The ranking is determined by the value of the numerical codes of the whole character string.
Opposite of oracle function : least.

initcap

Returns the string with each word’s first letter capitalized.
Similar to oracle functions : lower and upper

instr

Returns the first position of a string inside another string.
The 1st argument is the string to be searched.
The 2nd argument specifies the character or string to search for.
The 3rd argument allows you to specify from which character position to start searching.
The 4th argument allows you to specify which occurence of the search string to return.

last_day

Returns the last day’s date of the supplied date’s month.

least

Returns the least or smallest ranking argument out of a list of arguments.
The ranking is determined by the value of the numerical codes of the whole character string.
Opposite of oracle function : greatest

length

Returns the length (as an integer) of the supplied argument.

lower

Returns the argument in lower case.
Similar to oracle functions : upper and initcap

lpad

Returns the first argument, left-padded by the third argument, so that the complete string is as long as specified by the second argument.
If you do not supply the third argument, blank spaces are used for padding.
Similar to oracle function : rpad

ltrim

Removes the second argument from the first argument.
If you do not supply the second argument, it will remove blank spaces.
Similar to oracle function: rtrim

max

Returns the largest of a series of numbers.
Similar to oracle function : min

min

Returns the smallest in a series of numbers.
Similar to oracle function : max

mod

Returns the remainder of the first argument divided by the second argument.
The result is floor-ed.

months_between

This date-oriented oracle function returns the number of months between two dates as a fraction.

next_day

Returns date of the day of the week (supplied as the second argument) after the date of the first argument.
The second argument is specified as the name of a weekday.

nullif

Returns null if the first and second argument are equal, if they are unequal, it will return the first argument.

nvl

Returns the second argument if the first argument is null.
Otherwise, the first argument is returned.
Similar to oracle function : nvl2

nvl2

This function is an extension to nvl by allowing you to specify a value to return if the argument to be checked is null.
So, nvl2 will return the third argument if the first argument is null and will return the second argument if the first argument is not null.
Similar to oracle function : nvl

replace

Returns the first argument with every occurence of the second argument replaced with the third argument.
If the third argument is left out, the second argument is removed from the first argument.
If the second argument is null, then the first argument is returned unmodified.

round (number)

Returns the first argument rounded to the number of places after the decimal point as specified by the second argument.
If the second argument is not specified, then the first argument is rounded to 0 places after the decimal point.
If the result is n.5, round will round up to the next integer, if the result is -n.5, it will round down to the next integer.

round (date)

This will always return a date.
The format of the second argument determines the timeperiod it will round to, like YEAR, MONTH, DAY, HH and MI.
If you do not specify a second argument, the format defaults to day.

rpad

Returns the first argument, right-padded by the third argument, so that the complete string is as long as specified by the second argument.
If you do not supply the third argument, blank spaces are used for padding.
Similar to oracle function : lpad

rtrim

Removes the second argument from the first argument.
If you do not supply the second argument, it will remove blank spaces.
Similar to oracle function : ltrim

sqrt

Returns the square root of the argument.

substr

This very useful sql function returns the part of the first argument, determined by what you specify as the second and third arguments.
The second argument tells Oracle from which character position to start counting.
It can contain a positive or negative value.
The third argument determines the number of characters to select.
If you do not supply this argument, all characters until the end are selected.

Read up more about this very useful oracle function :
Oracle online documentation : SQL Reference, substr

sum

Returns the sum of a range of numbers.

sysdate

Returns the current date and time of the local database server’s operating system.
You will see the date format as it is specified by the NLS_DATE_FORMAT
database initialization parameter.
You can set NLS_DATE_FORMAT for your session to overwrite the database’s default value.
Alternatively, you can convert sysdate to a character datatype using the to_char function.

to_char

Converts the argument to a character datatype, using the format specified by the second argument.
You can convert number, date and CLOB datatypes.

Read up more about format models here:
Oracle online documentation : SQL Reference, format models

to_date

Converts a character string to a date datatype.
If the character string is already in the default format, you do not need to specify a format as the second argument.
The default format is specified by initialization parameter NLS_TERRITORY or can be set by NLS_DATE_FORMAT.

Read up more about format models here:
Oracle online documentation : SQL Reference, oracle function format models

to_number

Converts a character datatype to a number datatype
The second argument supplies the format needed to do the conversion.

Read up more about format models here:
Oracle online documentation : SQL Reference, format models

translate

Returns a string which replaces all occurences of the characters in the second argument
with the characters of the third argument.
If the second argument contains more characters than the third argument,
the extra characters are removed from the result.

trim

Returns the third argument with the leading, ending or both sides of the argument’s characters trimmed.
The first argument specifies which side to trim from.
Values can be ‘LEADING’,'TRAILING’ or ‘BOTH’.
If null, both sides are trimmed.
The second argument specifies which character to trim. If null, blanks are assumed.
The third argument takes the form : FROM < source > which can be a column name.

trunc(number)

Returns the first argument truncated to the number of decimal places determined by the second argument.
If the second argument is not specified, Oracle assumes 0.

trunc(date)

Returns the date truncated to the day specified by the second argument’s format.
The format of the second argument determines the timeperiod it will truncate to, like YEAR, MONTH, DAY, HH and MI.
If you do not specify a second argument, the format defaults to DAY.

upper

Returns the argument with all characters in upper case.
Similar to oracle functions : lower and initcap

Recommended reading for more information on oracle functions :

Oracle online documentation: SQL Reference

• Definition
• Simple subquery
• Correlated subquery
• Update with correlated subquery
• Delete with correlated subquery

Definition

A subquery is basically a select clause which is used instead of another statement.

For example : Have a look at the following 2 tables : DEPT and EMP

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select empno,ename,job,sal,deptno from emp;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800         20
      7499 ALLEN      SALESMAN        1600         30
      7521 WARD       SALESMAN        1250         30
      7566 JONES      MANAGER         2975         20
      7654 MARTIN     SALESMAN        1250         30
      7698 BLAKE      MANAGER         2850         30
      7782 CLARK      MANAGER         2450         10
      7788 SCOTT      ANALYST         3000         20
      7839 KING       PRESIDENT       5000         10
      7844 TURNER     SALESMAN        1500         30
      7876 ADAMS      CLERK           1100         20
      7900 JAMES      CLERK            950         30
      7902 FORD       ANALYST         3000         20
      7934 MILLER     CLERK           1300         10

14 rows selected.

Simple Oracle subquery

A simple subquery is evaluated once for each table.

You would like to select all employees whose department is located in Chicago.

A join would be a better solution for this select,
but for the purposes of illustration we will use a subquery.

SQL> select empno,ename,job,sal,deptno from emp where
     deptno in (select deptno from dept
	 where loc = 'CHICAGO');

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7900 JAMES      CLERK            950         30
      7844 TURNER     SALESMAN        1500         30
      7698 BLAKE      MANAGER         2850         30
      7654 MARTIN     SALESMAN        1250         30
      7521 WARD       SALESMAN        1250         30
      7499 ALLEN      SALESMAN        1600         30

6 rows selected.

You can use a subquery in the place of a table name in the ‘from’ clause.

SQL> Select ename, deptno from
     (select ename, deptno from emp
	 where job = 'CLERK' )
     where
     deptno > 20;

ENAME          DEPTNO
---------- ----------
JAMES              30

You can even replace a column name with a subquery:

SQL> select ename,
            (select max(sal) from emp) "maxsal" ,
            sal,
            ((select max(sal) from emp ) - sal )
			"difference"  from emp;

ENAME          maxsal        SAL difference
---------- ---------- ---------- ----------
SMITH            5000        800       4200
ALLEN            5000       1600       3400
WARD             5000       1250       3750
JONES            5000       2975       2025
MARTIN           5000       1250       3750
BLAKE            5000       2850       2150
CLARK            5000       2450       2550
SCOTT            5000       3000       2000
KING             5000       5000          0
TURNER           5000       1500       3500
ADAMS            5000       1100       3900
JAMES            5000        950       4050
FORD             5000       3000       2000
MILLER           5000       1300       3700

14 rows selected.

Correlated Oracle subquery

A correlated Oracle subquery is evaluated once FOR EACH ROW as opposed to a normal subquery which is evaluated only once for each table.

You can reference the outer query inside the correlated subquery using an alias which makes it so handy to use.

Let’s select all employees whose salary is less than the average of all the employees’ salaries in the same department.

SQL> select ename ,sal ,deptno from emp a where
     a.sal < (select avg(sal) from emp b
	           where a.deptno = b.deptno)
     order by deptno;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK            2450         10
MILLER           1300         10
SMITH             800         20
ADAMS            1100         20
WARD             1250         30
MARTIN           1250         30
TURNER           1500         30
JAMES             950         30

8 rows selected.

Using a correlated subquery in an update

Let’s give these people (whose salary is less than their department’s average) a raise.

SQL> select ename, sal, deptno  from emp
     order by deptno, ename;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK            2450         10
KING             5000         10
MILLER           1300         10
ADAMS            1100         20
FORD             3000         20
JONES            2975         20
SCOTT            3000         20
SMITH             800         20
ALLEN            1600         30
BLAKE            2850         30
JAMES             950         30
MARTIN           1250         30
TURNER           1500         30
WARD             1250         30

14 rows selected.

SQL>  UPDATE emp a
      set sal = (select avg(sal)
                from emp b
	        where
	        a.deptno = b.deptno)
      where sal <
	   (select avg(sal) from emp c
	   where a.deptno = c.deptno);

8 rows updated.

SQL> select ename, sal, deptno  from emp
     order by deptno, ename;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK         2916.67         10
KING             5000         10
MILLER        2916.67         10
ADAMS            2175         20
FORD             3000         20
JONES            2975         20
SCOTT            3000         20
SMITH            2175         20
ALLEN            1600         30
BLAKE            2850         30
JAMES         1566.67         30
MARTIN        1566.67         30
TURNER        1566.67         30
WARD          1566.67         30

14 rows selected.

SQL> commit;

Commit complete.

Using a correlated subquery in a delete

Let’s delete the highest earning employees in each department.

SQL>  delete from emp a where
      a.sal = (select max(sal) from emp b
	  where a.deptno = b.deptno);

4 rows deleted.

SQL> select ename, sal, deptno  from emp
     order by deptno, ename;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK         2916.67         10
MILLER        2916.67         10
ADAMS            2175         20
JONES            2975         20
SMITH            2175         20
ALLEN            1600         30
JAMES         1566.67         30
MARTIN        1566.67         30
TURNER        1566.67         30
WARD          1566.67         30

10 rows selected.

SQL> commit;

Commit complete.

Lastly: do not forget to commit to make your changes permanent when using the oracle subquery statement.

Recommended reading:

The Oracle Delete statement allows you to remove rows from a table.

The syntax is : delete from
(where );

If you specify the delete statement without the where clause, you will delete all rows from a table.

Specifying the where clause will restrict the deletion to the rows which match the restriction clause.

Here is the sample table:

SQL> select * from dept order by deptno;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
70 CALLCENTRE
80 SUPPORT NONE
90 BOISE
91 SHIPPING DETROIT
92 FINANCE DETROIT

11 rows selected.

Let’s delete all rows that are located in Detroit.

SQL> delete from dept where loc = ‘DETROIT’;

2 rows deleted.

SQL> select * from dept order by deptno;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
70 CALLCENTRE
80 SUPPORT NONE
90 BOISE

9 rows selected.

Now let’s select all rows that have a null value in either the dname column or the deptno column.

SQL> delete from dept where dname is null or loc is null;

2 rows deleted.

SQL> select * from dept order by deptno;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
80 SUPPORT NONE

7 rows selected.

Now let’s delete all the remaining rows from this table:

SQL> delete from dept;

7 rows deleted.

SQL> select * from dept order by deptno;

no rows selected

Lastly: do not forget to commit to make your changes permanent after you use the Delete statement.

Recommended reading:

Oracle Online Documentation: SQL Reference, Delete statement

Syntax

update
set = where ;

If you do not specify a where clause, all rows in the table will be updated.

Permissions
You must have update privileges to update another schema’s table.

If you want to update through a view, the view’s owner needs to have
update permissions on the base table and you need update privileges on the view.

The ‘update any table’ privilege sidesteps the 2 requirements above by combining them in one privilege.

Examples

Let’s use table SCOTT.DEPT as our example table.

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

DEPT contains 3 columns (deptno, dname and loc) and 4 rows.

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

I want to update the location for department ‘SALES’ to ‘LONDON’.

SQL> update dept set loc = ‘LONDON’
where dname = ‘SALES’;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES LONDON
40 OPERATIONS BOSTON

Let’s update 2 columns at the same time:

SQL> update dept set loc = ‘MIAMI’, deptno = 50
where dname = ‘SALES’;

1 row updated.

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
50 SALES MIAMI
40 OPERATIONS BOSTON

SQL> commit;

Commit complete.

Let’s use a function to change the value of the location column:

SQL> update dept set loc = lower(loc) ;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING new york
20 RESEARCH dallas
50 SALES miami
40 OPERATIONS boston

Lastly: do not forget to commit to make your changes permanent.

Recommended reading:

# Oracle Online Documentation: SQL Reference, Update statement

The Oracle Insert statement allows you to add data to a table.

Contents :

• Inserting without specifying column names
• Inserting by specifying column names
• Insert a column’s default value
• Inserting a null value
• Inserting multiple rows by means of a subquery

The syntax is : insert into <table_name> ( <column list>) values (<values list>);

You do not have to specify the column names if your value list is in the same order as the columns of the table, but it is best to always specify the column names.

Sample table for use in the Oracle Insert syntax tutorial:

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

No column list

When you do not specify the column list, you can use the values clause immediately after
the table clause, but Oracle will expect the values to match the column datatypes,
so you must make sure that the values are in the same order as the table’s columns.

To see what the order of a table’s columns is,
use the ‘DESCRIBE’ statement or its abbreviation ‘desc’ :

SQL> describe dept
 Name                   Null?    Type
 ---------------------- -------- ---------------

 DEPTNO                          NUMBER(2)
 DNAME                           VARCHAR2(14)
 LOC                             VARCHAR2(13)

SQL> insert into dept values
     (50,'MARKETING','MELBOURNE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      MELBOURNE

Using a column list

SQL> insert into dept (deptno,dname,loc)
     values (60,'IT','HARTFORD');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      MELBOURNE
        60 IT             HARTFORD

6 rows selected.

Using a column list allows you to insert values for selected columns only:

SQL> insert into dept (deptno,dname)
     values (70,'CALLCENTER');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      MELBOURNE
        60 IT             HARTORD
        70 CALLCENTER

7 rows selected.

Default values
You can choose to insert a column’s default value like this:
Let’s first change the DEPT table to have a default value of ‘NONE’ for the location column:

SQL> alter table dept modify (loc default 'NONE');

Table altered.

SQL> insert into dept (deptno,dname,loc)
     values (80,'SUPPORT',DEFAULT);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      MELBOURNE
        60 IT             HARTORD
        70 CALLCENTER
        80 SUPPORT        NONE

8 rows selected.

Inserting a null value

Use the ‘null’ identifier to insert a null value instead of a real value.

SQL> insert into dept (deptno,dname,loc)
     values (90,NULL,'BOISE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      MELBOURNE
        60 IT             HARTORD
        70 CALLCENTER
        80 SUPPORT        NONE
        90                BOISE

9 rows selected.

Inserting multiple rows by means of a subquery

Instead of adding one row at a time, here is an example of inserting a number of rows by using a subquery:

We have another table called DEPT_MERGE with the same table structure as DEPT and with rows which need to be added to DEPT.

SQL> select * from dept_merge order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        91 SHIPPING       DETROIT
        92 FINANCE        DETROIT

SQL> insert into dept
  2  (select * from dept_merge);

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      MELBOURNE
        60 IT             HARTORD
        70 CALLCENTER
        80 SUPPORT        NONE
        90                BOISE
        91 SHIPPING       DETROIT
        92 FINANCE        DETROIT

11 rows selected.

Lastly: do not forget to commit to make your changes permanent after you use the Oracle Insert statement.

Recommended reading:

How to query the Oracle database

Always test these ’select’ commands on a test server
before implementing them on a production server.

This tutorial will show you how to use the correct syntax to query data in the Oracle database.

When you query an object, you could be querying a table, a synonym or a view,
but this will make no difference to the syntax you need to use or to the result you will obtain.

A table consists of a number of columns and rows and your query will be built so that you decide which rows and which columns to return.

The simplified syntax for the queries we’ll be using in this tutorial is as follows:

select <column list> from <table> where <row criteria> ;

For example: (log in as scott/tiger)

Table DEPT looks like this :

There are 3 columns : DEPTNO, DNAME and LOC
There are 4 rows.

Column criteria

Let’s query all columns and all rows:

SQL> select deptno, dname, loc from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Time saving tip: use * instead of the whole column list if you plan to return each column
in the table:

Let’s only query the Location column in the table.

SQL> select loc from dept;

LOC
-------------
NEW YORK
DALLAS
CHICAGO
BOSTON

Now let’s query both the DNAME and LOC columns from this table:

SQL> select dname, loc from dept;

DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

Row criteria

To specify only a subset of rows to be returned,
you will use the WHERE clause.

Let’s query only the rows in the dept table belonging to the SALES department.
We will query all columns.

SQL> select * from dept where dname = 'SALES';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

We could also decide to make the DEPTNO the criteria to deliver the same result.

SQL> select * from dept where deptno = 30;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

What if we want to query all rows related to departments that have department numbers larger than 20 ?

SQL> select * from dept where deptno > 20;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

What if you want to make your where clause more complex ?

Let’s look for any rows that have ‘SALES’ as a department name but also any rows that have ‘BOSTON’ as the location.

This means that
any row contains the value ‘SALES’ in the DNAME column,
as well as
any row containing the value ‘BOSTON’ in the LOC column
will be included in the result set.

You can do so by adding another section to the where clause and using the ‘OR’ term to glue them together.

SQL> select * from dept
     where dname = 'SALES' or loc = 'BOSTON';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

If you want your result set to return only rows
where the location is ‘BOSTON’
and
the department name is ‘SALES’,
you will see 0 rows returned because no row has both those values for the relevant columns :

SQL> select * from dept
     where dname = 'SALES' and loc = 'BOSTON';

no rows

Note on datatypes
Your where clause will have to be aware of which type of data you are using as query criteria.
In other words, you must be sure to use the correct datatype in your where clause.

For example, DNAME is a varchar column, so you must put single quotes around your query criteria.
(Only number datatypes do not need single quotes)

If you try to specify SALES in your where clause like this :

where dname = SALES;

you will receive an error:

ORA-00904: “SALES”: invalid identifier

because you are expected to supply single quotes around a varchar datatype like this:

where dname = ‘SALES’;

How do you know the datatype of a column in a table ?

Use DESCRIBE to see which columns are in a table and what their datatypes are:

SQL> desc dept;
 Name                Null?    Type
 ------------------- -------- -----------------

 DEPTNO                       NUMBER(2)
 DNAME                        VARCHAR2(14)
 LOC                          VARCHAR2(13)

Varchar tip
Oracle is case sensitive to data inside the tables,
unlike column and table names which are usually upper case unless you explicitly create them otherwise.

So, there is a difference between ‘SALES’, ’sales’ and even ‘Sales’.

If you are unsure of the case that is used in your data, you can force the result to upper (or lower)
case by calling the upper or lower function to convert the column data, like this:

… where lower(dname) = ’sales’;.

Recommended reading:

Spend some time reading Oracle’s documentation to get comfortable with Oracle’s syntax.

It will be worth the effort.

It is the language you will use to access any data structure or its data in the Oracle database.

You will use this to issue commands to send instructions to the database.

It is a standard across all compliant databases.

SQL commands work on sets of data and allow you to :

• Query the data (select)
• Change the data (insert, update, delete)
• Work with data structures (create or replace, alter, drop)
• Determine access rules to data and structures (grant)
• Lock rows or tables to enforce consistency. (lock)

Tables and indexes

A table is a structure which contains horizontal rows and vertical columns.

The intersection of a row and a column is a cell.

When we search for data in a table, we use the SQL ’select’ statement to extract the data in the table cells by specifying which ‘intersection’ of column and row we want to extract results for. (More details later..)

Here’s a real-world example of a table:

Table: Employees, contains 3 columns : EmployeeID, Surname and Firstname and 4 rows of data.
Each table has a hidden column called rowid which uniquely identifies each row in the table.

Index: a technique implemented by the database engine to conduct data access as fast as possible.

An index on the ‘Surname’ column of the Employees table would create another ‘table’ (but now called an index) containing only the sorted Surname data and the rowid of that row.

When you query data in the Surname column of the Employees table,
the database engine’s optimizer determines whether or not to use the index,
based on the expected number of rows it has to return.
If it detects and uses the relevant index, it does a search on the index rather than the table.

Once if finds the correct row, it uses the stored rowid to access the Employees table directly (no searching) to retrieve the whole row.

Once an index is created, you do not need to maintain it, nor do you need to use its name to access
the data in the table. The Oracle engine does that for you in the background.

Normalization:

A methodology to design a database system in such a way so that there is no or limited data duplication.
No table should contain data already stored in another table unless it is a link to a row
in the other table (foreign key relationships).
There are many other rules and degrees of normalization , if you would
like to read more about it , see the link below in ‘Additional resources’.

Recommended reading:

For more info on relational database theory, here’s an excellent well-priced resource:

To find large directories, use the du command and sort the output.

For example, to output the 10 largest directories in /var, sorted in ascending size order, use the following command:

du -ko /var|sort -n | tail -10

To avoid crossing file system boundaries, that is, to see the directory usage in / but not in the other mounted files systems (/var, /opt, and so on), add the d option to the du command:

du -kod /var|sort -n | tail -10

Finding Large Files

To find large files, use the find command and sort the output.

Example 1: To find all plain files (not block, character, symbolic links, and so on) in a file system larger than 200,000 512-byte blocks (approximately 100 Mbytes) and sort on field 7 (file size) while numerically ignoring leading blanks, do this:

find / -size +200000 -type f -ls | sort -k 7,7 -n

Sourced from: http://wikis.sun.com/display/BigAdmin/Finding+Large+Files+and+Directories+in+the+Solaris+OS

Here is the method to follow to clean it up while your listener is still running.

Log into your listener from the command prompt (make sure your Oracle environment is set to that of where the listener is started from, the commands below assume there is only one (default)  listener running ) :

$lsnrctl

..

LSNRCTL>  set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
LISTENER parameter “log_status” set to OFF
The command completed successfully

(Now go delete/rename $ORACLE_HOME/network/log/listener.log to something else in another OS session )
LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
LISTENER parameter “log_status” set to ON
The command completed successfully
LSNRCTL> quit

Permalink — click for full blog post

Related Blogs

• Related Blogs on kill oracle process

• Related Blogs on oracle process

Permalink — click for full blog post

Related Blogs

• Related Blogs on technology webcasts

Permalink — click for full blog post

Permalink — click for full blog post

Permalink — click for full blog post

Permalink — click for full blog post

Permalink — click for full blog post