Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Import & Export Problem

    Hi

    I m exporting tables from one database and all exported tables are in system table space when i import that tables in another user schema which has its own default table space then all imported tables after import still exsist in system table space i want to place that tables in its default table space not in system tablespace plz help me out.

    thanks in advance

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    modifiy the default tablespace clause for your users you are going to import

    imp sys/<pwd>@sid file=expdat.dmp fromuser=(<usr1,..., usr{n}>) touser=(<usr1,..., usr{n}>) buffer=16000000 log=<yourlog>

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    Originally posted by osy45
    modifiy the default tablespace clause for your users you are going to import

    imp sys/<pwd>@sid file=expdat.dmp fromuser=(<usr1,..., usr{n}>) touser=(<usr1,..., usr{n}>) buffer=16000000 log=<yourlog>
    otherwise

    write a generic script to move all table from system tablespace which are of other users than system/sys

    alter table <usr1>.<table> move tablespace <usr1tablespace>; ...

  4. #4
    Join Date
    Mar 2004
    Posts
    2
    Thanks for helping but after implementing first one command
    -------------------------------------------------------------------------------
    modifiy the default tablespace clause for your users you are going to import

    imp sys/<pwd>@sid file=expdat.dmp fromuser=(<usr1,..., usr{n}> ) touser=(<usr1,..., usr{n}> ) buffer=16000000 log=<yourlog>
    --------------------------------------------------------------------------------
    the tables are still in system tablespace

    and kindly explain the second one what it will do for example

    if i connect with sys/oracle as sysdba and then i place this command

    alter table vibra.vib_employee move tablespace newvibra;

    then this comamnd will put my vib_employee table from vibra table space to newvibra tablespace.

    but what about the intergrity constraints if i put tables one by one either i disable whole database constraints and then enable it ( i think it is very risky and time consuming process ) so plz help me out with import and export solution . Thanks again

  5. #5
    Join Date
    Nov 2002
    Posts
    833
    Originally posted by abdulnauman
    Thanks for helping but after implementing first one command
    -------------------------------------------------------------------------------
    modifiy the default tablespace clause for your users you are going to import

    imp sys/<pwd>@sid file=expdat.dmp fromuser=(<usr1,..., usr{n}> ) touser=(<usr1,..., usr{n}> ) buffer=16000000 log=<yourlog>
    --------------------------------------------------------------------------------
    the tables are still in system tablespace

    and kindly explain the second one what it will do for example

    if i connect with sys/oracle as sysdba and then i place this command

    alter table vibra.vib_employee move tablespace newvibra;

    then this comamnd will put my vib_employee table from vibra table space to newvibra tablespace.

    but what about the intergrity constraints if i put tables one by one either i disable whole database constraints and then enable it ( i think it is very risky and time consuming process ) so plz help me out with import and export solution . Thanks again

    from sqlplus issue the following cmds
    drop user vibra cascade ; -- this will remove all vibra objects from system tablespace
    create user vibra identified by <your pwd>;
    alter user vibra default tablespace newvibra temporary tablespace temp;
    grant connect, resource to vibra;

    then


    imp sys/oracle file=expdat.dmp fromuser=vibra touser=vibra buffer=16000000 log=yourlog

    the default buffer is 64000 16000000 for speeding up your imp process
    fromuser advises the utilitiy to take all objects from user vibra to be placed to user vibra; by default it would be the importing user which is sys ... which as you said in your inital mail you don't want

Posting Permissions

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