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.
- Oracle database server software has been installed.
- An Oracle instance (say
orcl) has been set up.
- 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
set echo on
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
ALTER USER myuser DEFAULT TABLESPACE MY_TABLESPACE
TEMPORARY TABLESPACE TEMP;
GRANT select_catalog_role TO myuser;
- 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