I want select all Reference tables

Hi ,
I want to select all the table names which have foreign key relationship with the master tables.Can any one give solution for this?

Thx,
Selva

look dba_constraints, dba_cons_columns

Thanks for your reply.
I Look at dba_constraints, dba_cons_columns.
Can you give query to select all tables which References to master table. For example i have d_sec_account as a master table and a1,a2,a3 ,a4 are references to d_sec_account tables. My query will returen a1,a2,a3,a4 tables name.

Thx,
Selva

Hi ,
As per my knowledge . different naming conventions is usually set for master tables and transaction tables . it depends on the design stratagy .
If naming conventions are not set . then just confirm from your designer if all the tables without foreign key and with primary key are master tables . if YES based on these where clause just query on the above tables .

select c.owner, c.table_name
from dba_constraints c,
dba_constraints m
where c.constraint_type = 'R'
and m.constraint_name = c.r_constraint_name
and m.owner = 'OWNER_OF_MASTER_TABLE'
and m.table_name = 'MASTER_TABLE'

The following procedure uses the following steps to enable or disable all of the FK constraints *TO* a specified table:
Finds the PK of the specified table.
Uses this PK to find all of the FKs that are linked to the PK.
Puts together an ALTER TABLE DISABLE CONSTRAINT command to disable each FK.
Uses dynamic SQL to execute the commands.


PROMPT
PROMPT Creating Procedure able_fk_constraints
CREATE OR REPLACE PROCEDURE able_fk_constraints(
pTable IN VARCHAR2 ,
pAble IN VARCHAR2 )
IS
vPKName VARCHAR2(80);

-- This cursor returns the list of FK constraints linked to the specified
-- PK constraint.
CURSOR curFK(pcPKName IN VARCHAR2) IS
SELECT constraint_name, table_name
FROM user_constraints
WHERE r_constraint_name = pcPKName;

-- These two variables are used for the dynamic SQL
nDDLCursor INTEGER;
nDDLReturn INTEGER;
BEGIN
/*******************************************************************************/
/* ABLE_FK_CONSTRAINTS - This procedure easily enables/disables FK constraints */
/* pointing TO the specified table. */
/* */
/* Parameters: pTable - The name of the table to dis/enable FK */
/* constraints to. */
/* pAble - One of: DISABLE or ENABLE */
/*******************************************************************************/

-- Get the name of the PK constraint for the specified table.
BEGIN
SELECT constraint_name INTO vPKName
FROM user_constraints
WHERE table_name = pTable
AND constraint_type = 'P';
END;


-- Now get the FK constraints linked to the PK constraint of the specified table.
FOR fk IN curFK(vPKName) LOOP

-- Use dynamic SQL to execute the ALTER TABLE command and dis/enable the constraint
nDDLCursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDLCursor,'ALTER TABLE '||fk.table_name||' '
||pAble||' CONSTRAINT '||fk.constraint_name, 1);
nDDLReturn := dbms_sql.execute(nDDLCursor);
dbms_sql.close_cursor(nDDLCursor);
END LOOP;
END ABLE_FK_CONSTRAINTS;
/


An example of using the script:
execute able_fk_constraints('MYTABLE','DISABLE');
truncate table mytable;
@load_mytable
execute able_fk_constraints('MYTABLE','ENABLE');