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

Http://blog.csdn.net/tianlesoftware/article/details/4718366

 

Comparison between exp/IMP and expdp/impdp and some optimization items in use

Http://blog.csdn.net/tianlesoftware/article/details/6093973

 

Oracle expdp/impdp example

Http://blog.csdn.net/tianlesoftware/article/details/6260138

 

Oracle 10g Data Pump expdp/impdp

Http://blog.csdn.net/tianlesoftware/article/details/4674224

 

Example of Oracle expdp/impdp from high version to low version

Http://blog.csdn.net/tianlesoftware/article/details/6533421

 

 

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

Http://blog.csdn.net/tianlesoftware/article/details/6277193

 

[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;

 

 

Ii. Data Pump (expdp/impdp)

 

2.1 export data of a DVD user

[Oracle @ Rac1 ~] $ Expdp DVD/dvddirectory = backup dumpfile = DVD. dmp schemas = DVD

 

Export: Release 10.2.0.4.0-production onwednesday, 21 September, 2011 20:16:59

 

Copyright (c) 2003,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

Flashback automatically enabled to preservedatabase integrity.

Starting "DVD". "sys_export_schema_01": DVD/******** directory = backupdumpfile = DVD. dmp schemas = DVD

Estimate in progress using blocks method...

Processing object typeschema_export/table/table_data

Total estimation using blocks method: 64 KB

Processing object type schema_export/user

Processing object typeschema_export/system_grant

Processing object typeschema_export/role_grant

Processing object typeschema_export/default_role

Processing object typeschema_export/pre_schema/procact_schema

Processing object type schema_export/table

Processing object typeschema_export/table/index/Index

Processing object typeschema_export/table/constraint/Constraint

Processing object typeschema_export/table/index/statistics/index_statistics

Processing object type schema_export/table/comment

.. Exported "DVD". "T1" 4.906 kb 1 rows

Master table "DVD". "sys_export_schema_01" successfully Loaded/unloaded

**************************************** **************************************

Dump File set for DVD. sys_export_schema_01is:

/U01/backup/DVD. dmp

Job "DVD". "sys_export_schema_01" successfully completed at20: 17: 34

 

 

2.2 DDL generation

 

[Oracle @ Rac1 ~] $ Impdp DVD/dvddirectory = backup dumpfile = DVD. dmp sqlfile = DVD. SQL

 

Import: Release 10.2.0.4.0-production onwednesday, 21 September, 2011 20:18:50

 

Copyright (c) 2003,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

Master table "DVD". "sys_ SQL _file_full_01" successfully Loaded/unloaded

Starting "DVD". "sys_ SQL _file_full_01": DVD/********* directory = backupdumpfile = DVD. dmp sqlfile = DVD. SQL

Processing object type schema_export/user

Processing object typeschema_export/system_grant

Processing object typeschema_export/role_grant

Processing object typeschema_export/default_role

Processing object typeschema_export/pre_schema/procact_schema

Processing object typeschema_export/table

Processing object typeschema_export/table/index/Index

Processing object typeschema_export/table/index/statistics/index_statistics

Job "DVD". "sys_ SQL _file_full_01" successfully completed at20: 18: 54

 

2.3 view DDL text

 

[Oracle @ Rac1 backup] $ cat DVD. SQL

-- Connect DVD

-- New object type path is: schema_export/user

-- Connect System

Create user "DVD" identified byvalues '1111602792579cce'

Default tablespace "users"

Temporary tablespace "Temp ";

 

-- New object type path is: schema_export/system_grant

Grant unlimited tablespace to "DVD ";

 

-- New object type path is: schema_export/role_grant

Grant "dba" to "DVD ";

 

-- New object type path is: schema_export/default_role

Alter user "DVD" default role all;

 

-- New object type path is: schema_export/pre_schema/procact_schema

-- Connect DVD

 

Begin

SYS. dbms_logrep_imp.instantiate_schema (Schema_name => sys_context ('userenv', 'current _ scheme'), export_db_name => 'anqing. regress. RDBMS. dev. us. oracle. com ', inst_scn => '123 ');

Commit;

End;

/

 

-- New object type path is: schema_export/table

Create Table "DVD". "T1"

("ID" Number

) Pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress Logging

Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645

Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

Tablespace "users ";

 

-- New object type path is: schema_export/table/index/Index

Create Index "DVD". "idx_t1" on "DVD". "T1" ("ID ")

Pctfree 10 initrans 2 maxtrans 255

Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645

Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

Tablespace "users" parallel 1;

 

Alter Index "DVD". "idx_t1" noparallel;

 

-- New object type path is: schema_export/table/index/statistics/index_statistics

Declare ind_name varchar2 (60 );

Ind_owner varchar2 (60 );

Begin

Delete from "sys". "impdp_stats ";

Ind_name: = 'idx _ t1'; ind_owner: = 'dvd ';

Insert into "sys". "impdp_stats" (type, version, flags, C1, C2, C3, C5,

N1, N2, N3, N4, N5, N6, N7, n8, records, N10, N11, n12, D1)

Values ('I', 4, 0, ind_name, null, null, 'dvd', 1, 1, 1, 1, 1, 1, 0, 1, null, null, null, null, to_date ('2017-09-21 19:45:20 ', 'yyyy-MM-DD: hh24: MI: ss '));

 

Dbms_stats.import_index_stats ('"' | ind_owner | '"', '"' | ind_name | '"', null, '"impdp_stats"', null, '"sys "');

Delete from "sys". "impdp_stats ";

End;

/

 

Compare the DDL results of exp/imp with expdp/impdp. expdp/impdp extracts DDL statements in more detail and has better readability.

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 (full) dba5 group: 142216823 (full)

Dba6 group: 158654907 (full) dba7 group: 69087192 (full) dba8 group: 172855474

DBA super group 2: 151508914 dba9 group: 102954821 chat group: 40132017 (full)

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.