| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: alter table disable constraint
Sorry -hit the wrong button!
The variant I was talking about is...
select pk.owner, pk.constraint_name, pk.constraint_type,
pk.table_name, fk.owner, fk.constraint_name,
fk.constraint_type, fk.table_name
from dba_constraints pk, dba_constraints fk
where pk.constraint_name = fk.r_constraint_name(+)
and pk.table_name in (select table_name from
dba_indexes where tablespace_name='INDX')
and pk.constraint_type='P';
...and obviously, you replace the 'BLAH' there with whatever name you've actually got for your Indexes tablespace.
The syntax rather relies on you having correctly placed indexes on all your foreign keys (which you should do anyway, otherwise a small bit of DML on the parent table totally locks the entire child table from all DML).
Regards
HJR
"Howard J. Rogers" <howardjr_at_www.com> wrote in message
news:F02g6.135$305.48521_at_inet16.us.oracle.com...
> Here's the syntax I was thinking of. Let me know if it does what you are
> after, or if I have to find something else:
>
> select pk.owner, pk.constraint_name, pk.constraint_type,
> pk.table_name, fk.owner, fk.constraint_name,
> fk.constraint_type, fk.table_name
> from dba_constraints pk, dba_constraints fk
> where pk.constraint_name = fk.r_constraint_name(+)
> and pk.constraint_type='P';
>
> It lists all Primary Key constraints, and shows any which have foreign key
> relations.
>
> The list could potentially be huge, so you might want this variant:
>
>
>
>
> "Naushi Hussain" <naushi.hussain_at_alliedsignal.com> wrote in message
> news:3A8055C7.714D0E4E_at_alliedsignal.com...
> > you help will be greatly appreciated
> >
> > "Howard J. Rogers" wrote:
> >
> > > "Naushi Hussain" <naushi.hussain_at_alliedsignal.com> wrote in message
> > > news:3A802540.F4FDA1A1_at_alliedsignal.com...
> > > > 1) I did an alter table disable primary key cascade and it worked.
Now
> > > > I like to enable it. Will enabling automatically recreate the FK
> > > > constraint?
> > > >
> > >
> > > No. There's no 'enable constraint cascade' syntax.
> > >
> > > > 2) Also I tried looking in dba_constraints table for the table this
key
> > > > is a FK to. But I could not find the related table_name even in the
> > > > R_CONSTRAINT_NAME. How can I find the name of the (child) table
where
> > > > the primary key of a (parent) table is a foreign key.
> > > >
> > >
> > > There's some fairly standard syntax that covers this -and I don't have
it to
> > > hand. If you've been on the Backup and Recovery course, it's included
in
> > > the Oracle course notes for that. If no-one's posted anything else by
this
> > > evening, I'll have it for you then (it's currently 6am-ish).
> > >
> > > Regards
> > > HJR
> > >
> > > > will appreciate any suggestions
> > > >
> >
>
>
Received on Tue Feb 06 2001 - 19:59:10 CST
 |
 |