Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> grep and awk Demo to Drop Foreign Keys

grep and awk Demo to Drop Foreign Keys

From: Dan Bikle <bikle_at_bikle.com>
Date: 17 Dec 2001 21:08:56 -0800
Message-ID: <1fc668d.0112172108.52524d41@posting.google.com>


fkDemo.html
12-05-2001 Bikle


grep and awk Demo to Drop Foreign Keys

This file describes a simple technique for dropping foreign keys for a given set of tables.

Suppose I'm given a list of tables which I display below:

AGENT
AGENT_JOB_ORDER
COMPETENCY_REQ
DAS_ID_GENERATOR
EDUCATION_REQ
EXPERIENCE_REQ
JOB_ORDER
JOB_POST_STATUS
ORGANIZATION
ORG_CONTACT
RELATIONSHIP
RELATIONSHIP_EVENT
RELATIONSHIP_EVENT_ATTR
SOURCE I will take the above list and supply it to an exp command line:

#! /bin/sh

# someScottTables.exp.sh

# Simple export script

# 12-05-2001 Bikle

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

${ORACLE_HOME}/bin/exp SCOTT/TIGER ROWS=N FILE=someScottTables.nr.dmp \
TABLES=\
AGENT\
,AGENT_JOB_ORDER\
,COMPETENCY_REQ\
,DAS_ID_GENERATOR\
,EDUCATION_REQ\
,EXPERIENCE_REQ\
,JOB_ORDER\
,JOB_POST_STATUS\
,ORGANIZATION\
,ORG_CONTACT\
,RELATIONSHIP\
,RELATIONSHIP_EVENT\
,RELATIONSHIP_EVENT_ATTR\
,SOURCE
exit

Next, I may want to inspect some actual SQL which could be used to recreate the constraints.

We may find the simple shell command below to be useful: /bin/grep 'ALTER TABLE' someGURUtables.nr.dmp|/bin/grep FOREIGN > fk.txt

(On Linux, grep does not work well with .dmp files. We use the 'strings' shell
command to work around this:
/bin/strings someGURUtables.nr.dmp|/bin/grep 'ALTER TABLE' | /bin/grep FOREIGN > fk.txt
Actually the above shell command works fine on Solaris also)

A simple awk command may be used to transform the above file into a series of SQL statements which could be used to drop constraints:

cat fk.txt|/bin/awk '{print $1 " " $2 " " $3 " DROP " $5 " " $6 ";"}' > dropFK.sql

That's about it.

I have more demos on my site.

-Dan



Daniel B. Bikle/Independent Oracle Consultant bikle_at_bikle.com | 650/941-6276 | P.O. BOX AG LOS ALTOS CA 94023 http://www.bikle.com
Received on Mon Dec 17 2001 - 23:08:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US