From: oratune@aol.com
Newsgroups: comp.databases.oracle.server
Subject: Re: Foreign keys.
Date: Tue, 10 Oct 2000 23:39:45 GMT
Organization: Deja.com - Before you buy.
Lines: 70
Message-ID: <8s09c0$195$1@nnrp1.deja.com>
References: <8rvob0$i50$1@nnrp1.deja.com> <971214678.2789.3.pluto.d4ee154e@news.demon.nl> <9hNE5.5747$mC.389522@monger.newsread.com>
NNTP-Posting-Host: 38.193.42.5
X-Article-Creation-Date: Tue Oct 10 23:39:45 2000 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
X-Http-Proxy: 1.1 x55.deja.com:80 (Squid/1.1.22) for client 38.193.42.5
X-MyDeja-Info: XMYDJUIDddf_dba


Excellent script but it won't do much good if the user isn't granted
DBA.  IMHO it's better to use USER_CONSTRAINTS.


In article <9hNE5.5747$mC.389522@monger.newsread.com>,
  "Van Messner" <vmessner@bestweb.net> wrote:
> ACCEPT tname PROMPT 'Enter Name of Table You Want to Look at:'
> ACCEPT qowner PROMPT 'Enter Name of Table Owner:'
> column fowner heading 'Owner Of |Table With|Foreign Key' format a11
> column ftable heading ' Name Of | Table With| Foreign Key' format a16
> column fconname heading '      FK Name ' format a22
> column fdel heading 'Delete|Rule' format a10
> column fref heading 'Constraint in|Parent Table|Pointed to by FK'
 format a22
> column fstatus heading 'FK Status' format a8
> TTITLE ' FOREIGN KEYS THAT POINT TO THIS TABLE '
> SELECT
>   frn.owner fowner,
>   frn.table_name ftable,
>   frn.constraint_name fconname,
>   frn.delete_rule fdel,
>   frn.r_constraint_name fref,
>   frn.status fstatus
> FROM
>   dba_constraints par,
>   dba_constraints frn
> WHERE
>   par.table_name = UPPER('&tname')
>   and par.owner = UPPER('&qowner')
>   and par.constraint_name = frn.r_constraint_name
> ;
>
> "Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message
> news:971214678.2789.3.pluto.d4ee154e@news.demon.nl...
> > select r.table_name, r.constraint_name
> > from user_constraints c, user_constraints r
> > where c.table_name = '<your table>'
> > and   c.constraint_type='P'
> > and   r.referenced_name=c.constraint_name
> > and   r.referenced_owner=c.owner
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >
> > <vdolt@my-deja.com> wrote in message news:8rvob0$i50
 $1@nnrp1.deja.com...
> > > I am trying to delete a record from the table. Other tables have
> > > foreign keys on that table, which prevents me from deleting
 original
> > > record. Is there a query I can run to uncover all the foreign key
 on
> > > that table?
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.