HELP: Import generates too many space allocation
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: HELP: Import generates too many space allocation

  1. #1
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    HELP: Import generates too many space allocation

    Hi friends,

    I'm migrating my database from 8i to 9i.
    I made a blank 9i database and I'm using the IMP/EXP utility to export the information from 8i to 9i.
    The problem is that the segments of the schemas's are growing a lot (200%), so a schema that allocates 50Mb in 8i, now allocates almost 100Mb in 9i.
    I created the control file of the 9i database with a different value of the parameter MAXINSTANCES. Is it the reason of the increment
    of the space?

    I'm importing the information in two phases, using the commands:

    imp system/manager file=mydump.dmp buffer=30000 commit=y full=y rows=y indexes=n constraints=n grants=n ignore=y log=import1.log

    imp system/manager file=mydump.dmp buffer=30000 commit=y full=y rows=n indexes=y constraints=y grants=y ignore=y log=import2.log


    Any help will be very appreciated. Thanks a lot for your answers.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,966
    What is the minumum extent sizes on your tablespaces? A large minimum extent size will cause your database to grow.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    Re: HELP: Import generates too many space allocation

    Originally posted by Turin

    I created the control file of the 9i database with a different value of the parameter MAXINSTANCES. Is it the reason of the increment
    of the space?

    No.



    I'm importing the information in two phases, using the commands:

    imp system/manager file=mydump.dmp buffer=30000 commit=y full=y rows=y indexes=n constraints=n grants=n ignore=y log=import1.log

    imp system/manager file=mydump.dmp buffer=30000 commit=y full=y rows=n indexes=y constraints=y grants=y ignore=y log=import2.log
    Probably what's happening is a combination of differing minimum extent sizes and a "feature" of the export/import process. exp by default uses compress=y so that all your extents get compressed into one large extent. To bypass this "feature", use compress=n...
    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."

  4. #4
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    Thank you very much for your answers.

    The block size of both source and target databases is the same (4096).

    I'm using LMT in the source 9i database.

    The size of the blank target tablespaces before importing the data are 200% bigger than the source tablespaces and I'm receiving the error:

    IMP-00003: ORACLE error 1658 encountered
    ORA-01658: unable to create INITIAL extent for segment in tablespace XXXXXX

    I will test using the option compress=n and I will notify you if I solve the problem.

    Thank you very much, again.

  5. #5
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    I try to use the option compress=N of the EXPORT utility with exactly the same results. A tablespace that allocates 50 Mb in 8i, now allocates 120 Mb in 9i.
    I don't know what is happening, perhaps LMT is not a good option.
    Does anybody have an idea how to solve this problem ???

    Regards and thanks again.

  6. #6
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by Turin
    I try to use the option compress=N of the EXPORT utility with exactly the same results. A tablespace that allocates 50 Mb in 8i, now allocates 120 Mb in 9i.
    I don't know what is happening, perhaps LMT is not a good option.
    Does anybody have an idea how to solve this problem ???

    Regards and thanks again.
    What about your pctfree?? and pctused???
    -nagarjuna

  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by gandolf989
    What is the minumum extent sizes on your tablespaces? A large minimum extent size will cause your database to grow.
    Table level storage clauses over write default tablespace level storage parameters.
    -nagarjuna

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,966
    Originally posted by Turin
    I try to use the option compress=N of the EXPORT utility with exactly the same results. A tablespace that allocates 50 Mb in 8i, now allocates 120 Mb in 9i.
    I don't know what is happening, perhaps LMT is not a good option.
    Does anybody have an idea how to solve this problem ???
    Regards and thanks again.
    you might want to post the sql you used to create your tablespaces. You can also write dynamic sql that will create all of your tables without foriegn or primary keys, run the script on your target database and then do a full import with ignore=y. This will remove all of the sizing from all of your tables. You will still need to create the indexes. Which you can do that through dynamic sql as well.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Turin
    I try to use the option compress=N of the EXPORT utility with exactly the same results. A tablespace that allocates 50 Mb in 8i, now allocates 120 Mb in 9i.
    I don't know what is happening, perhaps LMT is not a good option.
    Does anybody have an idea how to solve this problem ???
    Wee need more datails, but in short: LMT is good, the best and (in the near future) the only option.

    What we need to explain what is happening is at least the following:
    - UNIFORM or AUTOALLOCATE LMT?
    - if UNIFORM, what is the extent size in that tablespace?
    - what are the storage parameters for that LMT?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    Here I send you more relevant information:


    * The PCT_FREE value of all my tables in either source and target databases are the same.

    I extracted this information with the query:

    select table_name, pct_free, pct_used
    from dba_tables
    where owner='xxxx'
    order by owner, table_name


    The value of PCTFREE is not showed because I'm using automatic space management (I think that). The values that I got with the above query, in my Source Database, were: 40% and 30%.

    * I created each user's tablespace in this way:

    CREATE TABLESPACE "XXXX"
    LOGGING
    DATAFILE '/xxxfile.dbf' SIZE 50M EXTENT
    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

    I really expect to solve this problem, that doesn't let me sleep well.

    Thank you all of you that share your knowledge with the rest of the world.

    Regards!
    Last edited by Turin; 03-27-2003 at 01:19 PM.

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