Oracle get foreign keys

found it!

this is what i was looking for, thanks everybody for helping.

SELECT a.table_name, a.column_name, uc.table_name, uc.column_name                 FROM all_cons_columns a                JOIN all_constraints c ON a.owner = c.owner                    AND a.constraint_name = c.constraint_name                JOIN all_constraints c_pk ON c.r_owner = c_pk.owner                       AND c.r_constraint_name = c_pk.constraint_name                join USER_CONS_COLUMNS uc on uc.constraint_name = c.r_constraint_name                WHERE  C.R_OWNER = 'myschema'

Using @maephisto solution will case a little bug:
If the source tables primary key is a composite key then running the query will result duplicate unnecessary records.

Consider T1 and T2 tables:
Master table T1:

create table T1(  pk1 NUMBER not null,  pk2 NUMBER not null);alter table T1  add constraint T1PK primary key (PK1, PK2);

Detail table T2:

create table T2(  pk1   NUMBER,  pk2   NUMBER,  name1 VARCHAR2(100));alter table T2  add constraint T2FK foreign key (PK1, PK2)  references T1 (PK1, PK2);

The result of the @maephisto query will be:



To over come the problem the query bellow will serve:

SELECT master_table.TABLE_NAME  MASTER_TABLE_NAME,       master_table.column_name MASTER_KEY_COLUMN,       detail_table.TABLE_NAME  DETAIL_TABLE_NAME,       detail_table.column_name DETAIL_COLUMN  FROM user_constraints  constraint_info,       user_cons_columns detail_table,       user_cons_columns master_table WHERE constraint_info.constraint_name = detail_table.constraint_name   AND constraint_info.r_constraint_name = master_table.constraint_name   AND detail_table.POSITION = master_table.POSITION   AND constraint_info.constraint_type = 'R'   AND constraint_info.OWNER = 'MY_SCHEMA'

They are listed in the system view ALL_CONSTRAINTS

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022

Edit

The columns involved in the constraints are listed in ALL_CONS_COLUMNS

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1035.htm#i1575870