Home arrow Oracle arrow Six Must Know Oracle Commands

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.

TABLE OF CONTENTS:
  1. Six Must Know Oracle Commands
  2. The Oracle SQL Connect Command
  3. The Oracle SQL Create User/Grant Command
  4. The Create Table, Insert, and Select Oracle SQL Commands
By: Codex-M
Rating: starstarstarstarstar / 5
March 16, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Codex-M
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: