Monday, August 27, 2012

How to create an oracle schema ( user )

Working with oracle db is quite different from other databases because of the concepts adopted by oracle differ.

Following are the steps to create a user ( Schema ) in oracle default database known as orcl.

1. Log into oracle using SQLPlus.
C:/>sqlplus /nolog

2. Connect to the database with the desired schema name and password. Here, I am connecting with user name greg with password greg.

connect greg/greg@orcl as sysdba;

3. Now that, you are logged in to SQLPlus, create the user and unlock the account.

create user greg identified by greg account unlock;
grant ALL PRIVILEGES to greg;


If you want to connect with the Thin driver, you must specify the port number and SID.  Default SID ( system identifier ) is orcl for default orcl db. 

For example, if you want to connect to the database on host myhost that has a TCP/IP listener up on port 1521, and the SID  orcl , the connection url is.

jdbc:oracle:thin:greg/greg@myhost:1521:orcl

Oracle jdbc driver is  : oracle.jdbc.OracleDriver

Always use oracle6 driver which is the latest.