Oracle export not importing correctly (missing more than half of the tables)

backupexportimportoracleoracle-11g

I have an export of a 75gb database broken up into 2gb files. When I run imp utility after creating the tablespaces and users to match the original database, I don't think all tables are created. I think this because the table names (alphabetically) go from A-D. Each of the existing table names have a matching sequence. However, the sequences go from A-W. So there "should" be about 2x more tables that start with letters C-W.

So my first question is, is the entire schema for the database stored in the first dmp file (when it is split into multiple files with exp)? Or can it be split up into multiple. Looking through the first dmp file, I can not see any indication of the missing tables.

Alternatively, is this just a bad export?

Best Answer

No, the schema definition is not stored entirely in the first file:

SQL> select segment_name, bytes/1024/1024 as size_mb from user_segments;

SEGMENT_NAME    SIZE_MB
------------ ----------
T1                   80
T2                   80


$ exp bp/bp tables=T1,T2 file=expdat1.dmp,expdat2.dmp,expdat3.dmp,expdate4.dmp filesize=50M

Export: Release 12.1.0.2.0 - Production on Wed Sep 21 16:52:04 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditi
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                             T1
continuing export into file expdat2.dmp
     675872 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                             T2
continuing export into file expdat3.dmp

continuing export into file expdate4.dmp
     675872 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

First CREATE TABLE is in the first file, second CREATE TABLE is in the second file:

$ grep -a "CREATE TABLE" *dmp
expdat1.dmp:CREATE TABLE "T1" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 83886080 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
expdat2.dmp:CREATE TABLE "T2" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 83886080 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS

When you run the import, you should provide the name of all dumpfiles, in the correct order.

Related Question