Database creation using export and import

[sbhujbal]

I am thinking on one problem of using export and import for new database creation.
Say I am having database "abc" and data tablespace named as "data" and having user "x" ,"y". I have exported this database and I have available export dump file.
Now I created new database "xyz" having tablespace name "users_data" instead of "data" .
Can I use existing export dump file to import into this database ???? The reason I am asking is because users are attached to tablespaces and I have doubt that whether export file also content the name of user tablespace and demands that it requires same setting for your new database ???

Sunil

[marist89]

Sure you can use your dump file.

In order to change tablespaces, you will have to:
1. create users on target database
2. dump structures to an indexfile
(imp system/manager file=yourfile.dmp indexfile=yourfile.sql full=y )
3. edit yourfile.sql and remove the REM's
4. go through every table and re-assign the tablespace (or use global search & replace is you are comfortable)
5. check for connect statements in yourfile.sql (either connect to correct user or get rid of them entirely)
6. run yourfile.sql to pre-create all the structures
7. import your data with ignore=y

That said, it's going to be REALLY slow because along with your tables being pre-created, indexes have also been pre-created. However, it works.

[sbhujbal]

Hello,
/*4. go through every table and re-assign the tablespace (or use global search & replace is you are comfortable) */

So all tables in the process of import will sit in systems tablespace and I have to move them into desired tablespace.
.....
I think this is not feasible way...
Does anybody has any other thought......or export/import cannot be used to create databases in this way and we have to stick to same logical structure of original database...


Sunil