-
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
-
Could this have anything to do with PCT INCREASE value on one of you tables?
MH
I remember when this place was cool.
-
Could do PCT INCREASE on that table is 50, not sure how to get round that though, as it is exported?
-
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
-
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.
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|