Need urgent help with this....please
I have an export dmp file which I have to import into a clean database. I have created a new d/b. How should I do it ? Do I need any special precautions, consideration or anything ?
Also. I have another dmp from a specific user. This user's default tablespace was system. This dmp file has to be imported into this new d/b with all the objects going into the new tablespace that I will create.
Can someone please help me with this....
you can do all of this fairly easily.
basically you just need to import the .dmp file into your database. just look at the IMPORT docs to create your parameter file and command lines.
on the second import, you can specify what tablespace you want the import to go into.
i know of no way to tell the import which tablespace to import into, if this is what the previous poster indended to say.
if i understand your task at hand, you will have to have the default tablespace of the importing-to set to the desired tablespace. set up the user to have no ability to write to system (which no user but sys or system should have). set up the user with quota granted to the new tablespace(s). if you grant resource, be sure to revoke unlimited tablespace from the user. (this may vary in versions other than 8i--check with session privs).
import to the new user. if you want indexes in another tablespace, choose indexes=n on import. then create an index build file with imp indexfile=filename and edit the tablespace clause. then run the script.
there are some threads in this forum explaining this sort of activity in great detail.
Consideration before import:
- Script CATALOG.SQL has been run once on ur new database to prepare the database for the import
- Import user session and the import database use the same character set
- Character set in the export file is the same as the import user session
- Import version is the same or higher than the export version
While for the import command, that would depend on what kind of export performed, you can get the command and parameters details from the Oracle Import documentation.
Hope this help.
Thanks for all the suggestions.
Here is what I ended up doing.
I created a new database using the DB confi asst and stored the results into a bat file and all the corresponding sql scripts.
I then went ahead and executed the bat file to create a d/b.
Then with the following command I imported the full dmp of the d/b.
imp system/password full=y file=fullexp.dmp ignore=y log=fullimp.log
I then created a new user and assigned him a specific def t/s and granted him unlimited quota on it.
with the foll imp command I imported the second dump into this user.
D:\Ora8i3\bin>imp system/manager log=scott.log file=scott.dmp tables=old fromuser=scott touser=nscott
So from all this here is a set of new question that I am still not sure of:
1) In the process of creating a new d/b was it necessary to have run all those sql scripts ?
if not then
2) What is the min number of scripts I should have run that would have created a functioning d/b with just the bare bones - absolute min - just enough for me to import the full dump. May be this question justfies it's own thread.
3) It seems that the pre requisite for a userlevel import is that the receiving schema/user must exist. True or false.
4)However at the full import level it will create all the users, roles, grants etc...However where will it store these user's objects. Should their tablespaces be precreated and how would I know from the dmp what tablespaces were assigned to these users in the d/b where this dmp was generated?