Useful shortcuts for vi editor

Wednesday, 30 November 2016

How to export/import schema/table with Oracle Data Pump?

Define new user:
SQL> CREATE USER NEWHR
  IDENTIFIED BY newhr
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

SQL>
GRANT CREATE SESSION TO NEWHR;
GRANT ALTER SESSION TO NEWHR;
GRANT CONNECT TO NEWHR;
GRANT RESOURCE TO NEWHR;
GRANT CREATE DATABASE LINK TO NEWHR;
GRANT CREATE SEQUENCE TO NEWHR;
GRANT CREATE SESSION TO NEWHR;
GRANT CREATE SYNONYM TO NEWHR;
GRANT CREATE VIEW TO NEWHR;
GRANT CREATE TYPE TO NEWHR;
GRANT CREATE TABLE TO NEWHR;
GRANT CREATE TRIGGER TO NEWHR;

Check current Oracle directories
SQL> select * from dba_directories;

Define new/replace new Oracle Directory
SQL> create or replace directory export_dir AS 'C:\Alper_ORACLE';

Give grants to user
SQL> grant read, write on directory export_dir to hr;

Export the schema
expdp hr/hr schemas=hr directory=export_dir dumpfile=hr.dmp logfile=hr.log

Export tables
expdp hr/hr tables=employees,employees_new directory=export_dir dumpfile=hr_table.dmp logfile=hr.log

Give grants to new user
SQL> grant read, write on directory export_dir to newhr;

Import schema to new machine
impdp newhr/newhr schemas=hr remap_schema=hr:newhr directory=export_dir dumpfile=hr.dmp logfile=hr.log

Import tables to new machine
impdp newhr/newhr tables=employees,employees_new remap_schema=hr:newhr directory=export_dir dumpfile=hr_table.dmp logfile=hr_table.log

If local storage is not enough to locate export file, use database link
e.g.
HR located in A machine 
NEWHR located in B machine
"XE" is A's machine DB link connection 

On B machine:
SQL> grant create database link to newhr;

SQL> create database link old_hr connect to hr identified by "HR" using 'XE';

To check db link;
SQL> select * from v$instance@XE;

impdp newhr/newhr DIRECTORY=export_dir NETWORK_LINK=old_hr remap_schema=hr:newhr logfile=newhr.log