Oracle imp dmp file, create table statements skipped

Question

I get a dmp and install a Oracle XE for developing. However, when I use imp to import, the create table statements were skipped. Can anyone kindly help on the issue.

The screen output were captured as follow: Many Thanks!

---

C:\Documents and Settings\wong\Desktop>imp

Import: Release 10.2.0.1.0 - Production on Mon Aug 2 22:48:32 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: report 

Password:

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio n

Import file: EXPDAT.DMP > report20100524.dmp

Enter insert buffer size (minimum is 8192) 30720> 30720

Export file created by EXPORT:V10.01.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) List contents of import file only (yes/no): no > yes

Import entire export file (yes/no): no
> yes

. importing REPORT's objects into REPORT  "BEGIN  "  "sys.dbms_logrep_imp.instantiate_schema(schema_name=>'REPORT', export_db_na"  "me=>'SPACESAT', inst_scn=>'650909');"  "COMMIT; END;"  "CREATE TABLE "BASE_COUNTRY" ("IN_CODE" VARCHAR2(60) NOT NULL ENABLE, "OUTCO"  "DE" VARCHAR2(60), "OUTNAME" VARCHAR2(60), "LEVELID" NUMBER(1, 0), "IS_LEAF""  " NUMBER(1, 0), "SX" VARCHAR2(10), "PHONE" VARCHAR2(20), "TIMEX" NUMBER(2, 0"  "))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FRE"  "ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING "  "NOCOMPRESS" . . skipping table "BASE_COUNTRY"


.... many tables skipped...


"CREATE UNIQUE INDEX "XF_TC_FLOOR_U1" ON "XF_TC_FLOOR" ("XF_FLOOR" , "XF_GSI" "D" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 " "FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING" "CREATE TRIGGER "YREPORT".xf_tc_floor_insupd BEFORE INSERT OR UPDATE" "  ON xf_tc_floor" " FOR EACH ROW" "  BEGIN" "    :new.xf_lastmodtime := sysdate;" "    :new.xf_lastuploadtime := sysdate;" "  END xf_tc_floor_insupd" ";" "ALTER TRIGGER "XF_TC_FLOOR_INSUPD"  ENABLE" mport terminated successfully without warnings.

C:\Documents and Settings\wong\Desktop>

Answer 1

The IMP you are using is only importing objects owned by REPORT. Tables/indexes etc owned by other schemas won't be imported (mostly because there is a good chance that REPORT won't be able to create table in those schemas).

You could look into the FROMUSER/TOUSER command line parameters, but you may also need to use a privileged user for the import.

Answer 2

This could be due to the restrictions of Oracle XE(Express i.e. the freebie version). Specifically only one predefined "user" tablespace and a 4GB limit on storage.

While this doesnt seem too restrictive the tablespace and "initial" storage sizes are embedded in the export files table definitions. If your the exporting system specified a tablespace other than users or specified an "initial" storage allocation that cumulativly exceeds your 4GB storage allocation the import will fail. So unless the export file was created by another XE database you are likely to have problems.

You can capture the DDL (sort off) by using the -show option of the imp command.

You can also edit the .imp file to remove the "tablespace" and "initial" clauses from the DDL -- but make sure you backup the original file first as you can easily make the file unreadable.