Hello,
can someone shed what is the best way to import a full database into a brand new database? In this case when i create a new database with DBCA do i give tablespaces or how to create a new DB in order to import…
Please do let me know.
Thank you.
Evening Sujatha,
can someone shed what is the best way to import a full database into a
brand new database?
Briefly, don't!
In this case when i create a new database with DBCA
do i give tablespaces or how to create a new DB in order to import....
You will need to create a new database to import into because Oracle
will not create a brand new one when you import. You can use DBCA to
build a new database but my advice is to build it with nothing more than
the default tablespaces.
Once you have the new database up and running you can create new
tablespaces (in Toad I presume since you are posting to a Toad list) to
match those required by the user(s) that you wish to import.
The next step is to create the users you need and assign a default (and
temporary) tablespace to them. They will also need the same rights as
the original database if the new one is to be used in a similar manner.
If not, you should decide on the correct privileges that may be required
and grant them accordingly.
Once you have created the database, tablespaces, users and granted
privileges, you simply use "imp" to import the dump file into the new
database.
You can run "imp help=y" to view a list of the parameters, however, if
you wish to import USER_A, USER_B and USER_D the following should work:
imp fromuser=USER_A,USER_B,USER_D touser=USER_A,USER_B,USER_D
file=dumpfile.name log=logfile.name ignore=y commit=y buffer=40960000
The above is all on one line. When prompted for a username, supply the
name of a user with enough privileges to import the database - "/ as
sysdba" will be sufficient,if not a little over kill!
Eventually, it will complete or fail and the logifle will tell you what
happened.
Things to note:
The dumpfile holds details of the tablespaces in the original database
that each object will be created in. If the receiving database has the
same tablespaces the imp will attempt to use those.
However, if the user owning the objects has no quota on them, then the
objects will be placed in that user's default tablespace instead.
If the user has no quota on the default tablespace, then the SYSTEM
tablespace will be tried. You really don't want any user objects in the
SYSTEM tablespace, so make sure that there is a suitable tablespace (or
more, if required) and that the user being imported has adequate quota
on the/those tablespaces.
HTH
Cheers,
Norm. [TeamT]
And please pay very close attention to the database character set