Six Must Know Oracle Commands

Oracle is one of the top relational databases in the world and has been used by Fortune 500 companies such as Exxon Mobil, Wal-mart, General Electric and many others. In this tutorial, we will show you six “must know” Oracle SQL commands.

Currently, only Oracle Database 10G Express – otherwise known as Oracle 10G XE – can be used freely by developers. If you do not have an Oracle installation on your computer, then you should install Oracle database 10G Express so that you can follow along and practice these commands. This tutorial is using Ubuntu 10.04 LTS- the Lucid Lynx – as the operating system. The Oracle version used in this tutorial is “Oracle Database 10g Release 2 (10.2.0.1) Express Edition for Linux x86”.

First Command: Startup/Shutdown

Obviously the first command that you should know is how to start and shutdown Oracle in Linux. Bear in mind that Unix/Linux OS is the most common operating systems used in Oracle implementations (Source: http://bit.ly/dSzjsa ).

If you cannot start Oracle, then you will not be able to execute other commands into it.  Therefore this task is essential to any beginning database administrator. Below are some common errors found when Oracle is not started:

a.) In SQLplus, you connect as SYSTEM then you get this error:


Error:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


b.) Or if you launch Database Homepage you get this error in your browser:

Could not connect to 127.0.0.1:8080


Solution:

1.) Go to System – Administration – Users and Groups.
2.) Click (null)Oracle – you should enable this account.
3.) When you enable it, it will ask what password you want to assign to that account. Make sure you write down the password. It will also ask for your Ubuntu root password for verification as well.
4.) Launch the terminal. Login as root:

 

codex-m@codex-m-desktop:~$ sudo -s -H

 

[sudo] password for codex-m:


5.) Go to Oracle home bin directory:

root@codex-m-desktop:/home/codex-m# cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/


6.) Edit nls_lang.sh using the pico command:

root@codex-m-desktop:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin# pico nls_lang.sh

Change the following from (found in the first line of nls.lang.sh):

#!/bin/sh

to:

#!/bin/bash


See the following screenshot for the correct changes (inside the yellow box): http://www.php-developer.org/screenshot/bashchange.jpg.To save the change, press Control- O then press the Enter key (the filename should still be the same). Then to exit, press Control – X. Close the terminal window.

7.) Launch another terminal window and login as “oracle” user.

codex-m@codex-m-desktop:~$ su – oracle
Password:
oracle@codex-m-desktop:~$


8.) Set the environment variables correctly. Enter this in the command prompt:

oracle@codex-m-desktop:~$ source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh


9.) Initiate the SQL plus:

oracle@codex-m-desktop:~$ sqlplus / as sysdba


This is the result (with the SQL prompt in the end):

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Mar 16 02:29:42 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

 

SQL>


10.) Finally, start Oracle in the SQL prompt using the “startup” command:

SQL> startup


This is the result:

ORACLE instance started.

 

Total System Global Area  603979776 bytes
Fixed Size 1260292 bytes
Variable Size 176162044 bytes
Database Buffers 423624704 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.

 

SQL>


Oracle is now started. If you would like to shutdown Oracle, issue this command in the SQL (to prevent permission denied errors):

SQL> exit


You will then see:

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production


Type the SQL*plus statement into the terminal again:

oracle@codex-m-desktop:~$ sqlplus / as sysdba


Safely Shutdown Oracle (using “shutdown immediate” command)

SQL> shutdown immediate


This will result in:

Database closed.
Database dismounted.
ORACLE instance shut down.

{mospagebreak title=The Oracle SQL Connect Command}

Second Command:
Connect

Now that you have started Oracle in Ubuntu, the next thing is to login as SYSTEM. The user “SYSTEM” has database administration privileges that have been created during the installation of Oracle in Ubuntu. You should remember the password you assign for “SYSTEM”. To use the “connect” command and login as SYSTEM, type the following into the SQL-plus prompt (after starting Oracle using the startup command):

SQL> connect system
Enter password:
Connected.

Once you see “connected” you are connected as “system” to Oracle. The following are some common problems relating to the CONNECT command in Oracle:

First Problem:
 

ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
Linux Error: 13: Permission denied

Second Problem:

ORA-27140: attach to post/wait facility failed


Solution:

In Linux terminal:

1.) Login as root:

codex-m@codex-m-desktop:~$ sudo -s -H
[sudo] password for codex-m:


2.) Go to root directory:

root@codex-m-desktop:/home/codex-m# cd /
root@codex-m-desktop:/#


3.) Go to /usr/lib and list the files/directories under it

root@codex-m-desktop:/# cd usr/lib
root@codex-m-desktop:/usr/lib# ls -l -a

  Look for “oracle” and make sure it has the following permission and owners:

drwxr-xr-x 3 root root 4096 2011-03-11 01:21 oracle


4.) Now cd (change directory) to oracle:

root@codex-m-desktop:/usr/lib# cd oracle


5.) Assign the following owners and groups to the xe folder:

root@codex-m-desktop:/usr/lib/oracle# chown oracle:dba xe


6.) Go to the bin directory:

root@codex-m-desktop:/usr/lib/oracle# cd xe/app/oracle/product/10.2.0/server/bin


7.) Finally change the file permission for Oracle to 6751 (chmod 6751):

root@codex-m-desktop:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin# chmod 6751 oracle


8.) Confirm the you have the correct owner and file permissions for the Oracle executable here (ls -l -a):

