Shulou(Shulou.com)06/01 Report--

[exp/imp] Import the dmp file of US7ASCII character set into the database of ZHS16GBK character set

1.1 BLOG document structure map

1.2Preface 1.2.1 introduction and precautions for technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) obsession:

How does ① import the dmp file of US7ASCII character set into the database of ZHS16GBK character set (focus, 2 methods)?

What information can ② get from the dmp file? How to get the character set of dmp file from dmp file (emphasis, N methods)?

How does ③ get the DDL statements from dmp files, such as table building, indexing statements, etc. (2 methods)

General steps for importing ④ dmp files

The role of the indexfile option of the ⑤ imp tool

The use of ⑥ Software UE, EditPlus, Pilotedit Software

1.3 what information can be obtained from the dmp file on the relevant knowledge points 1.3.1 of this article? It is often encountered in development that you need to import dmp files into an existing database. The dmp files here may come from other systems, so you generally don't know the version of the export program (exp), the export time, or the export mode. So how do you get this information from an existing dmp file? The author will explain them one by one below.

1.3.1.1 get basic information: exported version, time, exported user. In the example below, exp_ddl_lhr_02.dmp is the generated dmp file:

[ZFZHLHRDB1:oracle]: / tmp > strings exp_ddl_lhr_02.dmp | head-10

TEXPORT:V11.02.00 = "version number

DSYS = "Export using SYS user

RTABLES = "based on table schema, RUSERS means based on user mode, and RENTIRE means based on full database mode.

4096

Tue Aug 2 16:8:8 2016 Greater TMPAGUP expandable ddlcards lhrbones 02.dmpcharts generated times and file addresses = "

# C#G

# C#G

+ 00:00

BYTE

UNUSED

1.3.1.2 get the table information in the dmp file in the following example, exp_ddl_lhr_02.dmp is the generated dmp file:

[ZFZHLHRDB1:oracle]: / tmp > strings exp_ddl_lhr_02.dmp | grep "CREATE TABLE" | awk'{print $3}'| sed's / "/ / g'

EMP = "indicates that there is only one emp table in exp_ddl_lhr_02.dmp.

1.3.1.3 parsing the dmp file to generate the parfile file in the following example, exp_ddl_lhr_03.dmp is the generated dmp file:

[ZFZHLHRDB1:oracle]: / tmp > strings exp_ddl_lhr_03.dmp | grep "CREATE TABLE" | awk'{print $3}'| sed's / "/ / g' | awk'{if (FNR==1) print" tables= "$1; else print", "$1}"

Tables=DEF$_AQCALL

, DEF$_AQERROR

, DEF$_CALLDEST

, DEF$_DEFAULTDEST

, DEF$_DESTINATION

, DEF$_ERROR

, DEF$_LOB

, DEF$_ORIGIN

, DEF$_PROPAGATOR

, DEF$_PUSHED_TRANSACTIONS

, MVIEW$_ADV_INDEX

[ZFZHLHRDB1:oracle]: / tmp >

1.3.2 how to get database DDL create statement data pump tool (impdp) tool provides us with the command line option of SQLFILE, only get DDL statements, and do not really perform data import. In addition, for the sole purpose of exporting DDL statements, you can use the CONTENT=METADATA_ONLY and EXCLUDE=STATISTICS options when using expdp export, so that the exported DMP file is smaller. As follows:

Expdp\'/ AS SYSDBA\ 'DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=lhrsql20161215.log CONTENT=METADATA_ONLY SCHEMAS=SCOTT EXCLUDE=STATISTICS

Impdp\'/ AS SYSDBA\ 'DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=imp_exptest.log SQLFILE=expddl_lhr.sql

You can get the DDL statement by viewing the expddl_lhr.sql file.

The way the imp tool uses SHOW=Y LOG=GET_DDL.sql, you can see clear DDL scripts and don't actually perform data imports. In addition, if you simply export DDL statements, you can use the ROWS=N option when using exp export, so that the exported DMP file is smaller. As follows:

Exp\'/ AS SYSDBA\ 'TABLES=SCOTT.EMP FILE=/tmp/exp_ddl_lhr_01.dmp LOG=/tmp/exp_table.log BUFFER=41943040 ROWS=N COMPRESS=N

Imp\'/ AS SYSDBA\ 'FILE=/tmp/exp_ddl_lhr_01.dmp SHOW=Y LOG=/tmp/get_ddl.sql BUFFER=20480000 FULL=Y

You can get the DDL statement by viewing the get_ddl.sql file.

-generating DDL statements does not import data

-- expdp\'/ AS SYSDBA\ 'tables=lhr.exptest directory=DATA_PUMP_DIR dumpfile=exptest.dmp logfile=exp_exptest.dmp EXCLUDE=STATISTICS

-- expdp\'/ AS SYSDBA\ 'directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=lhrsql20161215.log content=metadata_only schemas=SCOTT EXCLUDE=STATISTICS

Impdp\'/ AS SYSDBA\ 'directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=imp_exptest.log sqlfile=exptest.sql

