Re: How to drop all views or tables? remove constraints

From: David Bath <dtb_at_otto>
Date: 4 Nov 92 04:00:01 GMT
Message-ID: <dtb.720849601_at_otto>

Some time ago I wrote a little UNIX script that removes all referential integrity constraints relating to a table so that it could be dropped.

You gave it the table and owner names as arguments.

I posted it to comp.databases.oracle (or tried to).

Here it is again (I hope the serial transfer went OK---) This was written for a dual universe pyramid and oracle 6.0.30

Hope it helps.
If you find it of use, could you let me know.

It should be noted that I have glogin set up to detect if the process is not attached to a terminal, and sets pause off accordingly. If anybody want details about this, let me know via mail and I'll post a summary to the news.

Also - I expand tabs to 4 characters on screen in vi, which may explain why your screen looks a bit funny.

---

David T. Bath

email: 	dtb_at_otto.bf.rmit.oz.au
Work: 	Global Technology Group, Carlton, Vic, OZ  (03) 3477511

"Failure to emulate is the best revenge" - Marcus Aurelius

---

                -----------CUT HERE-------

#!/bin/att /bin/sh


# NAME

# 	rm_rfcon - remove relational constraints

# SYNOPSIS

# 	${ORACLE_HOME}/local/bin/rm_rfcon [-u usr/pw] -o own -t tbl

# DESCRIPTION

# 	rm_rfcon removes referential integrity constraints to the

# 	nominated table in an Oracle database for the nominated user.

# 	This must be done before the table can be dropped.

# BUGS AND WARNINGS

# 	The oracle user must have access to the DBA views or this

# 	just won't work.

# 	No checking for locks on the table are done.

# FILES

# 	${HOME}/rmr$$.sql 	- temporary - deleted


# AUTHOR


#	David T. Bath dtb_at_otto.bf.rmit.oz.au
#	Copyright 1991, Global Technology Group 



# Set up default flags and parameters


    OraUsrId="/" 			# Oracle User Id
    Verbose='' 				# Verbose default
    ErrRtn=0
    ErrMsg=''
    Options='o:u:t:v?' 			# Option arguments
    Usage="Usage: ${0} [-${Options}]"
    . /usr/local/bin/sherror 		# Get error number symbols
					# This just sets up values
					# for Err_OK, Err_EGENERAL
					# and Err_EINVAL equivalent
					# to values in 
					# /usr/include/sys/errno.h
					# Edit these symbols in this
					# script to hard-coded values
					# if you like.

# Parse the arguments

    while getopts ${Options} c
    do

	case $c in
	    u) 	# Identifier of user under Oracle
		OraUsrId="${OPTARG}"
		;;
	    o)  # Table owner
		TblOwner="${OPTARG}"
		;;
	    t)  # Table name
		TblName="${OPTARG}"
		;;
	    v)  # Verbose flag
		Verbose='Y'
		;;
	    \?) # Display usage/help information
		cat <<!EOHELP!

Name: ${0} -- remove referential constraints to a table ${Usage}

    -u 		Oracle user id and password in form username/password
    -o 		Table owner
    -t 		Table name
Returns:
    ${Err_OK} 	No error - ${Entity} exists
    ${Err_EGENERAL} 	General error
    ${Err_EINVAL} 	Invalid argument\n
!EOHELP!
		exit ${Err_EGENERAL}
		;;
	    \*) # Oh, dear, should not have other arguments
		echo "${Usage}" 1>&2
		exit ${Err_EINVAL}
		;;
	esac;

    done
    shift `expr $OPTIND - 1`
    export Verbose TblOwner TblName OraUsrId ErrMsg ErrRtn

# Check that Table owner and table names are specified

    if test -z "${TblOwner}"
    then

	ErrMsg="No table owner specified"
	ErrRtn="${Err_EINVAL}"

    fi
    if test -z "${TblName}"
    then

	ErrMsg="No table name specified"
	ErrRtn="${Err_EINVAL}"

    fi
    if test "${ErrRtn}" -ne "0"
    then

	test "${Verbose}" = "Y" && echo "${0}: ${ErrMsg}" 1>&2
	exit ${ErrRtn}

    fi

    WorkFile="${HOME}/rmr$$.sql"
    export WorkFile

# Do the actual work

    sqlplus -s "${OraUsrId}" > ${WorkFile} <<!EOF! set pagesize 0
set linesize 80
set pause off
set sqlprompt ''
set time off
set feedback off
define C_OWNER=${TblOwner}
define C_TABLE=${TblName}
select

    'alter table "' || A.OWNER || '"."' || A.TABLE_NAME || '"',     'drop constraint "' || A.CONSTRAINT_NAME || '" ;' from

    DBA_CONSTRAINTS A,
    DBA_CONSTRAINTS B
where

    B.OWNER = '&&C_OWNER'
and

    B.TABLE_NAME = '&&C_TABLE'
and

    A.R_OWNER = B.OWNER
and

    A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME and

    A.CONSTRAINT_TYPE = 'R'
;
!EOF!
if test "${Verbose}" = 'Y'
then

    ( echo "set echo on"; cat ${WorkFile} ) | sqlplus ${OraUsrId} else

    ( echo "set echo off"; cat ${WorkFile} ) | sqlplus -s ${OraUsrId} \

        > /dev/null
fi
rm ${WorkFile}

ErrMsg="Completed"
# Do the output and returns

    test "${Verbose}" = "Y" && echo "${0}: ${ErrMsg}" 1>&2     exit ${ErrRtn}

-- 
David T. Bath
email: 	dtb_at_otto.bf.rmit.oz.au
Work: 	Global Technology Corporation, Carlton, Vic, OZ  (03) 3477511
#include <disclaimer.h>

Received on Wed Nov 04 1992 - 05:00:01 CET