RE: references trace

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Thu, 3 Mar 2011 11:03:29 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F682890AA_at_AAPQMAILBX02V.proque.st>

You should take a look at DBA_CONSTRAINTS and DBA_CONS_COLUMNS.

Look for constraints with CONSTRAINT_TYPE='R'.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of jose soares Sent: Thursday, March 03, 2011 10:57 AM
To: ORACLE-L
Subject: references trace

Hi all,

I'm trying to trace all references to a table.

I have a table, like this one:

create table mytable(

    id int primary key,
    description varchar2(200)
    version int);

I would like to know which tables have a reference to mytable.

in PostgreSQL I can do that in this way:

SELECT c.relname, r.conname
  FROM pg_catalog.pg_constraint AS r, pg_catalog.pg_class AS c   WHERE r.contype = 'f'
  AND r.conrelid = c.oid
  AND conname like '%_mytable_fkey'
  ORDER BY 1

        relname         |                    conname


------------------------+------------------------------------------------

 abbattimento_ordinanza | abbattimento_ordinanza_id_mytable_fkey
 autorizzazione         | autorizzazione_id_mytable_fkey
 capo                   | capo_id_mytable_fkey
 capo_rubato            | capo_rubato_id_mytable_fkey
 controllo_capo         | controllo_capo_id_mytable_fkey
 figura_aziendale       | figura_aziendale_id_mytable_fkey
 gestione_doc_file      | gestione_doc_file_id_mytable_fkey
 marca_ristampata       | marca_ristampata_id_mytable_fkey
 movimento              | movimento_id_mytable_fkey
 passaporto             | passaporto_id_mytable_fkey
 pratica                | pratica_id_mytable_fkey
 prestazione            | prestazione_id_mytable_fkey
 produzione             | produzione_id_mytable_fkey
 ricetta                | ricetta_id_mytable_fkey
 scadenza_malattia      | scadenza_malattia_id_mytable_fkey
 scadenziario           | scadenziario_id_mytable_fkey
 scheda                 | scheda_id_mytable_fkey
 scheda_totali          | scheda_totali_id_mytable_fkey
 sopralluogo            | sopralluogo_id_mytable_fkey
 trasporto              | trasporto_id_mytable_fkey
 versamento             | versamento_id_mytable_fkey

(21 rows)

Is there a way to do it on Oracle?

thanks for any help,

j

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 03 2011 - 10:03:29 CST