Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> grep and awk Demo to Drop Foreign Keys
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