Powered By Blogger

Monday, May 18, 2009

Export and import the schema into the same database with different name

Oracle 10.2.0
Exported "N" schema in the database "BL".
"N" Schema details.
* Schema's default tablespace is "N_data".
* Schema has its indexes in "N_index" tablespace.
* Schema has LOB objects. And its size around 8 GB.
> Exp N/Passwd file='e:\n_exp.dmp' owner=N statistics=none buffer=314572800
exported sucessfully without warnings.
Import steps :
* Try to create a user named "NV" and created a new tablespace "NV_data" and assigned as the default tablespace.
* Created the tablespace named "NV_index" for indexes and assigned Unlimited tablespace quota to user "NV".
* Granted the same roles(Connect,resource,DBA) to the newly created user.
* Tried to import
> Imp Nv/passwd file='e:\n_exp.dmp' fromuser=n touser=nv statistics=none buffer=314572800

Objects imported into the NV user, but it used 'N_data' tablespace to create the objects.
And i tried to revoke the unlimited tablespace privilege from the user "NV".
And granted unlimited tablespace privilege on the tablespace "NV_data" to "NV" user.
Import completed sucessfully. But the index was only created in the "NV_data" tablespace later i moved the indexes to Nv_index" tablespace.

Refer :

http://forums.oracle.com/forums/thread.jspa?threadID=899752&tstart=285

No comments:

Post a Comment