When we dump the whole schema as a dumpfile, the SQL statements for creating those database objects (DDL) can be extracted from the dumpfile. First, we create the dumpfile using Oracle datapump export utility expdp.
expdp myuser/myuser dumpfile=myuserdump.dmp directory=myuser_dirWe use Oracle datapump import utility impdp and specify "sqlfile=".
impdp myuser/myuser dumpfile=myuserdump.dmp directory=myuser_dir sqlfile=myuserdump.sqlWhen "sqlfile=" is used, data import is not actually done. Instead, it produces a file containing the SQL statements to create database objects. For example, the following "create table" and "create index" are taken from the sqlfile produced by impdp. We can simple run those SQL queries to create objects.
CREATE TABLE "MYUSER"."DMPZKM" ( "CLUSTER_ID" NUMBER, "RECORD_COUNT" NUMBER, "PARENT" NUMBER, "TREE_LEVEL" NUMBER, "DISPERSION" BINARY_DOUBLE, "NORM" BINARY_DOUBLE, CONSTRAINT "DMPSKM" PRIMARY KEY ("CLUSTER_ID") ENABLE ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT); CREATE UNIQUE INDEX "MYUSER"."DMPQKM" ON "MYUSER"."DMPXKM" ("CLUSTER_ID", "ATTRIBUTE_NAME", "ATTRIBUTE_SUBNAME", "BIN_ID") PCTFREE 10 INITRANS 2 MAXTRANS 164 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DMUSER" PARALLEL 1 ;