Description of how Oracle extracts DDL statements from the Dump file
Description of how Oracle extracts DDL statements from the Dump file
Commands for Dump files include exp/imp and expdp/impdp. These four commands have been compiled in related articles.
Oracle EXP/IMP description
Comparison between exp/imp and expdp/impdp and some optimization items in use
Oracle expdp/impdp example
Oracle 10g Data Pump Expdp/Impdp
Example of Oracle expdp/impdp from high version to low version
For Dump files, we cannot extract Data directly, but we can extract the corresponding DDL statements from the Dump file through relevant parameters.
(1) If it is an Export Import (exp/imp), it is the indexfile parameter.
(2) if it is a data pump (expdp/impdp), it is the sqlfile parameter.
Preparations:
SYS @ anqing1 (rac1)> create user dvdidentified by dvd;
User created.
SYS @ anqing1 (rac1)> grant dba to dvd;
Grant succeeded.
SYS @ anqing1 (rac1)> conn dvd/dvd;
Connected.
DVD @ anqing1 (rac1)> create table t1 (idnumber );
Table created.
DVD @ anqing1 (rac1)> insert into t1values (1 );
1 row created.
DVD @ anqing1 (rac1)> commit;
Commit complete.
DVD @ anqing1 (rac1)> create index idx_t1on t1 (id );
Index created.
DVD @ anqing1 (rac1)>
1. Use the Export Import command
1.1 export the data of a dvd user and generate a dump file
[Oracle @ rac1 ~] $ Exp dvd/dvd file = dvd. dmpowner = dvd
Export: Release 10.2.0.4.0-Production onWed Sep 21 19:50:14 2011
Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connected to: Oracle Database 10 gEnterprise Edition Release 10.2.0.4.0-Production
With the Partitioning, Real applicationters ters, OLAP, Data Mining
And Real Application Testing options
Export done in US7ASCII character set andAL16UTF16 NCHAR character set
Server uses ZHS16GBK character set (possible charset conversion)
About to export specified users...
. Exporting pre-schema procedural objectsand actions
. Exporting foreign function library namesfor user DVD
. Exporting PUBLIC type synonyms
. Exporting private type synonyms
. Exporting object type definitions foruser DVD
About to export DVD's objects...
. Exporting database links
. Exporting sequence numbers
. Exporting cluster definitions
. About to export DVD's tables via ConventionalPath...
.. Exporting table T1 1 rows exported
EXP-00091: Exporting questionablestatistics.
. Exporting synonyms
. Exporting views
. Exporting stored procedures
. Exporting operators
. Exporting referential integrityconstraints
. Exporting triggers
. Exporting indextypes
. Exporting bitmap, functional andextensible indexes
. Exporting posttables actions
. Exporting materialized views
. Exporting snapshot logs
. Exporting job queues
. Exporting refresh groups and children
. Exporting dimensions
. Exporting post-schema procedural objectsand actions
. Exporting statistics
Export terminated successfully withwarnings.
1.2 extract DDL statements from the dump file
[Oracle @ rac1 ~] $ Imp dvd/dvd file = dvd. dmpfromuser = dvd touser = dvd indexfile = dvd. SQL
Import: Release 10.2.0.4.0-Production onWed Sep 21 19:50:50 2011
Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connected to: Oracle Database 10 gEnterprise Edition Release 10.2.0.4.0-Production
With the Partitioning, Real applicationters ters, OLAP, Data Mining
And Real Application Testing options
Export file created by EXPORT: V10.02.01 viaconventional path
Import done in US7ASCII character set andAL16UTF16 NCHAR character set
Import server uses ZHS16GBK character set (possiblecharset conversion)
.. Skipping table "T1"
Import terminated successfully withoutwarnings.
Note the following two points:
(1) The import command does not actually import data, but only generates all the DDL SQL statements for the corresponding user.
(2) The DDL statements of the table are annotated with REM.
[Oracle @ rac1 ~] $ Cat dvd. SQL
Rem create table "DVD". "T1" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS
REM 1 MAXTRANS 255 STORAGE (INITIAL 65536 FREELISTS 1 freelist groups 1
REM BUFFER_POOL DEFAULT) TABLESPACE "USERS" logging nocompress;
REM... 1 rows
Connect dvd;
Create index "DVD". "IDX_T1" ON "T1" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE (INITIAL 65536 FREELISTS 1 freelist groups 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS" LOGGING;
If you only want to view the index DDL, you can use the grep command to say that REM is not displayed.
Linux Grep command description
[Oracle @ rac1 ~] $Cat dvd. SQL | grep-v REM
Connect dvd;
Create index "DVD". "IDX_T1" ON "T1" ("ID") PCTFREE10 INITRANS 2 MAXTRANS
255 STORAGE (INITIAL 65536 FREELISTS 1 freelist groups 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS" LOGGING;