Import Schema into a Different Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Import Schema into a Different Tablespace

  1. #1
    Join Date
    May 2001
    Posts
    28

    Exclamation

    I have found many posts regarding the subject of "Importing a schema into a different tablespace" but I have not found one that shows complete instructions on how to do this from start to finish. Can someone point out to me or provide me exact instructions on how to export and import a schema from a certain tablespace into a different tablespace???

    Please help...I've been trying to do this for some time now and I have not been able to do so...

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    pando has a really nice solution, playing with quotas

  3. #3
    Join Date
    Jul 2001
    Location
    Minneapolis
    Posts
    15
    Note:1012307.6 on Metalink has pretty good instructions.

  4. #4
    Join Date
    May 2001
    Posts
    28

    Question

    Okay... I have tried all of your recommendations and I'm back to square one.

    I followed very throughly the instructions on document 1012307.6:

    1) Create new user (user-b)
    2) ALTER USER user-b QUOTA 0 ON TABLESPACE tblspace-A

    3) REVOKE UNLIMITED TABLESPACE from user-b

    4) ALTER USER user-b DEFAULT TABLESPACE tblspace-B
    QUOTA UNLIMITED ON tblspace-B

    5) Tested create test table on tblspace-A (did not allow)
    - received ORA-01536 instead of ORA-01950

    6) Tested create test table on tblspace-B (okay)

    7) Performed Import
    Results: with RESOURCE priv and without doing step 4
    it imported back to tbspace-A
    Without RESOURCE and step 4, it loaded into
    correct tblspace-B but failed with the following
    errors:

    After various combinations of removing privileges (RESOURCES) and removing tablespace allocation, I was able to load into the appropriate table space but the import failed first with "IMP-00017: following statement failed with ORACLE error 2270:..." and after reimporting with options ROWS=N, IGNORE=Y, it failed again with error "IMP-00017: following statement failed with ORACLE error 2264:"

    Please help?



  5. #5
    Join Date
    Aug 2001
    Posts
    184
    have u tried to import with fromuser=olduser touser=newuser?

  6. #6
    Join Date
    May 2001
    Posts
    28
    Yes. These are the parameters I used for the first import:

    USERID=...
    FILE=...
    LOG=...
    FROMUSER=USER-A
    TOUSER=USER-B
    BUFFER=128000
    ANALYZE=N
    GRANTS=Y
    COMMIT=Y
    INDEXES=Y
    IGNORE=Y
    CONSTRAINTS=Y
    ROWS=Y

    2nd Import:
    USERID=
    FILE=...
    LOG=...
    FROMUSER=USER-A
    TOUSER=USER-B
    BUFFER=128000
    ROWS=N
    IGNORE=Y

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Personally, I would:
    1. export USER-A
    2. imp indexfile=abc.sql
    3. edit abc.sql
    3a. change references from old TS to new TS
    3b. uncomment create table statements
    4. run abc.sql to pre-create objects from sqlplus as USER-B
    5. imp fromuser=user-a touser=user-b ignore=y
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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