Proracle’s Weblog

November 26, 2007

Indexfile beautifier

Filed under: useful — proracle @ 8:04 am
Tags:
#!/usr/bin/awk -f
# -----------------------------------------------------------------------------
# NAME
#   beautify_indexfile.awk
#
#
# DESCRIPTION
#   If you have used 'imp INDEXFILE=' to extract the SQL from an export file,
#   you can use this script to seperate the create table and create index
#   commands into different files.
#
#   It also does some formatting to make it easier to do search/replace, such
#   as putting the STORAGE clause on a single line.
#
#   This version works with Oracle 9 index files.
#
#
# USAGE
#   awk -f beautify_indexfile.awk INDEXFILE
#   (produces files 'create_table.sql'; 'create_index.sql' and 'create_trash.sql'
#
#
# WARNING
#   It makes some assumptions about the format of the output file, but since
#   the file is generated by imp, I guess its not too much of a problem.
#
#
# WARNING
#   I have not tested it with Oracle 8+ features, such as IOTs.  Come to think
#   of it, I haven't tested it with clusters either.  Oh well, you've got the
#   source.  Modify it until it works.
#
#
# TIPS
#   1) If you have a POSIX compatible shell, you can run this script without
#      having to specify 'awk -f'.
#
#      First, make this file executable; change the very first like to a
#      hash '#', followed by an exclamation mark '!', followed by the path
#      to 'awk' on your system, followed by a space, followed by '-f'.
#
#      Then you can run this script like this:-
#         beautify_indexfile.awk INDEXFILE
#
#      Oh, you might want to rename this script to something shorter, such as 'pif'
#
#
# COPYRIGHT
#   Er, its free.  You reap what you sow.  Share and enjoy.
# -----------------------------------------------------------------------------BEGIN {
index_file="create_index.sql";
print "REM Generated automatically" >index_file;
print "SET TRIMSPOOL ON TIMING ON"  >index_file;
print "ALTER SESSION SET SORT_AREA_SIZE=102400000;" >index_file;
print "SPOOL create_index.log"      >index_file;
print ""                            >index_file;
table_file="create_table.sql";
print "REM Generated automatically" >table_file;
print "SET TRIMSPOOL ON TIMING ON"  >table_file;
print "SPOOL create_table.log"      >table_file;
print ""                            >table_file;
trash_file="create_trash.sql";
print "REM Generated automatically" >trash_file;
current_file=trash_file;
data="";
}
# -----------------------------------------------------------------------------
/^REM  / { sub("^REM  ", ""); }
/^CREATE INDEX/  { spill(); current_file = index_file; }
/^CREATE UNIQUE/ { spill(); current_file = index_file; }
/^ALTER TABLE/   { spill(); current_file = index_file; }
/^CREATE TABLE/  { spill(); current_file = table_file; }
{ append_line(); }
/;$/ { spill(); current_file = trash_file; }
# -----------------------------------------------------------------------------
function append_line()
{
data=data $0 " ";
}
# -----------------------------------------------------------------------------
function fold_before( t )
{
i=match(data," " t);
if (i>0)
{
printf("%s\n",substr(data,1,i-1)) >current_file;
data=substr(data,i+1);
}
}
# -----------------------------------------------------------------------------
function spill()
{
gsub("  ", " ",data);  # compress multiple spaces to single space
sub(" $","",data);     # remove trailing space
if (""==data) return;
gsub(" ,", ",",data);  # remove spaces before commas
fold_before("ADD CONSTRAINT");
fold_before("FOREIGN KEY");
fold_before("REFERENCES");
fold_before("ON");
fold_before("PRIMARY KEY");
fold_before("USING INDEX");
i=match(data,"\" [(]\".*PCTFREE");
if (i>0)
{
printf("%s\n(\n",substr(data,1,i)) >current_file;
data=substr(data,i+3);
while (i=match(data,", \""))
{
printf("  %s\n",substr(data,1,i)) >current_file;
data=substr(data,i+2);
}
i=match(data,") PCTFREE");
if (i>0)
{
printf("  %s\n",substr(data,1,i-1)) >current_file;
data=substr(data,i);
}
}
fold_before("PCTFREE");
fold_before("STORAGE");
fold_before("TABLESPACE");
fold_before("DISABLE");
sub(" ;",";",data);    # remove space before semicolon
printf("%s\n\n",data) >current_file;
data="";
}
# -----------------------------------------------------------------------------
END {
print "SPOOL OFF" >index_file;
print "SPOOL OFF" >table_file;
close(index_file);
close(table_file);
close(trash_file);
}
# -----------------------------------------------------------------------------
Next Page »

Create a free website or blog at WordPress.com.