Re: Failure to extend rollback segment

lparsons_at_world.std.com (Lee E Parsons) wrote:
>Martin Jesterhoudt <martin_at_pi.net> wrote:
>>
>>But when importing a table, you can also get the message "unable
>>allocate temp segment". You'll get this message until version 7.1.6 (I
>>believe), because of the fact that Oracle will first import the table
>>as a kind of temporary segment. It then transforms the temporary
>>segment to the correct table.
>
>Where did you hear that this was going to change? Has Oracle changed the
>generic mesage for this case or just for IMP?
>Lee E. Parsons
This is a very common error on imports and loading of large amounts of data. All inputs generate extents in the users defined 'temporary tablespace' The users defined 'temporary tablespace' has to have enough space to handle the full size of the data being inserted. You can't see the problem after getting this error message because the temporary extents are deleted after the process fails. You can only see the effect,not the cause unless you are constantly executing a sql script during the import looking for extents of type 'temporary' from the dba_extents view. You need to use one or both of the following to correct this problem: 1. Use the COMMIT=Y option to the IMP command. You will probably have to run the IMP command from a parameter file ( my prefered way to use the tool :) ) or type in all of the options for the IMP tool on the command line. Use IMP HELP=Y to get all of the options. This will also show all of the default values.
2. Redefine the default storage clause for the tablespace define as the temporary tablespace for the username running the IMP. I have run into this problem many times. Usually the addition of the COMMIT=Y solved the problem, but not always. good luck...

-- 
Paul Teague    Systems Engineer/Unix Admin/Oracle DBA
E-mail: teague_paul_at_bah.com
#include <Standard disclaimer>  "All opinions voiced are my own, etc..."