Importing Oracle DMP Files using Command Prompt
Just a moment ago, in my new workplace, I had a task that I hadn’t done yet in office. So I need to do it in my home. But I need to copy a oracle database and all it’s data to my oracle XE. I realized that if it’s not a simple things, it didn’t like MySQL (just a bit effort to export and import by sql file). And with some google activity and assistance from my workmate, the ingredient to need is :
- TOAD for Oracle, this tools is database browser. I used it to produce dmp file
- Command Prompt
First, mix the egg with flour about a minute, eh.. sorry :).
- First, open your TOAD for Oracle and my assumptions you have had a connection to oracle database that you want to export. Look at to top menu bar, find Database, chose Export, and click Export Utility Wizard.
- Choose Export Users from the following option and click Next.
- Choose the available user that you want to export and click Next.
- Specify the directory location that you want to save an exporting files.
- And click Finish.
The exporting activity has been end here. You have a DMP files and ready to use.
Now, I’m sure your oracle service already run and we can start to do importing database.
- Create new user at your local oracle database
- And then create a new connection from TOAD to your new user that you just created
- Create a tablespace with execute this following script at TOAD query editor
CREATE TABLESPACE new_office_t DATAFILE ‘C:\oraclexe\oradata\XE\new_office_t001.DBF’ SIZE 1024M AUTOEXTEND OFFLOGGINGPERMANENTEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT AUTOFLASHBACK ON;
- Create a temporary tablespace
CREATE TEMPORARY TABLESPACE new_office_tTEMP TEMPFILE ‘C:\oraclexe\oradata\XE\new_office_tTEMP01.DBF’ SIZE 28M AUTOEXTEND OFFTABLESPACE GROUP ”EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
- Do some grant option that needed
GRANT “CONNECT” TO new_eoffice WITH ADMIN OPTION;GRANT “DBA” TO new_eoffice WITH ADMIN OPTION;GRANT “EXP_FULL_DATABASE” TO new_eoffice WITH ADMIN OPTION;GRANT “IMP_FULL_DATABASE” TO new_eoffice WITH ADMIN OPTION;GRANT “RESOURCE” TO new_eoffice WITH ADMIN OPTION;ALTER USER new_eoffice DEFAULT ROLE “CONNECT”, “DBA”, “EXP_FULL_DATABASE”, “IMP_FULL_DATABASE”, “RESOURCE”;
- Alter the user that you have already create in the first time. Pointing to tablespace and tablespace before
ALTER USER new_eoffice IDENTIFIED BY “new” DEFAULT TABLESPACE new_office_t TEMPORARY TABLESPACE new_office_tTEMP;
- Now, open your cmd.
- Type IMP to command line
- Login with username and password of oracle user that we have created
- Point to directory of DMP files and do the rest of process like this picture below
And that’s all I can tell about this stuff. I hope this will be useful to everyone. And the very last word of this article, I want to say thanks for all reader.
That’s knowledge, it’s not like a meal, not like money that will vanish if we share. But, it will rise and preserve.