Use awk to parse dump files

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
/