How to import just data without trying to rebuild the tables?

[Doug Leonardi]

I am trying to import a particularly large table from production to test, where the definitions of the two tables (extents, etc) are different. When I use imp to selectively pull just that table I get the following error.

IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace AAMDATA
Import terminated successfully with warnings.

I am using ignore=y, which I thought would continue loading data after the failed create, but the table is still empty.

Can imp be run so that it doesn't try to create the table, or that it really ignores the error and just finishes the data load?

Thanks in advance

[Michael Schulte zur Sur]

Hi Doug,

my first idea would be:

create a database link in test instance to production instance.
create table in test as select from production table.

Would that be sufficient?

Michael

[Michael Schulte zur Sur]

Hi Doug,

here??s my second thought. Are you sure, you have enough space? Also check the compress parameter. If it is set to yes, imp will try to allocate enough for the first extent to fit all data into it. ignore=y otherwise should work.

Michael

[Indira Aramandla]

Hi,

You can import the data without the table creation by giving the ignore=Y option in the import parameters.

With the erro you are getting ORA-01658, indicates that the initial extent size of the table into which you are importing is not big enough to hold the data. The reasons could be....

1. If while exporting you had given compress=y then the import utility will try to place the entire data into one extent which will be the initial extent.

2. If you are sure that you did not give compress=Y and as you said the two table storage parameters are different, and may be the table in the test environment has smaller extent sizes. If there is enough space in the test environment then the best way would be to recreate the table before or while importing with proper sizes.

When you drop the table and recreate you have to make sure to grant the previllages that existed if any.


I hope this helps.

IA

[T G Manikandan]

extend the tablespace AAMDATA and continue importing the table with IGNORE=Y option.


Thanks

[bob hollis]

I do this sort of thing quite frequently and have similar problems.
First - I assume you do have enough space in the test instance, it just might be fragmented. If you DON'T have the space, then this won't work.
Then - I precreate the table with no data using whatever storage parameters are appropriate for the test version. If I am really having problems with space I don't create the indexes either.
I then do the import with ignore=Y, and again if space is a real problem I also have indexes=N
It won't matter how the export was done, the storage clause of the existing table will be used. After the table is imported, the indexes can be created in a different tablespace - move the primary key index first.
Hope this helps

[Brian Crabtree]

What you can do is create an index file by using the "INDEXFILE=filename.sql". You will want to edit this file and change the initial extent from the current value to something that will fit in the tablespaces largest extent (select max(bytes) from dba_free_space where tablespace_name = 'AAMDATA'). You should then be able to import the table successfully with the IGNORE=Y option.

Thanks,

Brian

[Yogeeraj_1]

hi,

The error you are getting means the tablespace AAMDATA you have setup does not enough of contigous free space in it.

You should ensure that the schema owner where you have created the table is the same you are using for the import are the SAME! It seems like the IMP process is creating the table again!

or you can specify the following options when importing:

fromuser= touser=


You can use the attached script for verifying the amount of free space available.


In sqlplus. It'll show use by tablespace & database the amount of space ALLOCATED, the amount USED and by tablespace the LARGEST CONTIGOUS FREE chunk of space (the max size of a next extent in that tablespace).

Also, when you export, make SURE you are not using compress=y (the default). It'll ballon up your tablespaces. The initial in the export is equal to the sum of all ALLOCATED extents for the existing table.


hope this helps!

regards
Yogeeraj

[Yogeeraj_1]

attachment