root@codex-m-desktop:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin# ls -l -a


This the correct file permission and owner:

-rwsr-s–x  1 oracle dba 74016776 2006-02-25 04:43 oracle

{mospagebreak title=The Oracle SQL Create User/Grant Command} 

Third Command: CREATE USER/GRANT COMMAND

It’s not recommended to use SYSTEM user during your actual work (like creating tables, doing queries, etc) within the Oracle database. For that, you should create a new admin user. To do this, you need to use the CREATE USER and GRANT command:

1.) Suppose you want to create a new Oracle database administrator named “new_admin” with a password “adminpassword”. In the SQL*plus command line, issue the CREATE USER command in the SQL prompt:

SQL> CREATE USER new_admin IDENTIFIED BY adminpassword
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;


Then press the Enter key.

2.) To grant database administrator privileges to “new_admin”, you need to use the grant command:

SQL> GRANT connect, resource, dba to new_admin;

If this command is successful you will then see, “Grant succeeded”.

{mospagebreak title=The Create Table, Insert, and Select Oracle SQL Commands}

Fourth Command: CREATE TABLE

This is an important command; the following are the steps to use it:

1.) Login as new_admin in SQL prompt:

SQL> connect new_admin
Enter password:
Connected.

2.) Suppose you would like to create a table with the following specifications:

Table name: FAVORITE_BANDS
Column name: BAND_NAME
Column type: VARCHAR2 with length 50
Constraint: Primary key index for Field name ID
Constraint name: FAVORITE_BANDS_PK

You can create the above tables using the commands below:

SQL> CREATE TABLE "FAVORITE_BANDS"
( "ID" NUMBER (6,0),
"BAND_NAME" VARCHAR2(50),
CONSTRAINT "FAVORITE_BANDS_PK" PRIMARY KEY ("ID") ENABLE
);

If you enter the commands above in the SQL*plus, just ignore the numbers beside the syntax they are just used for labeling.

Fifth Command: INSERT INTO

This command is used to insert data into your Oracle database tables. Suppose you would like to insert the following data into your FAVORITE_BANDS table:

1.) Nirvana
2.) Offspring
3.) Smashing pumpkins

It will look like in the SQL*plus command line:

SQL> INSERT INTO FAVORITE_BANDS (ID,BAND_NAME) VALUES (1,’Nirvana’);
1 row created.

SQL> INSERT INTO FAVORITE_BANDS (ID,BAND_NAME) VALUES (2,’Offspring’);
1 row created.

SQL> INSERT INTO FAVORITE_BANDS (ID,BAND_NAME) VALUES (3, ‘Smashing Pumpkins’);
1 row created.


If you are planning to insert several pieces data into the table, it would be time-consuming to type it one by one in the SQL*plus – instead, you could put all of your Insert statements into the SQL script and then execute it in the SQL command line (do not forget to place “@” symbol before the path):

SQL> @/home/codex-m/Desktop/insertthis.sql


Just replace the path /home/codex-m/Desktop/insertthis.sql to the path of your SQL file containing the INSERT statements.

For example, this is the content of insertthis.sql:

http://www.php-developer.org/wp-content/uploads/tutorials/insertthis.sql.zip

Try inserting that statements to the FAVORITE_BANDS table by executing that script in the SQL*plus.

Sixth Command: SELECT

This command is commonly used when extracting or retrieving information from the Oracle database. There are several features of the SELECT command for Oracle.

a.) Retrieve the entire data of FAVORITE_BANDS table:

SQL> select * from FAVORITE_BANDS;


b.) Retrieving entire column data of BAND_NAME from FAVORITE_BANDS table

SQL> select BAND_NAME from FAVORITE_BANDS;


It will results in:

BAND_NAME
———–
Nirvana
Offspring
Smashing Pumpkins
Metallica
Led Zeppelin
Sepultura
Greenday


c.) Retrieving rows

1.) Normal method (hard parse method: http://bit.ly/ToGbO)

SQL> select BAND_NAME from FAVORITE_BANDS where ID=2;


2.) Using Bind variables

This makes the query efficient because it is not as workload-extensive as the normal method (hard parsed method). The primary reason is that instead of repeating the entire statements to be executed, you are using variables.

Supposing you set “idnumber” as the bind variable representing ID column name. You need to declare it first.

SQL> variable idnumber NUMBER
SQL> exec :idnumber :=1


In the above code, you are declaring the bind variable “idnumber” as number type, then assign 1 to it initially.

The SELECT query now becomes (to use the bind variable it should have colon punctuation before it):

SQL> select BAND_NAME from FAVORITE_BANDS where ID= :idnumber;

It will output:

BAND_NAME
————–
Nirvana

Since you have pre-assigned the bind variable as 1 that relates to “Nirvana” band.

To make another query, instead of re-typing the entire select command, you just need to assign another value to “idnumber” which is the bind variable:

SQL> exec :idnumber :=2


Then to repeat your last command, you do not need to type the SELECT statement but simply type “/” to execute the last command:

SQL> /

This will output:

BAND_NAME
———-
Offspring

“Offspring” band relates to ID of 2 in the database table.

d.) Sorting results

One important feature of the SELECT command is to sort results. Suppose you would like to sort the results by band name for all retrieved data:

SQL> select * from FAVORITE_BANDS
ORDER BY BAND_NAME;

Or if you want a descending order of the results:

SQL> select * from FAVORITE_BANDS
ORDER BY BAND_NAME desc;


 

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan