Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » 备份恢复

格式化imp show=y生成的sql

做为DBA,由于种种原因,有时候也是很苦逼的,虽然本来是很简单的事情。收到一堆用imp导出的dmp文件,什么信息都没有。要导入到指定表空间。在MOS上找到了脚本一个impshow2sql的脚本,但这个脚本还是不完善,生成的脚本有点乱。经过改进,现在好多了,主要做了一下改进:
删除开始的头两行
删除空行
实现了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)




关键词:shell oracle 

相关文章

在18c中通过ASM Flex DiskGroup克隆PDB
Oracle ASM Storage Limits
通过rpm包安装Oracle Database 18c
彻底搞懂Oracle RHP(Rapid Home Provisioning)
Oracle Database 18c (18.3.0) Upgrade and Migration hands-on Lab
Oracle数据库云服务
Oracle Graphpipe
How to config Oracle Member Cluster for database
Install Oracle Domain Service Cluster Step by Step
Oracle数据库备份云服务
Oracle RAC and Third Party Cloud
Open Source Languages and Oracle Database

联系我们

Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com