DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Database creation using export and import

  1. #1
    Join Date
    Oct 2000
    Posts
    28
    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    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.


  3. #3
    Join Date
    Oct 2000
    Posts
    28
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width