Exp\'/ AS SYSDBA\ 'tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n

Imp\'/ AS SYSDBA\ 'file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y

.

1.3.2.2 indexfile option for imp (indexfile exports ddl statements for tables and indexes)

There may be problems in exp and imp tools that can solve the problem of changing table from one library exp and then imp to another database in the indexfile parameter that does not specify tablespace and cannot imp,imp.

After Oracle's imp tool specifies the indexfile parameter, you can write the index you need to create into a text file in the form of a sql statement without importing any objects. Sql statements such as creating library tables are also written, but can be masked with rem comments.

View and modify the storage parameters of the imported object

If some tables in the original library are relatively large, the initial storage space setting of exp exported objects may be relatively high, and you need to apply for a larger storage space when importing. If only the logical structure is migrated, it will take a long time. At this time, you can use the indexfile parameter to export the sql statement, filter out the table statement with higher initial space, and create it manually. Use the ignore option to ignore object creation errors when importing again.

How to parse inexfile files: you can consider replacing regular expressions with a sed editor, or you can write a program to parse library tables with initial exceeding a certain threshold and their sql.

1.3.2.3 impdp example:-- expdp\'/ AS SYSDBA\ 'directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp

Impdp\'/ AS SYSDBA\ 'directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql

A netizen came to me and said that a dmp file was imported into the database, and the Chinese characters were always garbled. Let's see if I can help solve it. To tell you the truth, in general, garbled and installation problems, I generally do not want to take over, because it may be very simple questions, some people are lazy to use their brains and ask questions. Especially for installation problems, according to the installation documentation, step by step, there is generally no problem. Here, share a picture shared by netizens again:

However, when I asked the buddy of the character set, I could feel that he had worked hard and fumbled for himself, but he couldn't solve it, so he found me. In this case, I am determined to help. All right, let's cut the crap and look at the whole process.

1.4.1 this article introduces the project of the experimental environment.

Source db

Target db

Db Typ

Db version

10.2.0.1.0

10.2.0.1.0

Db storage

OS version and kernel version

Character set

US7ASCII

GBK

Dmp file character set

US7ASCII

US7ASCII

1.5 start to import 1.5.1 first get the relevant information of the dmp file the dmp file given by the netizens:

About 30m, about 282m after decompression:

[oracle@rhel6lhr ~] $strings / tmp/hhris.dmp | head-10

EXPORT:V10.02.01

DHHRIS

RUSERS

8192

Wed Oct 16 5:0:14 2013/data/dbbackup/expdata/hhris.dmp

# G#G

# G#G

+ 08:00

BYTE

UNUSED

[oracle@rhel6lhr ~] $strings / tmp/hhris.dmp | grep "CREATE TABLE" | awk'{print $3}'| sed's / "/ / g'

ADDTOHIS

APPOINT

APPOINTDETAIL

APPOINTMASTER

BACKUP_HISPPOINT

WEB_LOG

WEB_USER

WORK_FLOW

WORK_NODE

[oracle@rhel6lhr ~] $

[oracle@rhel6lhr ~] $cat / tmp/hhris.dmp | od-x | head-1 | awk'{print $2 $3}'| cut-c 1-2 7-8

0001

