This is more for my own notes but you are welcome to glean from it what you can.
There are a couple main steps involved in pumping a DMP file back into a blank Oracle database.
- Round up the user, schema and tablespace info
- Create the tablespaces
- Create the users
- Create a parameter file
- Import the DMP file
For (1) this can be done a few ways. First, get the info from the person who gave you the DMP file. Failing that, use a tool to read the DMP file (notepad works I think) and glean the info.
For (2) I would suggest putting the commands into a SQL file. I saved this file as CreateTablespaces.sql. Here are the contents:
| |
DROP tablespace [tablespaceName]; |
| |
create tablespace [tablespaceName] datafile '[path]' size [size & k|m|g] -- k=kilobytes, m=megabytes, g=gigabytes |
| |
EXTENT MANAGEMENT LOCAL; |
| |
DROP tablespace [tablespaceNameForIndices]; |
| |
create tablespace [tablespaceNameForIndices] datafile '[pathForIndices]' size [size & k|m|g] |
| |
EXTENT MANAGEMENT LOCAL; |
Again, for (3) put the commands in a SQL file. I saved it as CreateUsers.sql. Here are the contents:
| |
drop user [user/schema name] cascade; |
| |
create user [user/schema name] identified by [password] |
| |
default tablespace [tablespaceName]; |
| |
grant dba to [user/schema name]; |
Now, let's create the parameters we want to use I saved this as IMP.PAR. Here are the contents
| |
userid=[dba user]/[password]@[database] |
| |
touser=[user/schema] |
| |
fromuser=[original user/schema] |
| |
file=[DMP file path] |
| |
log=[log file path] |
| |
indexes=[y|n] |
| |
grants=[y|n] |
| |
rows=[y|n] |
| |
constraints=[y|n] |
| |
ignore=[y|n] |
And now it's time to open SQL*Plus in command line mode and do our do...
| |
c:\> sqlplus [user]/[password]@[database] |
| |
SQL> @CreateTablespaces.sql |
| |
SQL> @CreateUsers.sql |
| |
SQL> exit |
| |
c:\> imp parfile=imp.par |
I hope this helps someone.