How to Oracle import dmp file: A Comprehensive Guide

Introduction

In this tutorial, you will learn how to Oracle import dmp file. This process involves creating a new schema and then importing the data from the DMP file into the new schema. This guide will provide step-by-step instructions to help you accomplish this task efficiently.

Step 1: Create a New Schema

Before importing the DMP file, you need to create a new schema where the data will be imported.

Connect to SQL*Plus

Navigate to the directory where your schema creation script is located and connect to SQL*Plus with DBA privileges:

cd /tmp
sqlplus / as sysdba

Run Schema Creation Script

Run the script to create the new schema. In this example, the script is named CRT_SCHEMA_newhuupv:

SQL> @/home/oracle11g/HuuPV2/CRT_SCHEMA_newhuupv

Exit SQL*Plus

After running the script, exit SQL*Plus:

SQL> exit;

Example of Schema Creation Script

Here’s an example of what your schema creation script (CRT_SCHEMA_newhuupv.sql) might look like:

CREATE USER newhuupv IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO newhuupv;
ALTER USER newhuupv DEFAULT TABLESPACE users;
ALTER USER newhuupv TEMPORARY TABLESPACE temp;

Step 2: Import the DMP File

With the new schema created, you can now import the data from the DMP file.

Set NLS_LANG Environment Variable

Set the NLS_LANG environment variable to ensure proper character set handling:

export NLS_LANG=American_America.UTF8

Run the Import Command

Use the imp utility to import the data from the DMP file. Replace oldhuupv with the source schema name and newhuupv with the target schema name:

imp userid=oldhuupv/oldhuupv@DevopsRoles fromuser=oldhuupv touser=newhuupv BUFFER=100000 file=./exp_ora.dmp log=./exp_ora.log

Explanation of Parameters

  • userid: Specifies the user credentials for the import process.
  • fromuser: Specifies the source schema from which data is exported.
  • touser: Specifies the target schema to which data will be imported.
  • BUFFER: Sets the buffer size for the import.
  • file: Specifies the path to the DMP file.
  • log: Specifies the path to the log file where the import process will be logged.

Note

  • File dump: exp_ora.dmp
  • Service Name: DevopsRoles
  • To schema: newhuupv

Conclusion

In this tutorial, we covered the steps to create a new schema and import a DMP file into Oracle. By following these instructions, you should be able to efficiently import data into a new schema. For more Oracle tutorials and tips, continue following our page!

About HuuPV

My name is Huu. I love technology, especially Devops Skill such as Docker, vagrant, git, and so forth. I like open-sources, so I created DevopsRoles.com to share the knowledge I have acquired. My Job: IT system administrator. Hobbies: summoners war game, gossip.
View all posts by HuuPV →

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.