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 :

  1. TOAD for Oracle, this tools is database browser. I used it to produce dmp file
  2. Command Prompt

First, mix the egg with flour about a minute, eh.. sorry :).

  1. 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.

    Export to DMP Files

    Export to DMP Files

  2. Choose Export Users from the following option and click Next.

    Export users

    Export users

  3. Choose the available user that you want to export and click Next.

    Available Users

    Available Users

  4. Specify the directory location that you want to save an exporting files.

    Specify directory

    Specify directory

  5. 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.

  1. Create new user at your local oracle database
  2. And then create a new connection from TOAD to your new user that you just created
  3. 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;

  4. 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;

  5. 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”;

  6. 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;

  7. Now, open your cmd.
  8. Type IMP to command line
  9. Login with username and password of oracle user that we have created
  10. Point to directory of DMP files and do the rest of process like this picture below
Import DMP files

Import DMP files

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.

Advertisements

Posted on January 5, 2013, in #DuniaDigital. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: