删除开始的头两行
删除空行
实现了STRORAGE,PCTFREE,TABLESPACE的换行
去掉双引号(")
以逗号结尾换新行
如果加上egrep -v 'STORAGE|PCTFREE|TABLESPACE|PARTITION'还可去掉存储字句
在最后一行增加 /
impshow2sql.sh
#!/bin/bash
awk ' BEGIN { prev=";" }
/ \"CREATE / { N=1; }
/ \"ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\n/\n\n"; N++ }
/\"$/ { prev=""
if (N==0) next;
s=index( $0, "\"" );
if ( s!=0 ) {
printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )
prev=substr($0,length($0)-1,1 );
}
if (length($0)<78)
printf( "\n" );}' $* |
sed '1,2d;/^$/d;
s/PCTFREE/~&/g;
s/STORAGE *(I/~STORAGE(I/g;
s/) TABLESPACE/)~TABLESPACE/g;
s/[( ]PARTITION /~&/g;
s/, "/,~ "/g;
s/ (\"/~&/g;
s/ , / ,~/g;
s/ )/)/g;
s/"//g'|tr "~" "\n" #|egrep -v 'STORAGE|PCTFREE|TABLESPACE|PARTITION'
echo "/"
使用指南
生成sql脚本
cd /pgold/dmp for i in `ls *.dmp` do imp user/password fromuser=fuser touser=fuser file=$i log=$i.sql show=y statistics=none done修改脚本权限为755
chmod 755 impshow2sql.sh执行脚本
./impshow2sql.sh /tmp/showfile > /tmp/imp.sql
for i in `ls *.sql`
do
./impshow2sql.sh $i > ${i}_new.sql
done
创建对象
sqlplus user/password @/tmp/imp.sql导入数据
for i in `ls *.dmp`
do
imp user/password fromuser=fuser touser=fuser file=$i log=$i.log ignore=y statistics=none
done
for a in {6..1}
do
/pgold/zzdi/scripts/general.sh $a
done
修改表空间的名字，可以在上面语句egrep之前加上下面的内容可实现 awk '{ for (i=1;i<=NF;i++)
if ($i != "TABLESPACE" && $i != "STORAGE(INITIAL")
printf("%s ",$i);
else
{
if ($i == "TABLESPACE")
printf("TABLESPACE USERS ");
if ($i == "STORAGE(INITIAL")
printf("STORAGE( ");
i++;
}
printf("\n");
A.不生成CREATE开始的语句
\"CREATE / { N=1; }
修改为
\"CREATE / { N=0; }
B.只生成CREATE TRIGGER字句 把之前的N=1全部改为N=0，增加下面的行
/ \"CREATE TRIGGER/ { N=1; }
C.如果不生成创建语句的存储字句，可以去掉impshow2sql脚本中的##|egrep -v 'STORAGE|PCTFREE|TABLESPACE|PARTITION'
下面的脚本可以修改表空间，但是比较乱(educational purposes only)
#!/usr/bin/ksh
grep '^ \"' <$1 | sed -e 's/^ \"//g' -e 's/\"//g' |
awk '{
if ($1=="ALTER" || $1=="GRANT" || $1=="CREATE") printf(";\n\n%s\n",$0); else printf("%s\n",$0);
}' | sed -e 's/ $/ ~/g' -e 's/^ /~ /g' |
awk '{ if (NF>1)
{
for(i=1;i<NF;i++)
printf("%s ",$i);
printf("\n%s",$NF);
}
else
printf("%s\n",$0);
}' |
awk '{ if ($NF == "TABLESPACE" || $NF == "STORAGE(INITIAL")
{
for(i=1;i<NF;i++)
printf("%s ",$i);
printf("\n%s ",$NF);
}
else
printf("%s\n",$0);
}' | sed 's/~/ /g' |
awk '{ for (i=1;i<=NF;i++)
if ($i != "TABLESPACE" && $i != "STORAGE(INITIAL")
printf("%s ",$i);
else
{
if ($i == "TABLESPACE")
printf(" TABLESPACE USERS ");
if ($i == "STORAGE(INITIAL")
printf("STORAGE( ");
i++;
}
printf("\n");
}' |sed -e '1,1s/;//g' |
egrep -v '^$'
echo ";"
Reference
https://en.wikipedia.org/wiki/AWK
http://tldp.org/LDP/abs/html/sedawk.html
https://www.gnu.org/software/gawk/manual/gawk.html
http://www.grymoire.com/Unix/Sed.html
http://www.grymoire.com/Unix/Awk.html
https://www.gnu.org/software/sed/manual/sed.html
Unix Script: IMPSHOW2SQL - Extracting SQL from an EXPORT file (Doc ID 29765.1)
Import Tables From An Export Dump To A Different Tablespace (Doc ID 372992.1)
