#!/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' # # # HISTORY # Revision 1.2 2004-07-21 22:02:00 tangt (TAK TANG) # Minor, unlisted tweaks to do with constraints. # # Revision 1.1 2004-03-06 13:00:00 tangt (TAK TANG) # Initial version # # # 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); } # -----------------------------------------------------------------------------