ITPub博客

首页 > 数据库 > Oracle > IMPSHOW2SQL - Extracting SQL from an EXPORT file (Doc ID 29765.1)

IMPSHOW2SQL - Extracting SQL from an EXPORT file (Doc ID 29765.1)

Oracle 作者:rongshiyuan 时间:2014-02-19 15:56:51 0 删除 编辑

Unix Script: IMPSHOW2SQL - Extracting SQL from an EXPORT file (Doc ID 29765.1)

Applies to:

Oracle Database - Enterprise Edition - Version 7.3.4.0 and later
Information in this document applies to any platform.
***Checked for relevance on 29-Apr-2013***

Purpose

Checked for relevance on 15-APR-2009

This is a short script that allows you to extract the SQL from an export file.

Requirements

It is intended for use on UNIX machines.

Configuring

N/A

Instructions

Instructions are in the header of the script.

When entering the script ensure:

A> The first line in the file should be a single colon (:)

Please note this is a proto-type script so there may be some statements in the resultant file that need manual editting.

Caution

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Sample Code

:
# impshow2sql Tries to convert output of an IMP SHOW=Y command into a
# usage SQL script.
#
# To use:
# Start a Unix script session and import with show=Y thus:
#
# $ imp user/password file=exportfile show=Y log=/tmp/showfile
#
# You now have the SHOW=Y output in /tmp/showfile .
# Run this script against this file thus:
#
# $ ./impshow2sql /tmp/showfile > /tmp/imp.sql
#
# The file /tmp/imp.sql should now contain the main SQL for
# the IMPORT.
# You can edit this as required.
# Note: This script may split lines incorrectly for some statements
# so it is best to check the output.
#
# CONSTRAINT "" problem:
# You can use this script to help get the SQL from an export
# then correct it if it includes bad SQL such as CONSTRAINT "".
# Eg:
# Use the steps above to get a SQL script and then
# $ sed -e 's/CONSTRAINT ""//' infile > outfile
# Now precreate all the objects and import the export file.
#
# Extracting Specific Statements only:
# It is fairly easy to change the script to extract certain statements
# only. For statements you do NOT want to extract change N=1 to N=0
# Eg: To extract CREATE TRIGGER statements only:
# a) Change all lines to set N=0.
# Eg: / \"CREATE / { N=0; }
# This stops CREATE statements being output.
#
# b) Add a line (After the general CREATE line above):
# / \"CREATE TRIGGER/ { N=1; }
# This flags that we SHOULD output CREATE TRIGGER statements.
#
# c) Run the script as described to get CREATE TRIGGER statements.
#

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" );
}' $*

Sample Output

No sample output provided.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-1083944/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3250733