My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.