Jijo K Jose

Create a user named jijo then grant directory and other privileges to user jijo. If you are not familiar with how to create and grant privileges then read the post –> Oracle DataPump 3 – Create and grant privileges to user

SQL> CREATE USER jijo IDENTIFIED BY kjose DEFAULT TABLESPACE SYSAUX TEMPORARY TBLESPACE "TEMP";

User created.

SQL> GRANT connect, create procedure, create session, create table, create type, create view, create synonym,create sequence, create trigger, resource TO jijo;

Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO jijo;

Grant succeeded.

IMPDP Command Format

After creating the user we can import the database of  scott into jijo by using the following command.

impdp <username>/<password> DIRECTORY=<directory_name> DUMPFILE=<dump_file_name> REMAP_SCHEMA=<old_schema>:<new_schema>

or

impdp jijo/kjose DIRECTORY=TEST_DIR DUMPFILE=DB11G.dmp REMAP_SCHEMA=scott:jijo

Read more »

Once the directory access is granted, the user scott can export his database objects with command arguments. Open command prompt (CMD) and enter the expdp command with parameters

EXPDP Command Format

expdp <username>/<password>@<database_name> directory=<directory_name> dumpfile=<dump_file_name> logfile=<log_file_name>

or

D:\> expdp scott/tiger@xe directory=TEST_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

Example

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

H:\>expdp scott/tiger@xe directory=TEST_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

Export: Release 11.2.0.2.0 – Production on Mon Aug 5 13:49:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 – Productio
n
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@xe directory=TEST_DIR d
umpfile=DB11G.dmp logfile=expdpDB11G.log

Read more »

Login as DBA

To create directory object, first you need to login as DBA (DataBase Adminstrator). Open command prompt (CMD) in windows and type the following

H:\>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 5 13:30:54 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: CONN / AS SYSDBA
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production

SQL>

Create user to default table space

SQL> CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE SYSAUX TEMPORARY TABLESPACE "TEMP";

User created.

SQL>

Grant privileges to the user

SQL> GRANT connect, create procedure, create session, create table, create type, create view, create synonym,create sequence, create trigger, resource TO scott;

Grant succeeded.

SQL>

Read more »