Table of Contents
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!
