Re: Editing results of Import with show=y

From: Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid>
Date: 2000/07/19
Message-ID: <2ce411a6.d6ee5e53@usw-ex0102-015.remarq.com>#1/1

"Robert Wagner" <RobertWagner_at_alum.mit.edu> wrote:
>In a now-deleted thread in this newsgroup ("Editing dump file
before
>Import??"), Sybrand Bakker pointed out:
>
>>>You should *NEVER EVER!!* edit the dump file. If you want to
change
> create tablespace statements, use the following procedure:
> run imp with the following parameters
> show=y full=y log=<anyfilename>
> In <anyfilename> you will find a dump of *all* commands
including
> the create tablespace statements. You could easily edit
that file, pull
>the
> statements out and change them.
>
>I've tried doing this, but the resulting file puts all
statements in
>double quotes. For example:
>
>"CREATE TABLE "TBLJOBS" ("LOTSERIAL" NUMBER(11, 0) NOT NULL
ENABLE, "FLD"
> "JOB" VARCHAR2(8) NOT NULL ENABLE, "FLDRELATD_INFO" VARCHAR2
(50)) PCTFR"
> "EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE
(INITIAL 10240
>NEXT"
> " 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1
FREELIST
>GROU"
> "PS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA""
>.. . skipping table "TBLJOBS"
> "GRANT DELETE ON "TBLJOBS" TO "MYUSER""
> "GRANT INSERT ON "TBLJOBS" TO "MYUSER""
> "GRANT SELECT ON "TBLJOBS" TO "MYUSER""
> "GRANT UPDATE ON "TBLJOBS" TO "MYUSER""
> "ALTER TABLE "TBLJOBS" ADD UNIQUE ("LOTSERIAL") USING INDEX
PCTFREE 10 "
> "INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 10240 NEXT 10240
MINEXTENTS 1
>MAXE"
> "XTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL
>DEFAULT"
> ") TABLESPACE "USER_DATA" ENABLE"
>
> After I edit this quoted script, is there a simple way of
making SQL*Plus
>accept it?
>
>Also, why DOES the "Show=y" mode put the results in double-
quotes and drop
>the semicolons at the end of the statements?
>
>

Here is some code that is supposed to edit the terminal output from the show option that I saved but I do not have a note saying I tested it. Perhaps it will help you.

#!/bin/ksh
# Make DDL usable for Oracle svrmgrl or sqlplus from the Oracle
# export/import utilities.
# GPL Copyleft.
# More stuff at http://www.tc.umn.edu/~hause011/
# NOTE: Solaris use nawk or gawk instead of awk.

# How to do it: Use Oracle imp (import) utility to get the DDL from a
# oracle exp (export) file. Then filter textfile with this awk. # The result can be used by sqlplus or used to convert to a better
# database product.

# Example:
# exp name/password full=y rows=n ...
# imp name/password show=y full=y file=xxxnamexx.dmp > textfile
# ugly_DDL.ksh textfile > bunch_of_SQL.sql

# Note: to remove storage declarations uncomment the line: # '# remove_tablespace' near the bottom of this script.

# Find lines with beginning 'space doublequote' and length < 78. # Remove beginning 'space doublequote' and end doublequote, add space at end

# as they end at whole tokens.
# Find lines with beginning 'space doublequote' and length = 78.
# Remove beginning 'space doublequote' and end doublequote
# Break at the beginning of DDL statements.

get_statements () {

awk '

      /^ "/ { if (length($0)==78) {gsub("\"$","",$0);}
                 else  {gsub("\"$"," ",$0);};
              gsub("^ \"","",$0);
              sub("^CREATE ","\nCREATE ",$0);
              sub("^ALTER ","\nALTER ",$0);
              sub("^COMMENT ","\nCOMMENT ",$0);
              sub("^GRANT ","\nGRANT ",$0);
              print }

'
}

# Grab whole blank line delimited records, put a semicolon at the # end of a record then join the broken tokens by removing \n in the records.
join_tokens () {

awk '

          BEGIN { RS="" }
          { gsub("\n","",$0); }
          { print $0";\n" }

'
}

# Remove tablespace and extent declarations. remove_tablespace () {

awk '

         { sub(" PCTFREE.*;",";",$0); }
         { print }

'
}

# Fix line lengths; sqlplus and vi have problems with long lines. semicolon_break () {

awk '

         /;.*/ && /CREATE TRIGGER/ { gsub(";",";\n",$0); }
         /;.*/ && /CREATE PACKAGE/ { gsub(";",";\n",$0); }
         /;.*/ && /CREATE FUNCTION/ { gsub(";",";\n",$0); }
         { print }

'
}

# Fix line lengths; sqlplus and vi have problems with long lines. comma_break () {

awk '

         length($0)> 1024  { gsub(",",",\n",$0); }
         { print }

'
}

# Usage message.
usage () {

    echo;
    echo 'Usage: '$0 '<text file name from export/import>'     echo;
    exit
}

###################################

# Main
#

test $# -eq 1 || usage

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Wed Jul 19 2000 - 00:00:00 CDT