Unix scripts to extract DDL from imp show=y
Vikas Agnihotri
from the 'imp full=y show=y' output?
Metalink has a Perl script to do this, but i dont have Perl on my
system. Plain awk/sed scripts would be perfect.
Thanks
Norman Dunbar
read through an 'indexfile=???' file and extract each index and table
with it's tablespace into a 'mapfile'.
Then read through another 'indexfile=???' file removing all the REMs
from the table creation scripts, and changing the tablespace names to
match those in the mapfile to create a new 'schema' creation script.
If these sound useful (and they are in AWK) then email me direct for a
copy - they might need modification, but there is good documentation
with them :o)
They were used so that a database which had been created 'wrongly' by a
consultant could be compared against a 'correct' one and a rebuild
cortrectly script generated. It works for un-partitioned tables &
indexes only so should be ok for O7 and O8, provided the partitioning
option hasn't been taken advantage of.
Regards,
Norman.
PS. Just noticed, you have 'full=y' whereas mine have been tested on a
user level dump file.
------------------------------------------------------------------------
--------
Norman Dunbar EMail: NDu...@LynxFinancialSystems.co.uk
Database/Unix administrator Phone: 0113 289 6265
Lynx Financial Systems Ltd. Fax: 0113 201 7265
URL: http://www.LynxFinancialSystems.com
------------------------------------------------------------------------
--------
Nuno Souto
Agnihotri) wrote:
>Can someone please point me to some awk/sed scripts to extract DDL
>from the 'imp full=y show=y' output?
It would be easier from an INDEXFILE file, rather than show=y.
Cheers
Nuno Souto
nso...@bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html
James Williams
below.
x38u:oracle>cat ugly_DDL.ksh
#!/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/
# How to do it: Use Oracle imp (import) utility to get the DDL from
# an Oracle exp (export) file. Then filter textfile with this awk.
# The resulting DDL can be used by sqlplus or used to convert
# to a different 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.
# NOTE: Configure which awk to use right after "# Main" below.
# Known Bugs:
# DATE: 1/3/1999
# PROBLEM: Line to long for awk to process
# FIX: Use gawk instead of awk or nawk. Or convert to perl.
# DATE: 1/4/1999
# PROBLEM: arnold....@io-computer.de has shown that for PL/SQL
code blocks
# if there are comments starting with -- , in some - not all -
cases,
# the script joins the following row.
# FIX: None at this time. A work-around would be to edit the output
of the
# PL/SQL code before running it in an interpreter.
#
# 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
#
AWK=/usr/local/bin/gawk; export AWK
#AWK=/usr/bin/awk; export AWK
test $# -eq 1 || usage
cat $1|
get_statements |
join_tokens |
#remove_tablespace |
semicolon_break |
comma_break
Vikas Agnihotri
> On 21 May 2001 20:04:35 -0700, onlyfor...@yahoo.com (Vikas
> Agnihotri) wrote:
>
> >Can someone please point me to some awk/sed scripts to extract DDL
> >from the 'imp full=y show=y' output?
>
> It would be easier from an INDEXFILE file, rather than show=y.
Right, but INDEXFILE contains only CREATE TABLE/INDEX. I need all the
DDLs (create tablespace/user, grants, etc). Only 'exp/imp full=y'
provides this.
Thanks.
Vikas Agnihotri
Oracle 8.1.7 comes with Apache, Perl, etc. So I tried it out...
Heh! The Perl script posted on Metalink is totally broken. It extracts
just the first line of every CREATE statement.
Steven Hauser
awk script for DDL extract: uglyDDL.ksh
--
---------------------------------------------------------
Steven Hauser
email: haus...@tc.umn.edu URL: http://www.sofbot.com/
---------------------------------------------------------
Nuno Souto
Agnihotri) wrote:
>Right, but INDEXFILE contains only CREATE TABLE/INDEX. I need all the
>DDLs (create tablespace/user, grants, etc).
I know you're gonna find this question weird, but:
Can I ask, why?
Connor McDonald
Its useful for re-creating most (but not all) of a database. In any
event, my preference is to run 'strings' on an 'exp rows=n' rather than
the indexfile - it avoids all that nasty stuff with line's wrapping -
each ddl is on a single (long) line.
Its good for adding things like new storage, new tablespaces, NOLOGGING
or PARALLEL etc to index/constraint commands.
CHeers
Connor
--
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Vikas Agnihotri
> On 22 May 2001 07:58:43 -0700, onlyfor...@yahoo.com (Vikas
> Agnihotri) wrote:
>
> >Right, but INDEXFILE contains only CREATE TABLE/INDEX. I need all the
> >DDLs (create tablespace/user, grants, etc).
>
> I know you're gonna find this question weird, but:
>
> Can I ask, why?
Simply because I need the CREATE TABLESPACE, CREATE ROLLBACK SEGMENT
statements. Since I am migrating to LMTs, I want to extract these DDLs
and edit them to remove the DEFAULT STORAGE clause and add the EXTENT
MANAGEMENT LOCAL clause.
(Ok, I really dont need the create role/user, etc. :-)
Vikas
Nuno Souto
Agnihotri) wrote:
>Simply because I need the CREATE TABLESPACE, CREATE ROLLBACK SEGMENT
>statements. Since I am migrating to LMTs, I want to extract these DDLs
>and edit them to remove the DEFAULT STORAGE clause and add the EXTENT
>MANAGEMENT LOCAL clause.
>
>(Ok, I really dont need the create role/user, etc. :-)
Ah! OK, you do NOT need the user stuff. That's better.
I was going to suggest you dump the control file into the log and get
the tablespace creation statements from there with a bit of editing?
Then use the indexfile (which gives onlt tables/indexes) to get the
rest. Without having to fleece through thousands of CREATE USER crud?
But this won't do the constraints if any. You might be better off
using Connor's "strings" method if you are in Unix?
Vikas Agnihotri
>
> Its useful for re-creating most (but not all) of a database. In any
> event, my preference is to run 'strings' on an 'exp rows=n' rather than
> the indexfile - it avoids all that nasty stuff with line's wrapping -
> each ddl is on a single (long) line.
Sigh. Just realized a big problem.
Unfortunately, most of my large indexes are PK indexes and, the worst
part is that they are unnamed i.e. SYS_Cnnnnn.
As a result, they dont show up in the 'imp indexfile=' as CREATE INDEX
statements. Doing a 'imp indexfile=ind.sql constraints=y' DOES make
them show up as 'REM ALTER TABLE ... ADD PRIMARY KEY ....'.
But, when I do the following,
ALTER TABLE ... ADD PRIMARY KEY ... USING INDEX ... NOLOGGING PARALLEL
COMPRESS;
Since I was not sure which of the above 3 clauses was giving the
error, I tried elimination:
PARALLEL: gives me ORA-3001: unimplemented feature.
COMPRESS: gives me ORA-14071: invalid option for an index used to
enforce a constraint
NOLOGGING: OK, this works.
Needless to say, all these 3 options work fine if I pre-create the
index using CREATE UNIQUE INDEX.
So, obviously, creating a PK index via the ALTER TABLE command has
severe limitations as compared to pre-creating it using CREATE UNIQUE
INDEX and then doing a ALTER TABLE ADD PRIMARY KEY which will simply
use the existing index (right?)
Sigh. Question: How can I get my CREATE INDEX statements for my
SYS_Cnnnn indexes into the indexfile?
Once I get past this step and pre-create the indexes, I am assuming
the I can run my current indexfile and Oracle will simply ignore the
USING INDEX clause and use the pre-existing index, right?
Thanks.
Nuno Souto
Agnihotri) wrote:
>
>But, when I do the following,
>ALTER TABLE ... ADD PRIMARY KEY ... USING INDEX ... NOLOGGING PARALLEL
>COMPRESS;
>
>Since I was not sure which of the above 3 clauses was giving the
>error, I tried elimination:
>
>PARALLEL: gives me ORA-3001: unimplemented feature.
>COMPRESS: gives me ORA-14071: invalid option for an index used to
>enforce a constraint
>NOLOGGING: OK, this works.
>
>Needless to say, all these 3 options work fine if I pre-create the
>index using CREATE UNIQUE INDEX.
Ayup! I've heard that if you set PARALLEL DEGREE on the table itself,
the ADD PRIMARY KEY USING INDEX will do a parallel index creation. I
have not been able to make that work in any version up to 8i and
haven't tried yet in that one.
>
>So, obviously, creating a PK index via the ALTER TABLE command has
>severe limitations as compared to pre-creating it using CREATE UNIQUE
>INDEX and then doing a ALTER TABLE ADD PRIMARY KEY which will simply
>use the existing index (right?)
Yup!
>
>Sigh. Question: How can I get my CREATE INDEX statements for my
>SYS_Cnnnn indexes into the indexfile?
>
>Once I get past this step and pre-create the indexes, I am assuming
>the I can run my current indexfile and Oracle will simply ignore the
>USING INDEX clause and use the pre-existing index, right?
>
I think you're trying to do too much through exp/imp. You can use SQL
itself (as I'm sure you are aware) to create the "CREATE INDEX"
strings yourself into a spool file. And the same goes for the ALTER
TABLE ADD PRIMARY KEY. From the dictionary of the source db. Edit the
spool file (or generate it already the right way) to match the target
db.
Wouldn't this be a better option rather than fiddle with exp/imp
variations? Just a suggestion.