DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Import Error - Invalid next Storage

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi there,

    I have created a clone database for testing purposes, however when I try to import my production data I am getting the following error.

    ORA-02219 Invalid next storage option value.

    When I exported production I used the parameter compress = y
    I believe that my problem is due to fragmentation of the tablespace.
    The whole USER_DATA tablespace is empty, however the fragmentation is 80%

    I dropped the tablespace and OS deleted the datafiles, and recreated them however each time I add a datafile the frag% goes up.
    The datafiles are 11Gb in total using 6 files – this is plenty for out needs.

    Can anyone give me any advice on how to get round my import problem.
    I have attempted to coalesce the tablespace but the % stays the same (even though the datafiles are completely empty).

    Many thanks

    Alison

  2. #2
    Join Date
    Jan 2001
    Posts
    3,134
    Could this have anything to do with PCT INCREASE value on one of you tables?

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Could do PCT INCREASE on that table is 50, not sure how to get round that though, as it is exported?

  4. #4
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Sorry, just thinking...

    Why would the tablespace become fragmented as I add the datafiles, even before I start the full import (the database was created from scratch) the %frag is 80%.

    Any ideas

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134

    Lightbulb

    Can't you do 2 imports, one with rows=n just to get your structures in place. This way you can alter all the tables with PCT increase set at 50%, then import your data once that is corrected. From my experience here no table should have a 50% increase.

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Cool

    If you create the tables with the primary keys before you do the import, and without foreign keys, then do an import ignore=y you will be able to specify new storage parameters on the tables and the import will ignore the sizing of the tables. I would also suggest locally managed tablespaces. uniform extent sizes would be good for a test system.

  7. #7
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi there,

    Thank you all for your replies,

    I tried to create the table using SQL however I still get the error
    ORA-01658: unable to create INITIAL extent for segment in tablespace USER_DATA

    The values are as follows:
    INITIAL 2147483646 NEXT 2866839552 MINEXTENTS 1

    I understand that this is a huge INITIAL due to the compress = y parameter.

    My question still is though, why does adding datafiles to an empty tablespace fragment it, if this didn't happen I could create the table first (before anything else) and them run the import as usual - this is by far my largest table with over a million rows.

    Cheers

    A

  8. #8
    Join Date
    Jan 2001
    Posts
    3,134
    Originally posted by gandolf989
    If you create the tables with the primary keys before you do the import, and without foreign keys, then do an import ignore=y you will be able to specify new storage parameters on the tables and the import will ignore the sizing of the tables. I would also suggest locally managed tablespaces. uniform extent sizes would be good for a test system.
    I'm spoiled, most of out DB's do not use RI (referential integrity) So constraints are really never an issue.

    MH
    I remember when this place was cool.

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