Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146

    Unanswered: EXPORT/IMPORT Issue

    Hi,
    I wish to migrate the entire schema to another schema (i.e. from User-A to User-B) and doing it with help of EXPORT & IMPORT utility.

    The Export is done like this:
    exp system/<password> file=A.dmp log=A.log owner=A rows=Y indexes=Y grants=Y constraints=Y

    The Import is done like this:
    imp system/<password> file=A.dmp log=A_imp.log rows=Y indexes=N grants=N constraints=Y fromuser=A touser=B

    Export & Import operation is performed successfully & had no issue & no error.

    When i checked & noticed the corresponding default tablespace usages for the User-A (defaut tablesace is A_TS) & User-B (default tablespace is B_TS), i find a big difference in usages.

    After Import, The indexes are created separately on separate Tablespace for the both the USer-B and in this case i do not notice such a big difference between index tablespace usages for users A & B.

    At the time of EXPORT, the usages of tablespace A_TS is 74.123 MB and after IMPORT, the usages of tablespace B_TS is showing only 0.125 MB. Why there is such a big difference in Tablespace Usage after Import.?? Has it accommodated evrything in ONE EXTENT or what..? Please help me to clarify & understand this phenomenon.

    Thanks.

    Regards,
    Kamesh Rastogi
    - KR

  2. #2
    Join Date
    Dec 2003
    Posts
    10
    the difference is too big but ...
    Could it be fragmentation of the original schema?

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: EXPORT/IMPORT Issue

    Originally posted by RastogiKamesh
    At the time of EXPORT, the usages of tablespace A_TS is 74.123 MB and after IMPORT, the usages of tablespace B_TS is showing only 0.125 MB. Why there is such a big difference in Tablespace Usage after Import.?? Has it accommodated evrything in ONE EXTENT or what..? Please help me to clarify & understand this phenomenon.

    Evenif it accomodate all the data in One single extent, the data size must remain the same after successfull import. So, here in your case, the data is not imported into the B_TS tablespace. I guess you should check the default tablespace of the User B as well as make sure that it has UNLIMITED TABLESPACE granted or not.

    Also find out where (tablespace) exactly all those data are imported into.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi Butty
    Thanks for quick response. How it can be fragmentation of original schema..???? And so big difference....
    If this is the case....then is it possible to defragment the origibnal schema i.e. to compress the tablespace usages....and then i will EXPORT & IMPORT it.

    Please suggest some clear and to the point answer if you can. Thanks.

    Regards,
    Kamesh Rastogi
    - KR

  5. #5
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi Hings,
    Thanks for good rsponse...It is an eye opener for me. Yes...even i was worried about the tablespace usage after import.

    I checked the User-B details, it has defaut tablespace well set as B_TS, but....but.....it had UNLIMITED TABLESPACE granted while granting CONNECT, RESOURCE role to User-B. Does it have anything to do with the UNLIMITED TABLESPACE..?? It means the data is not going to B_TS tablespace storage...it is going somewhere else during Import.

    Please help me....how can i avoid this situation and make sure that the data is using the storage correctly i.e. it should use tablespace B_TS only. Please help.

    Thanks again for ur quick & good response pls.

    Kind Regards,
    Kamesh Rastogi
    - KR

  6. #6
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi Hings,
    Thanks again to you. Yes...with ur suggestion i checked entire scenario. Yes...it was because of UNLIMITED TABLESPACE privilege. I removed this privilege and given UNLIMITED QUOTA to this USER-B on default tablespace and then IMPORTed again. It is now using 30 MB so it seems good result for me and i hv understood the reason...Why...

    Thanks Hings.

    Regards,
    Kamesh Rastogi
    - KR

Posting Permissions

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