IMP-9 Due To Deferred Storage Segment Tables (Doc ID 1318855.1)

Last updated on AUGUST 23, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 23-Aug-2013***

Symptoms

Import of a deferred segment table is generating imp-9 error when export was done in direct path mode:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via direct path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
IMP-00009: abnormal end of export file
IMP-00033: Warning: Table "BONUS" not found in export file
Import terminated successfully with warnings.

Changes

Running the steps below, the issue can be reproduced immediately:

-- Creates a user TC with two tables, the first of which is a NOSEGMENT table in an ASSM tablespace.
-- It is essential for the BONUS table to be created as NOSEGMENT to demonstrate the problem:

sqlplus /nolog
connect tc/tc
CREATE TABLE BONUS
 (ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER, COMM NUMBER);
CREATE TABLE TEST ( a number );
insert into test values(1);
commit;
prompt Table BONUS must have no segment:
select segment_created
from dba_tables where owner='TC' and table_name='BONUS';

exp TC/TC direct=true
imp TC/TC show=y full=y
^
Import fails as the export file is corrupt:
IMP-9: abnormal end of export file

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms