0

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?

  • 1
    Check the export log (if you still have it) instead of the dump files themselves to verify tables are in the export. Then, check the import log for errors importing those tables. – Kris Johnston Sep 21 '16 at 14:33
0

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.

  • Thank you. It turns out, the export was not complete. Unfortunately, these exports were being used as back-ups... So the newest (most complete) export is still missing 6 2gb parts. The actual size of the database is closer to 150gb. Do you know if it is possible to skip parts in the import, assuming the parts with the schema are present? – Doug Sep 23 '16 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.