Tools to log into the Oracle database

The two most widely used tools to log into the database are SQL *Plus and SQL Developer.

Please Note that the tool iSQL *Plus which was introduced with Oracle 9i is dropped with the release of Oracle 11g.

SQL *Plus:

It’s a client server tool for connecting to the database server and running ad hoc SQL queries or PL/SQL. This tool is available with all the various databases for different platforms, and can communicate with the database server through the Oracle Net protocol, independent of the platform (O/S) on which the client(SQL *Plus) or the server (Oracle) is installed.

In Linux installation this can be found typically in a location like /u01/app/oracle/product/db_1/bin/sqlplus

And three environment variables must be set  

ORACLE_HOME : Where the oracle software is installed.

PATH : The path to the bin directory in the Oracle home.

LD_LIBRARY_PATH : The lib directory of the Oracle home.

In windows installation there used to be 2 versions the CUI (sqlplus.exe) and GUI (sqlplusw.exe). Now the GUI version is no longer available.

In typical windows 7 installation the .exe is to be found at location

C:\app\<USERNAME>\product\11.2.0\dbhome_1\BIN\sqlplus.exe

Shortcut for which is

START >> Programs >> ORACLE HOME >> Application Development >> SQL Plus

The SQL Developer

It’s a GUI java tool (requires JRE for running), to connect to the Oracle or some non-oracle databases. It’s used to fire ad hoc SQL queries or to manage the PL/SQL Objects. Multiple connections can be open simultaneously.

Logging into the database (Oracle 11g)

After successful installation of the database system, users often have problem in logging into the database, I too had similar kind of problems when I tried to log in as SYS with the same password as I had set at the time of installation for SYS user, using SQL *Plus ( a default client-server tool in the oracle package ). Tried out every variant of the sqlplus command from the command prompt, 
for example:
sqlplus myusername/mypassword@ORCL
sqlplus myusername/mypassword@Host/ORCL
sqlplus sys/mypassword  as dba
etc. But no luck.

Note:
Help on sqlplus command is available by typing:  
sqlplus -H at the command prompt

But then if we configured Oracle Enterprise Manager Database Control during the installation, we can use it to manage our database. Alternatively, we can use Oracle Enterprise Manager Grid Control to manage our database. To display the Database Control:

1. We use a Web browser to access the Database Control URL:

   http://host:port/em
   
   

   In this example:
   • host is the name of the computer on which we installed Oracle Database
   • port is the port number reserved for the Database Control during installation
   If we do not know the correct port number to use, we look for the following line in the $ORACLE_HOME/install/portlist.ini file:

   Enterprise Manager Console HTTP Port (db_name) = 1158
   
   

   The installation reserves the first available port from the range 5500 to 5519. For example, if we installed Oracle Database on host xyz, and the Database Control uses port 1158, use the following URL:

   http://xyz:1158/em
   
   

   Oracle Enterprise Manager displays the Database Control login page. 
2. If the db is accessed from the db server itself http://localhost:1158/em can be used.
3. We Log in to the database using the user name SYS and connect as SYSDBA.
   We Use the password that we have specified for the SYS account during the installation.

 As we log in we see something like this.

We go to Server >> Security >> Users to get the below screen

Now we change the password for the problematic account and check if the account is expired and locked. We can do almost anything to a user account if logged in as a sysdba, e.g. delete, create, set roles and permissions, mark locked assign a temporary table space and so on.


After resetting the SYS password from here, now when we try logging in from the SQL *Plus, it works like a magic.

Unlocking, setting a new password, or granting privileges to other user accounts can be done from OEM db control or from the command prompt while logged in as sysdba. Like

1. SQL> CONNECT SYS/SYS_password AS SYSDBA
   

2. SQL> ALTER USER account [ IDENTIFIED BY password ] ACCOUNT UNLOCK;

The SQL Command Set

There are 16 total commands in SQL grouped under various headings

Data SELECT Language used for extracting and displaying of Data from the database.

SELECT

DML ( Data Manipulation Language ) : To manipulate the data elements that are inside the database table.

INSERT
UPDATE
DELETE
MERGE

DDL ( Data Definition Language ) : Related to the data structure of the database tables and not the actual data that resides into them. 

CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

DCL ( Data Control Language ) : Related to the Database Security.

GRANT
REVOKE

TCL ( Transaction Control Language ) : To control the behavior of a block of DMLs written as a transaction. 

COMMIT
ROLLBACK
SAVEPOINT

Note:
1. There are many a things that are coming up to my mind, definitely would discuss them but may be in some other posts.
2. Many authors put SELECT statements in DML, but SELECT does not manipulate anything.
3. Many of us often get confused with the grouping of TRUNCATE and DELETE statements. But strictly DELETE is a DML and TRUNCATE is a DDL. I would surely come back to this topic when I shall write about the two.

What is SQL?

SQL or structured query language is a 4th generation language (4GL) where you just have to mention what you want to do, and not how do it. It is used to create, manipulate and retrieve information from RDBMS (Relational Database Management Systems).

Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their systems.

For history of SQL you can have a look at Wiki which says:

After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively.

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers.

SQL or "sequel" ?

The above article from wiki clears all our doubts for the pronunciation part of it. But still.. you don't have to worry about it, the pronunciation would come automatically. When I entered the field of databases, I used to say "S-Q-L" for where ever I saw SQL. Now its like "MySQL" , "Sequel Server" or "PL/sequel". The more you love the technology the more you speak of it, the more you speak of it the more you are drawn towards the ease of saying it. SQL has three syllable sequel has two, so good for the lazy developers. 

Prerequisites for Learning Oracle SQL and PL/SQL

You can find the download links for various versions of the Oracle databases under
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Download your favorite flavor, and if you have any problems in installation then Oracle provides Installation Guides for different operating systems.

For Example (for Oracle 11g Release2) :

For Linux : http://docs.oracle.com/cd/E11882_01/install.112/e47689.pdf
For Microsoft Windows : http://docs.oracle.com/cd/E11882_01/install.112/e47798.pdf

You can also go for the Express Edition if you want
http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

For development purposes we will use SQL *Plus and SQL Developer, both comes with the default installation package. Though most recent versions of the client tools can be downloaded separately.
Like: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Note: Considering the fact that JRE is pre-installed. SQL Developer needs the prior installation of JDK 7.

Starting off with SQL and PL/SQL (Oracle 11g)

Being a techie, having a keen interest on database technologies, and after working for several years as a developer, I thought of revisiting Oracle SQL and PL/SQL. In this blog I would like to share my views and experiences as a matured learner. The database chosen for this purpose is Oracle 11g. For those who want to put in their views are welcome. If I go wrong somewhere or I'm missing something, please feel free to correct them.