Monday, August 29, 2011

How to create user in oracle database

Following are the commands to create new user in Oracle database.

1. You have to log in to the oracle command window. In command prompt enter below command.
Syntax : sqlplus sys/DB_password@Database as sysdba;
Ex : sqlplus sys/password@orcl as sysdba;

2. Create user New_User_Name identified by password account unlock;

3. grant connect to New_User_Name;

4. grant create session, dba to New_User_Name;

5. commit;

Thursday, August 18, 2011

How to restore oracle dmp file to the database.

Here are the easiest steps to restore oracle dmp file to your database.

1. First you need to login to the sql command line terminal. Open command prompt if you are using windows and if you are using Linux environment open terminal.

2. Then you have to login to the SQL-PLUS as a system administrator. To do that use following command.
"sqlplus sys/admin@ORADB as sysdba"
Eg: D:\Documents and Settings\Administrator>sqlplus sys/admin@ORADB as sysdba

3. Then you have to create a new database user.
3.1 "create user username identified by password account unlock;"
Eg : create user chamara identified by chamara account unlock;
3.2 "grant connect to username;"
Eg : grant connect to chamara;
3.3 "grant create session, dba to chamara;"
Eg : grant create session, dba to chamara;

4. Now you have to exit from the SQL-PLUS and use "exit" command for that.

5. Finally restore your dmp file using this command.

"imp SYSTEM/test@ORADB fromuser=dbadmin touser=username IGNORE=Y FILE=file_path.dmp"

Eg : D:\Documents and Settings\Administrator>imp SYSTEM/test@ORADB fromuser=WSO2_RSCE touser=chamara IGNORE=Y FILE=D:\chamara\sample.dmp