Use awk to parse dump files

Source: Internet
Author: User

Use awk to parse dump files

Dump files are often encountered at work. Sometimes a dump is obtained, but more information is not provided, which may cause problems during import. If a user's default tablespace is user, but the table's tablespace in dump is datas01, The tablespace is automatically converted during import.
However, if the lob field exists in the table and the dump tablespace is inconsistent with the target tablespace, there is a classic 00959 problem when importing dump. The error is similar to the following format.
 
IMP-00017: following statement failed with Oracle error 959:
"Create table" XXXX_RULEGROUP "(" RULE_GROUP_ID "NUMBER (12, 0) not null enable"
"," RULE_GROUP_NAME "VARCHAR2 (60)," ENABLED "NUMBER (1, 0)," RULE_GROUP_RULES"
"" CLOB, "SCHEDULING_START_TIME" TIMESTAMP (6), "SCHEDULING_INTERVAL" NUMBER"
"(12, 0) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 104"
"8576 NEXT 1048576 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT) TABLES"
"PACE" DATAS01 "logging nocompress lob (" RULE_GROUP_RULES ") store as (TABLE"
"SPACE" indxs01" enable storage in row chunk 8192 retention nocache logging"
"STORAGE (INITIAL 1048576 NEXT 1048576 FREELISTS 1 freelist groups 1 BUFFER _"
"Pool default ))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'datas01' does not exist

Of course, there may be more complex scenarios. For example, when we need to get a dump, we only want to run a specified part of the script. Some data may be imported later.
You can consider using awk to parse the dump file. Of course, parsing the dump file directly may cause performance problems, and perl may be faster.

Introduction and use of AWK

AWK introduction and Examples

Shell script-AWK text editor syntax

Learning and using AWK in Regular Expressions

AWK diagram of Text Data Processing


Here we can filter the information. Dump the dump file to generate related dump logs. You only need to parse the dump log in the specified format.
Here we assume that the dump file name is test. dmp, And the generated dump file is imp_test.log, so data will not be imported.
Imp rows = n full = y ignore = y show = y file = test. dump log = imp_test.log userid = tests/oracle buffer = 10240000

Next, use awk for parsing. Assume that the script file name is gettabddl. sh.
Awk'
/\ "BEGIN/{N = 1 ;}
/\ "CREATE/{N = 1 ;}
/\ "Create index/{N = 1 ;}
/\ "Create unique index/{N = 1 ;}
/\ "ALTER/{N = 1 ;}
/\ "ALTER/{N = 1 ;}
/\ "ANALYZE/{N = 1 ;}
/\ "GRANT/{N = 1 ;}
/\ "COMMENT/{N = 1 ;}
/\ "AUDIT/{N = 1 ;}
N = 1 {printf "\ n/\ n"; N ++}
/\ "$ /{
If (N = 0) next;
S = index ($0 ,"\"");
Ln0 = length ($0)
If (s! = 0 ){
Lcnt ++
If (lcnt> = 30 ){
Ln = substr ($0, s + 1, length (substr ($0, s + 1)-1)
T = index (ln ,"),")
If (t = 0) {t = index (ln ,",")}
If (t = 0) {t = index (ln ,")")}
If (t> 0 ){
Printf "% s \ n % s", substr (ln, 1, t + 1), substr (ln, t + 2)
Lcnt = 0
}
Else {
Printf "% s", ln
If (ln0 <78) {printf "\ n"; lcnt = 0}
}
}
Else {
Printf "% s", substr ($0, s + 1, length (substr ($0, s + 1)-1)
If (ln0 <78) {printf "\ n"; lcnt = 0}
}
}
}
END {printf "\ n/\ n "}
'$ * | Sed '1, 2d;/^ $/d;
S/STORAGE * (INI /~ STORAGE (INI/g;
S /,"/,~ "/G;
S /(\"/~ & Amp;/g;
S/PCT [FI]/~ & Amp;/g;
S/[(] PARTITION /~ & Amp;/g;
S/) TABLESPACE /)~ TABLESPACE/g;
S /,/,~ /G;
S/DATAFILE /&~ /'| Tr "~ "" \ N"
 
Run it in this way. Imp_test.log is the generated dump imp log. Only ddl-related scripts are generated. That is, awk is used for parsing and formatting. The final generated script is gen_tabddl. SQL.
Ksh gettabddl. sh imp_test.dmp> gen_tabddl. SQL

The script generation format is as follows. You can make some changes on your own.
Create table "XXX_PARAMS"
("PARAM_KEY" NUMBER (6, 0) not null enable,
"PARAM_TYPE" VARCHAR2 (50) not null enable,
"PARAM_VALUE" VARCHAR2 (100 ))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (INITIAL 1048576 NEXT 1048576 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST" LOGGING NOCOMPRESS
/
Create unique index "XXX_PARAMS_PK" ON "XXX_PARAMS"
("PARAM_KEY ")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 1048576 NEXT 1048576 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST" LOGGING
/
Alter table "XXXX_PARAMS" add constraint "XXX_PARAMS_PK" PRIMARY KEY
("PARAM_KEY") USING INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 1048576 NEXT 1048576 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST" LOGGING ENABLE
/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.