Import Database from Oracle Dump

This post is probably too elementary and simplistic for anyone with Oracle administration experience. However, developers who normally don’t perform administration tasks may find it handy for setting up a DEV Oracle database.

Suppose you need to import an Oracle dump file for creating a local database copy for development purposes. The following steps can be used as a guideline.

  1. Prerequisites:
    1. Oracle database server software has been installed.
    2. An Oracle instance (say orcl) has been set up.
  2. Create a tablespace for the imported the data and associate a user with it. It can be accomplished with a script along the lines of the following. Replace the italicized tablespace name and user name/password with your own values. You can also change the database file path. Run this script when connected as SYSDBA.
    set echo on
    spool c:\temp\orcl.log
    CREATE TABLESPACE MY_TABLESPACE DATAFILE
    'C:\oracle\product\10.2.0\oradata\orcl\MY_TABLESPACE.DBF' SIZE 512M REUSE;
    ALTER DATABASE DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl\MY_TABLESPACE.DBF'
    AUTOEXTEND ON NEXT 256M MAXSIZE 2048M;
    GRANT CONNECT TO myuser IDENTIFIED BY myuserpassword;
    GRANT CREATE ANY VIEW,RESOURCE,UNLIMITED TABLESPACE to
    myuser;
    ALTER USER myuser DEFAULT TABLESPACE MY_TABLESPACE
    TEMPORARY TABLESPACE TEMP;
    GRANT select_catalog_role TO myuser;
    spool off
    disconnect;
    exit;
  3. Import the dump file using the command-line utility present in the bin folder, e.g. C:\oracle\product\10.2.0\db_1\BIN. The command line would look like,
    imp myuser/myuserpassword@ORCL file=c:\temp\mydb.dmp full=yes log=c:\temp\imp.log
Advertisements

3 thoughts on “Import Database from Oracle Dump

  1. I wrote a tool for this purpose….

    http://tsells.wordpress.com/2009/02/23/oracle-database-management-tool-new-and-improved/

    Over the next month or two I will be releasing a stand alone .Net application that handles the import. One thing to note is most developers will not have sysdba access. It is not recommended to use the SYSDBA as it can be dangerous (even for a dev server). A specialized account can be created on the server just for creating other users. This account is safer as you can restrict deleting items (schemas, objects, etc) from the server while still being allowed to create.

  2. Hi. I have followed the steps but when I run this command
    imp myuser/myuserpassword@ORCL file=c:\temp\mydb.dmp full=yes log=c:\temp\imp.log
    I got the following errors.

    IMP-00003 Oracle error 1435 encountered
    ORA-01435 user does not exist.

    Please help me. Used exists as I checked by select username from all_users

    Thanks

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