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 ???
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.
/*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...