Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: import schema to a different tablespace

    hi,
    I've exported my instance:

    EXP SYSTEM/MANAGER@OLDINSTANCE FULL=Y FILE=C:\EXP_FILE.DMP LOG=C:\ERROR_EXP.LOG

    after I've created new tablespace and new user:
    CREATE TABLESPACE MAX DATAFILE 'c:\MAX01.dbf' SIZE 3800M REUSE DEFAULT STORAGE
    (INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0) ONLINE;

    CREATE USER MAX IDENTIFIED BY MAX DEFAULT TABLESPACE MAX TEMPORARY TABLESPACE TEMP;

    finally I've imported tom's objects in MAX:

    IMP SYSTEM/MANAGER@NEWINSTANCE FROMUSER=TOM TOUSER=MAX FILE=C:\EXP_FILE.DMP LOG=C:\ERROR_IMP.log

    but in some tables I get these errors:

    IMP-00017: following statement failed with ORACLE error 1658:
    "CREATE TABLE "AREA" ("OFL" VARCHAR2(4), "OBL" VARCHAR2(32), "OSITE" V"
    "ARCHAR2(32), "OLS" VARCHAR2(32), "OAREA" NUMBER, "DV_ID" VARCHAR2(32), "DP_"
    "ID" VARCHAR2(32), "ODISMESSO" VARCHAR2(2), "OTYPE" VARCHAR2(64), "OCAT" VAR"
    "CHAR2(64), "OOCCUP" NUMBER, "SFL" VARCHAR2(32), "SBL" VARCHAR2(32), "SSITE""
    " VARCHAR2(32), "PRORATE" VARCHAR2(8), "STYPE" VARCHAR2(64), "SCAT" VARCHAR2"
    "(64), "SAREA" NUMBER, "SOCCUP" NUMBER, "AREA_SERV" NUMBER) PCTFREE 10 PCTU"
    "SED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 314572800 NEXT 52428"
    "800 MINEXTENTS 1 MAXEXTENTS 1000 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS "
    "1 BUFFER_POOL DEFAULT) TABLESPACE "TOM""
    IMP-00003: ORACLE error 1658 encountered
    ORA-01658: unable to create INITIAL extent for segment in tablespace TOM


    In new instance there are 2 tablespaces (TOM, MAX)

    I tried 2 solution:

    1) I've taken the tablespace TOM offline in the new instance, but when I import fromuser=TOM touser=MAX.............
    I get error because not exist tablespace TOM

    2)I removed MAX's quota on the TOM tablespace in the new instance.
    ALTER USER MAX QUOTA unlimited ON TOM;
    but when I import I get alway the same error:
    ORA-01658: unable to create INITIAL extent for segment in tablespace TOM
    I know That I must encrease tablespace TOM for avoid this error, but I must create 6 new users in 6 different tablespaces and I'd to increase tablespace TOM too much (how many GB?).

    how can I import schema to a different tablespace?

    Thanks
    Raf

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It doesnt matter what you do the users it will always try to create it in TOM as thats in the exp file.

    You can edit the dump file to change the tablespace specified to MAX (but be careful, use something like awk or sed) or alternatively let MAX share the TOM tablespace (you may need to grant quota on TOM to MAX). You could also use a tool like TOAD to generate a schema script to create all your tables (but with no data) and then use imp to import the data.

    Alan

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    your table area needs an initial extent of 3GB as continuous chunck

  4. #4
    Join Date
    Oct 2002
    Location
    London
    Posts
    5
    Best way of sorting I have found is create the tables first then import with the IGNORE=Y switch...

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    By the way the initial extent is 300MB

Posting Permissions

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