What are the prerequisites before Import Data Pump using impdp?

Before performing an impdp operation (Import Data Pump), you should make sure that you have the following prerequisites:

1. Sufficient privileges: You should have the necessary privileges to perform the impdp operation. This typically includes the IMP_FULL_DATABASE system privilege, which allows you to perform a full database import.
2. Sufficient disk space: You should have enough disk space on the server to store the imported data.
3. Access to the dump file: You should have access to the dump file that you want to import. This file should be located in a directory that is accessible by the Oracle database server.
4. Compatibility: The dump file should be compatible with the version of the Oracle database that you are importing it into. You can check the compatibility by running the impdp utility with the SHOW=Y parameter.
5. Network connectivity: If you are importing data from a remote server, you should have network connectivity between the source and target servers.
6. Proper configuration: You should ensure that the database and the database server are properly configured and running before performing the impdp operation.
7. Storage configuration: You should ensure that the storage configuration on the target database is appropriate for the data that you are importing.

	--As SYS user
	CREATE USER dbauser IDENTIFIED BY dbauser;
	GRANT DBA TO dbauser;
	--As dbauser
	--As a DBA user create tablespaces. Modify the datafile locations as per your environment
	CREATE TABLESPACE items_tblspc DATAFILE '/u01/app/oracle/oradata/XE/items.dbf' size 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
	CREATE TABLESPACE accounts_tblspc DATAFILE '/u01/app/oracle/oradata/XE/accounts.dbf' size 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
	CREATE TABLESPACE hr_tblspc DATAFILE '/u01/app/oracle/oradata/XE/hr.dbf' size 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
	CREATE TABLESPACE users DATAFILE '/u01/app/oracle/oradata/XE/users.dbf' size 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
	--Create users
	CREATE USER operations IDENTIFIED BY operations DEFAULT TABLESPACE accounts_tblspc;
	GRANT CONNECT, RESOURCE TO operations;
	GRANT CREATE ANY TRIGGER TO operations;
	CREATE USER hr IDENTIFIED BY hr DEFAULT TABLESPACE hr_tblspc;
	GRANT CONNECT, RESOURCE TO hr;
	--As user operations
	CREATE TABLE CUSTOMERS (cust_id NUMBER NOT NULL PRIMARY KEY,
	cust_name VARCHAR2(100) NOT NULL,
	cust_location VARCHAR2(2) NOT NULL,
	cust_insert_datetime TIMESTAMP,
	cust_insert_user VARCHAR2(60))
	TABLESPACE accounts_tblspc;
	CREATE INDEX cust_name_index ON CUSTOMERS(cust_name) TABLESPACE items_tblspc;
	CREATE TABLE ACCOUNTS (act_id NUMBER NOT NULL PRIMARY KEY,
	act_cust_id NUMBER NOT NULL,
	act_bal NUMBER(10,2),
	act_insert_datetime TIMESTAMP,
	act_insert_user VARCHAR2(60),
	CONSTRAINT act_cust_fk FOREIGN KEY (act_cust_id) REFERENCES customers(cust_id))
	TABLESPACE accounts_tblspc;
	CREATE TABLE ITEMS (item_id NUMBER NOT NULL PRIMARY KEY,
	item_name VARCHAR2(60) NOT NULL,
	item_value NUMBER(5,2) NOT NULL,
	item_insert_datetime TIMESTAMP,
	item_insert_user VARCHAR2(60) )
	TABLESPACE items_tblspc;
	CREATE SEQUENCE items_seq START WITH 1 INCREMENT BY 1;
	CREATE OR REPLACE TRIGGER ITEMS_INS_TRIGGER
	BEFORE INSERT ON items
	FOR EACH ROW
	BEGIN
	IF INSERTING THEN
	SELECT items_SEQ.nextval INTO :NEW."ITEM_ID" FROM dual;
	END IF;
	END;
	/
	CREATE TABLE ORDERS (order_id NUMBER NOT NULL PRIMARY KEY,
	order_item_id NUMBER NOT NULL,
	order_act_id NUMBER NOT NULL ,
	CONSTRAINT order_item_fk FOREIGN KEY (order_item_id) REFERENCES items(item_id),
	CONSTRAINT order_act_fk FOREIGN KEY (order_act_id) REFERENCES accounts(act_id) )
	TABLESPACE items_tblspc;
	CREATE SEQUENCE orders_seq START WITH 1 INCREMENT BY 1;
	-- INSERT INTO customers table
	INSERT INTO customers(cust_id,cust_name,cust_location) VALUES (1,'John','WA');
	INSERT INTO customers(cust_id,cust_name,cust_location) VALUES (2,'Jack','CA');
	INSERT INTO customers(cust_id,cust_name,cust_location) VALUES (3,'Jill','CA');
	-- INSERT INTO accounts table
	INSERT INTO accounts(act_id,act_cust_id,act_bal) VALUES (1,1,1000);
	INSERT INTO accounts(act_id,act_cust_id,act_bal) VALUES (2,2,1000);
	INSERT INTO accounts(act_id,act_cust_id,act_bal) VALUES (3,3,1000);
	-- INSERT INTO items table
	INSERT INTO items(item_name,item_value) VALUES ('Treadmill', 100);
	INSERT INTO items(item_name,item_value) VALUES ('Elliptical',100);
	INSERT INTO items(item_name,item_value) VALUES ('Weights-1',100);
	INSERT INTO items(item_name,item_value) VALUES ('Weights-2',100);
	INSERT INTO items(item_name,item_value) VALUES ('Weights-3',100);
	INSERT INTO items(item_name,item_value) VALUES ('Weights-4',100);
	INSERT INTO items(item_name,item_value) VALUES ('Weights-5',100);
	INSERT INTO items(item_name,item_value) VALUES ('Weights-6',100);
	INSERT INTO items(item_name,item_value) VALUES ('Weights-7',100);
	INSERT INTO items(item_name,item_value) VALUES ('Weights-8',100);
	INSERT INTO orders(order_id,order_item_id,order_act_id) VALUES (orders_seq.NEXTVAL, 1,1);
	INSERT INTO orders(order_id,order_item_id,order_act_id) VALUES (orders_seq.NEXTVAL, 2,1);
	INSERT INTO orders(order_id,order_item_id,order_act_id) VALUES (orders_seq.NEXTVAL, 2,2);
	INSERT INTO orders(order_id,order_item_id,order_act_id) VALUES (orders_seq.NEXTVAL, 3,1);
	INSERT INTO orders(order_id,order_item_id,order_act_id) VALUES (orders_seq.NEXTVAL, 3,2);
	INSERT INTO orders(order_id,order_item_id,order_act_id) VALUES (orders_seq.NEXTVAL, 3,3);
	COMMIT;
	CREATE OR REPLACE PACKAGE globals IS
	TYPE items_aa IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
	g_items_aa items_aa;
	TYPE order_count_rec IS RECORD(item_id NUMBER, order_count NUMBER);
	TYPE order_count_aa IS TABLE OF order_count_rec INDEX BY PLS_INTEGER;
	END globals;
	/
	CREATE OR REPLACE PROCEDURE set_items IS
	CURSOR items_cur IS
	SELECT item_id
	FROM items;
	l_counter NuMBER:=0;
	BEGIN
	FOR items_var IN items_cur LOOP
	l_counter := l_counter + 1;
	globals.g_items_aa(l_counter) := items_var.item_id;
	END LOOP;
	dbms_output.put_line('Items Set count '||l_counter);
	END set_items;
	/
	CREATE OR REPLACE FUNCTION get_order_count RETURN globals.order_count_aa IS
	CURSOR order_count_cur(p_item_id items.item_id%TYPE) IS
	SELECT count(*)
	FROM orders
	WHERE order_item_id = p_item_id;
	l_order_count_aa globals.order_count_aa;
	l_index NUMBER;
	l_item_id NUMBER;
	l_counter NUMBER:=0;
	BEGIN
	l_index := globals.g_items_aa.FIRST;
	WHILE l_index IS NOT NULL LOOP
	l_item_id := globals.g_items_aa(l_index);
	l_counter := l_counter + 1;
	l_order_count_aa(l_counter).item_id := l_item_id;
	OPEN order_count_cur(l_item_id);
	FETCH order_count_cur INTO l_order_count_aa(l_counter).order_count;
	CLOSE order_count_cur;
	l_index := globals.g_items_aa.NEXT(l_index);
	END LOOP;
	RETURN l_order_count_aa;
	END get_order_count;
	/
	-- As hr user
	CREATE TABLE departments
	(dept_id NUMBER NOT NULL PRIMARY KEY,
	dept_name VARCHAR2(60))
	TABLESPACE accounts_tblspc;
	INSERT INTO departments (dept_id,dept_name) VALUES(1,'IT');
	INSERT INTO departments (dept_id,dept_name) VALUES(2,'Accounting');
	COMMIT;
	CREATE TABLE employee
	(emp_id NUMBER NOT NULL PRIMARY KEY,
	emp_name VARCHAR2(60),
	emp_dept_id NUMBER ,
	emp_loc VARCHAR2(60),
	emp_sal NUMBER,
	emp_status VARCHAR2(1),
	CONSTRAINT emp_dept_fk FOREIGN KEY(emp_dept_id) REFERENCES departments(dept_id));
	INSERT INTO employee(emp_id,emp_name,emp_dept_id,emp_loc,emp_sal,emp_status) VALUES(10,'Tom',1,'CA',50000,'A');
	INSERT INTO employee(emp_id,emp_name,emp_dept_id,emp_loc,emp_sal,emp_status) VALUES(20,'John',1,'CA',40000,'A');
	INSERT INTO employee(emp_id,emp_name,emp_dept_id,emp_loc,emp_sal,emp_status) VALUES(50,'Tim',2,'WA',40000,'A');
	INSERT INTO employee(emp_id,emp_name,emp_dept_id,emp_loc,emp_sal,emp_status) VALUES(60,'Jack',2,'WA',70000,'A');
	COMMIT;
	--Create a procedure
	CREATE OR REPLACE PROCEDURE update_emp IS
	BEGIN
	DBMS_OUTPUT.PUT_LINE('Inside Standalone Procedure update_emp in demo schema');
	END update_emp;
	/
	-- Create a package
	CREATE OR REPLACE PACKAGE hr_mgmt IS
	PROCEDURE update_emp;
	END hr_mgmt;
	/
	CREATE OR REPLACE PACKAGE BODY hr_mgmt IS
	PROCEDURE update_emp IS
	BEGIN
	DBMS_OUTPUT.PUT_LINE('Inside Packaged Procedure update_emp in demo schema');
	END update_emp;
	END hr_mgmt;
	/
	-- Create a function
	CREATE OR REPLACE FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER
	AUTHID DEFINER IS
	l_count NUMBER;
	BEGIN
	DBMS_OUTPUT.PUT_LINE('Inside get_emp_count in demo schema');
	SELECT COUNT(*)
	INTO l_count
	FROM employee
	WHERE emp_dept_id = p_dept_id;
	RETURN l_count;
	END get_emp_count;
	/
	-- Create an object
	CREATE TYPE myobject AS OBJECT
	(emp_id number,
	emp_name VARCHAR2(60));