SYS@ora10g > SELECT NLS_CHARSET_NAME (TO_NUMBER ('0001)) FROM DUAL

NLS_CHARSET_NAME (TO_NUMBER ('0001))

-

US7ASCII

The following conclusions can be drawn:

1. The dmp file is exported by the client of 10.02.01

2. User export based on HHRIS

3. There are many tables under this user

4. The character set of dmp file is US7ASCII.

1.5.2 find out the DDL statement of the dmp file mainly to see if there are other tablespaces causing the problem that cannot be imported.

[oracle@rhel6lhr env_oracle] $imp\'/ AS SYSDBA\ 'file=/tmp/hhris.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y

Import: Release 10.2.0.1.0-Production on Tue May 9 14:06:22 2017

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path

Warning: the objects were exported by HHRIS, not by you

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Export client uses US7ASCII character set (possible charset conversion)

. Importing HHRIS's objects into SYS

"BEGIN"

"sys.dbms_logrep_imp.instantiate_schema (schema_name= > SYS_CONTEXT ('USERENV','")

"". The reason for the length of the text is omitted. ""

[oracle@rhel6lhr env_oracle] $sh gettabdd.sh / tmp/get_ddl.sql > / tmp/gen_tabddl.sql

[oracle@rhel6lhr env_oracle] $more / tmp/gen_tabddl.sql

BEGIN

Sys.dbms_logrep_imp.instantiate_schema (schema_name= > SYS_CONTEXT ('USERENV','CURRENT_SCHEMA'), export_db_name= >' ORACLE', inst_scn= > '59161085')

COMMIT; END

/

CREATE SEQUENCE "MICROSOFTSEQDTPROPERTIES" MINVALUE 1 MAXVALUE 999999999999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 50 NOORDER NOCYCLE

/

CREATE SEQUENCE "R_REPORTLOG_LOGID" MINVALUE 1 MAXVALUE 100000 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER CYCLE

/

.

"". The reason for the length of the text is omitted. ""

Look for the keyword tablespace and find only 1 tablespace HHRIS.

1.5.3 Database preparation dbca-silent-createDatabase-templateName General_Purpose.dbc-responseFile NO_VALUE\

-gdbname lhrdb-sid lhrdb\

-sysPassword lhr-systemPassword lhr\

-datafileDestination'/ cds/oradata'-recoveryAreaDestination'/ cds/oradata'\

-storageType FS\

-characterset ZHS16GBK-nationalCharacterSet AL16UTF16\

-sampleSchema false\

-memoryPercentage 20\

-databaseType OLTP\

-emConfiguration NONE

ORACLE_SID=lhrdb

Export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

Sqlplus / as sysdba

CREATE TABLESPACE HHRIS DATAFILE'/ cds/oradata/mydg/HHRIS01.dbf' size 1G

Create user hhris identified by lhr

Grant dba to hhris

Exit

Imp hhris/lhr file=/tmp/hhris.dmp full=Y log=/tmp/log_imp_hhrisgbk.dmp

[oracle@rhel6lhr mydg] $imp hhris/lhr file=/tmp/hhris.dmp full=Y log=/tmp/log_imp_hhrisgbk.dmp

Import: Release 10.2.0.1.0-Production on Tue May 9 14:17:55 2017

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Export client uses US7ASCII character set (possible charset conversion)

. Importing HHRIS's objects into HHRIS

. . Importing table "ADDTOHIS" 0 rows imported

. . Importing table "APPOINT" 0 rows imported

. . Importing table "APPOINTDETAIL" 0 rows imported

. . Importing table "APPOINTMASTER" 0 rows imported

"". The reason for the length of the text is omitted. ""

Import terminated successfully with warnings.

[oracle@rhel6lhr mydg] $

[oracle@rhel6lhr mydg] $

Can be successfully imported, but when querying, there are Chinese garbled.

1.5.4 to solve the garbled code, use UE or Pilotedit software to open the dmp file in hexadecimal format and modify the first-fourth bytes of line 4 of the dmp file.

Before modification:

After modification:

In fact, there is also data showing that the second and third bytes of the first line and the first-fourth bytes of line 4 need to be modified, as shown below:

After the test of wheat seedlings, it is found that all the three places have been modified and can be successfully introduced.

Save the file after modification, upload the server, re-import, query after import, and find that Chinese can be displayed normally.

1.5.5 another way not to display garbled is to import the dmp file of the US7ASCII character set into the database of the US7ASCII character set.

Dbca-silent-createDatabase-templateName General_Purpose.dbc-responseFile NO_VALUE\

-gdbname lhrdb-sid lhrdb\

-sysPassword lhr-systemPassword lhr\

-datafileDestination'/ cds/oradata'-recoveryAreaDestination'/ cds/oradata'\

-storageType FS\

-characterset US7ASCII-nationalCharacterSet AL16UTF16\

-sampleSchema false\

-memoryPercentage 20\

-databaseType OLTP\

-emConfiguration NONE

Export NLS_LANG=AMERICAN_AMERICA.US7ASCII

Imp hhris/lhr file=/tmp/hhris.dmp full=Y log=/tmp/log_imp_hhrisgbk.dmp

After import, set the client environment variable NLS_LANG to AMERICAN_AMERICA.US7ASCII on Windows, and then restart the PL/SQL DEVELOPER software to display Chinese normally.

Originally thought, then use GBK character set to export, and then import into the GBK database, the result found that this method does not work, there are always garbled. In fact, at this point, it is still possible to export the data to a file in text format, and then import the file in text format into the database of the GBK character set.

1.6 this paper summarizes that there is a way to deal with the problem of Chinese garbled code when importing the dmp file of the US7ASCII character set into the database of the ZHS16GBK character set. First, modify the characters that represent the character set in the dmp file. Second, import into the library of the US7ASCII character set, then export to text format, and then import into the library of GBK.

About Me

.

● author: wheat seedlings, only focus on the database technology, pay more attention to the application of technology

● article is updated synchronously on itpub (http://blog.itpub.net/26736162), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/abstract/1/

● article blog park address: http://www.cnblogs.com/lhrbest

● pdf version of this article and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● database written examination interview questions database and answers: http://blog.itpub.net/26736162/viewspace-2134706/

● QQ group: 230161599 WeChat group: private chat

● contact me, please add QQ friend (646634621), indicate the reason for adding

● completed in Mordor from 09:00 on 2017-05-09 to 22:00 on 2017-05-30.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

.

Pick up your phone and use Wechat client to scan the picture on the left below to follow the Wechat official account of wheat seedlings: xiaomaimiaolhr, scan the QR code on the right to join the QQ group of wheat seedlings, and learn the most practical database technology.

Cdn.qqmail.com/zh_CN/htmledition/p_w_picpaths/function/qm_open/ico_mailme_02.png ">

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.