Oracle Data Pump (expdp and impdp)

At this Article I will present Datapump (Export/ Import) tools.

Datapump help us to migrate Oracle data for copying to a new database after an upgrade or creating a backup.

Lets start step by step example

First of all the user have some privileges to use datapump tools.

Now create a backup location

SQL> create or replace directory dp_vey_dir as '/u01/backups/datapump';

Give privilege

SQL> grant read, write on directory dp_vey_dir to veysi;

Now use the tools

Full Export

For full export the user must have

EXP_FULL_DATABASE privileges

$ expdp user/password DIRECTORY=backup DUMPFILE=full_backup.dmp FULL=y LOGFILE=full_export.log

For example

$ expdp veysi/veysi DIRECTORY=dp_vey_dir DUMPFILE=Full_vey1.dmp FULL=y LOGFILE=full_vey1.log

Schema Export

$ expdp user /password DIRECTORY=backup DUMPFILE=schema_backup.dmp SCHEMAS=schema1,schema2

For example:

$  expdp veysi/veysi schemas=veysi DIRECTORY=dp_vey_dir DUMPFILE=Vey1.dmp LOGFILE=expdpvey1.log

Table Export

  

For example

$  expdp veysi/veysi tables=tbl_v_students DIRECTORY=dp_vey_dir DUMPFILE=Vey.dmp LOGFILE=expdpvey.log

Full Import

After Exporting we need to import. For that the user need IMP_FULL_DATABASE privileges.

$ impdp user/password DIRECTORY=backup DUMPFILE=full_backup.dmp FULL=y LOGFILE=full_import.log

For example

$ impdp veysi/veysi DIRECTORY=dp_vey_dir DUMPFILE=Full_vey1.dmp FULL=y LOGFILE=full_vey1.log

Schema Import

$  impdp veysi/veysi schemas=veysi DIRECTORY=dp_vey_dir DUMPFILE=Vey1.dmp LOGFILE=impdpvey1.log

Table Import

$  impdp veysi/veysi tables=v_tbl_student DIRECTORY=dp_vey_dir DUMPFILE=Vey.dmp LOGFILE=impdpvey.log

Oracle dblink

Using Dblink to connect to other database

Dblink, is bridge to connected databases.

Creating:

CREATE [PUBLIC] [PRIVATE][SHARED]DATABASE LINK dblink_name

Connect to target_user_name,

İdentified by Target_user_password

USING ’tns_name’;

For example:

CREATE SHARED PUBLIC DATABASE LINK veysi_lnk
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY sec_usr IDENTIFIED BY sec_pass
USING 'DB_ORCL';

If we want to create dblink we have to have some privileges such as:

  CREATE DATABASE LINK ,  CREATE PUBLIC DATABASE LINK

Also we can create dblink by Current user . Current user, should be the same with Remote database user and dblink user. That’s why user should be global

CREATE PUBLIC DATABASE LINK remote_connect

CONNECT TO CURRENT_USER

USING ‘DB_ORCL’;

For example:

CREATE PRIVATE DATABASE LINK remote_connect

CONNECT  TO veysi IDENTIFIED BY veysi

USING ‘DB_ORCL’;

Check your tnsname.ora :

DB_ORCL = (DESCRIPTION = (ADDRESS =
             (PROTOCOL = TCP) (Host = dborcl.localdomain) (Port = 1521))
             (CONNECT_DATA = (SID = db_orcl) (GLOBAL_NAME = db_orcl.localdomain.com)
             (SERVER = DEDICATED)))

When creating Dblink is requisite GLOBAL_NAME

So check your global name

SQL>SELECT GLOBAL_NAME FROM GLOBAL_NAME;

GLOBAL_NAME
-----------
db_orcl.localdomain

Now creating by global_name dblink;

CREATE PUBLIC DATABASE LINK db_orcl.localdomain

CONNECT  TO VEYSI IDENTIFIED BY  VEYSI

USING ‘DB_ORCL’;

Creating a view;

CREATE VIEW vw_emp_orcl

AS

SELECT * FROM employees@db_orcl.localdomain.com;

Or creating a table;

CREATE TABLE tbl_vey_emp_orcl

AS

SELECT * FROM employees@db_orcl.localdomain.com;

Updating

UPDATE employees@remote_connect

   SET salary=salary*1.1

   WHERE last_name = 'AYSE';

INSERTING

Kullanımı:

INSERT INTO employees@remote_connect(salary)

VALUES(salary*1.1);

   WHERE last_name = 'AYSE';

DELETING 

DELETE FROM employees@remote_connect ;

   WHERE employee_id = 150;

SELECT:

SELECT * from  employees@remote_connect ;

Looking at DBLINKS

SELECT * FROM all_db_links ;

or

SELECT * FROM dba_db_links; 

Dropping

DROP DATABASE LINK dblink_name

To close Dblink

ALTER SESSION CLOSE DATABASE LINK remote_connect;