view raw Create.txt hosted with by GitHub

	To run these examples you will need:
	1) Connection to an instance of Oracle, the higher the level the better.
	2) A development tool like Oracle SqlDevelper,Toad,Sqlplus etc
	3) Run Create.txt in the Before folder to create the tables you will need for these demos
	--As dbauser
	CREATE OR REPLACE DIRECTORY DATADIR AS '/home/demo/Documents/files';
	SELECT * FROM DBA_DIRECTORIES;
	GRANT READ,WRITE ON DIRECTORY DATADIR TO hr,operations;

view raw oracle-command-1.txt hosted with by GitHub

	-- As user hr
	GRANT all on employee to operations;
	-- As user operations
	CREATE OR REPLACE TRIGGER ITEMS_BR_UPD_TRIG
	BEFORE UPDATE ON hr.employee
	FOR EACH ROW
	BEGIN
	dbms_output.put_line('Before Row trigger firing');
	END;
	/
	--Let us export as hr user who does not have datapump_export_full_database priv or dba privs
	expdp hr/hr@xe DIRECTORY=datadir DUMPFILE=hrexport.dmp LOGFILE=hrexport.log SCHEMAS=hr
	--Let us look at the contents of the dump file using the impdp command and SQLFILE parameter
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=hrexport.dmp LOGFILE=hrexp.log SQLFILE=hrexport.sql
	--Let us export as dba user who has dba privs
	expdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=dbaexport.dmp LOGFILE=dbaexport.log SCHEMAS=hr
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=dbaexport.dmp LOGFILE=dbaexport.log SQLFILE=dbaexport.sql
	--Let us use import dump command to create hr_test schema and import objects from hr in it
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=dbaexport.dmp LOGFILE=remapimport.log REMAP_SCHEMA=hr:hr_test
	-- As dba user
	DROP USER hr_test cascade;
	--Let us use import dump command to create hr_test schema and import objects from hr in it and transform object ids
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=dbaexport.dmp LOGFILE=remapimport.log REMAP_SCHEMA=hr:hr_test TRANSFORM=OID:N
	-- As dba user
	DROP USER hr cascade;
	--Let us use import dump command to create hr schema and import objects from hr in it and transform object ids
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=dbaexport.dmp LOGFILE=remapimport.log SCHEMAs=hr TRANSFORM=OID:N

view raw oracle-command-2.txt hosted with by GitHub

	-- As dbause export just the metadata for the operations user
	expdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsmetadatexport.dmp LOGFILE=operationsmetadatexport.log SCHEMAS=operations CONTENT=METADATA_ONLY
	--Let us import as dba user who has dba privs
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsmetadatexport.dmp LOGFILE=operationsmetadatimport.log REMAP_SCHEMA=operations:operations_test CONTENT=METADATA_ONLY

view raw oracle-command-3.txt hosted with by GitHub

	-- As dbause export just the metadata for the operations user
	expdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsdatexport.dmp LOGFILE=operationsdatexport.log SCHEMAS=operations CONTENT=DATA_ONLY
	--Import to show errors
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsdatexport.dmp LOGFILE=operationsdatimport.log REMAP_SCHEMA=operations:operations_test CONTENT=DATA_ONLY

view raw oracle-command-4.txt hosted with by GitHub

	SELECT * FROM user_constraints WHERE constraint_type='R';
	BEGIN
	FOR var IN( SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R') LOOP
	EXECUTE IMMEDIATE 'ALTER TABLE '||var.TABLE_NAME||' DISABLE CONSTRAINT '||var.CONSTRAINT_NAME;
	END LOOP;
	END;
	/
	SELECT * FROM user_constraints WHERE constraint_type='R';
	DELETE FROM ORDERS;
	DELETE FROM ITEMS;
	DELETE FROM ACCOUNTS;
	DELETE FROM CUSTOMERS;
	COMMIT;
	--Let us import as dba user who hs dba privs
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsdatexport.dmp LOGFILE=operationsdatimport.log REMAP_SCHEMA=operations:operations_test CONTENT=DATA_ONLY
	BEGIN
	FOR var IN( SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R') LOOP
	EXECUTE IMMEDIATE 'ALTER TABLE '||var.TABLE_NAME||' ENABLE CONSTRAINT '||var.CONSTRAINT_NAME;
	END LOOP;
	END;
	/

view raw oracle-command-5.txt hosted with by GitHub

	DELETE FROM ORDERS;
	DELETE FROM ITEMS;
	DELETE FROM ACCOUNTS;
	DELETE FROM CUSTOMERS;
	COMMIT;
	BEGIN
	FOR var IN( SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R') LOOP
	EXECUTE IMMEDIATE 'ALTER TABLE '||var.TABLE_NAME||' DISABLE CONSTRAINT '||var.CONSTRAINT_NAME;
	END LOOP;
	END;
	/
	BEGIN
	FOR var IN( SELECT TABLE_NAME FROM USER_TABLES) LOOP
	EXECUTE IMMEDIATE 'ALTER TABLE '||var.TABLE_NAME||' DISABLE ALL TRIGGERS ';
	END LOOP;
	END;
	/
	--Let us import as dba user who hs dba privs
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsdatexport.dmp LOGFILE=operationsdatimport.log REMAP_SCHEMA=operations:operations_test CONTENT=DATA_ONLY
	--Let us now enable the constraints and the triggers
	BEGIN
	FOR var IN( SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R') LOOP
	EXECUTE IMMEDIATE 'ALTER TABLE '||var.TABLE_NAME||' ENABLE CONSTRAINT '||var.CONSTRAINT_NAME;
	END LOOP;
	END;
	/
	BEGIN
	FOR var IN( SELECT TABLE_NAME FROM USER_TABLES) LOOP
	EXECUTE IMMEDIATE 'ALTER TABLE '||var.TABLE_NAME||' ENABLE ALL TRIGGERS ';
	END LOOP;
	END;
	/
	SELECT item_seq.NEXTVAL FROM dual;

view raw oracle-command-6.txt hosted with by GitHub

	--Second iteration, using include and exclude
	expdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsmetadatexportnoref.dmp LOGFILE=operationsmetadatexportnoref.log SCHEMAS=operations CONTENT=METADATA_ONLY EXCLUDE=REF_CONSTRAINT,TRIGGER
	--Let us import as dba user who hs dba privs
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsmetadatexportnoref.dmp LOGFILE=operationsmetadataimportnoref.log REMAP_SCHEMA=operations:operations_cert CONTENT=METADATA_ONLY
	expdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsjustdatexport.dmp LOGFILE=operationsjustdatexport.log SCHEMAS=operations CONTENT=DATA_ONLY
	--Let us import as dba user who hs dba privs
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsjustdatexport.dmp LOGFILE=operationsjustdatimport.log REMAP_SCHEMA=operations:operations_cert CONTENT=DATA_ONLY
	expdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsmetadareftexport.dmp LOGFILE=operationsmetadareftexport.log SCHEMAS=operations CONTENT=METADATA_ONLY INCLUDE=REF_CONSTRAINT,TRIGGER
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=operationsmetadareftexport.dmp LOGFILE=operationsmetadatarefimport.log REMAP_SCHEMA=operations:operations_cert CONTENT=METADATA_ONLY INCLUDE=REF_CONSTRAINT,TRIGGER

view raw oracle-command-7.txt hosted with by GitHub

	--As dbauser --Create users
	CREATE USER combined IDENTIFIED BY combined DEFAULT TABLESPACE USERS;
	expdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=combined.dmp LOGFILE=combined.log SCHEMAs=operations,hr
	impdp dbauser/dbauser@xe DIRECTORY=datadir DUMPFILE=combined.dmp LOGFILE=combinedimport.log REMAP_SCHEMA=operations:combined,hr:combined TRANSFORM=OID:N

view raw oracle-command-8.txt hosted with by GitHub

Rajesh Kumar

I’m a DevOps/SRE/DevSecOps/Cloud Expert passionate about sharing knowledge and experiences. I am working at Cotocus. I blog tech insights at DevOps School, travel stories at Holiday Landmark, stock market tips at Stocks Mantra, health and fitness guidance at My Medic Plus, product reviews at I reviewed , and SEO strategies at Wizbrand. 

Please find my social handles as below;

Rajesh Kumar Personal Website
Rajesh Kumar at YOUTUBE
Rajesh Kumar at INSTAGRAM
Rajesh Kumar at X
Rajesh Kumar at FACEBOOK
Rajesh Kumar at LINKEDIN
Rajesh Kumar at PINTEREST
Rajesh Kumar at QUORA
Rajesh Kumar at WIZBRAND