Oracle get the Primary and foreign Key Column

获取单个表主键
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

http://stackoverflow.com/questions/9016578/how-to-get-primary-key-column-in-oracle

获取单个表主键外键
SELECT ac.table_name,
        column_name,
        position,
        ac.constraint_name,
        DECODE (constraint_type, 'P', 'Primary Key', 'Foreign Key') key_type,
        (SELECT ac2.table_name
           FROM all_constraints ac2
          WHERE AC2.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME)
           fK_to_table
   FROM all_cons_columns acc, all_constraints ac
  WHERE     acc.constraint_name = ac.constraint_name
        AND acc.table_name = ac.table_name
        AND CONSTRAINT_TYPE IN ('P', 'R')
        AND ac.table_name = --(your table here)
ORDER BY table_name, constraint_type, position;

https://community.oracle.com/thread/2182932

获取所有表主键外键，将NUMBER类型的改成38。
DECLARE
        alter_table_name varchar2(40);
        alter_column_name varchar2(30);
        dtype varchar2(10);
        sql_stmt VARCHAR2(200);
    BEGIN
      FOR TABLE_NAME_RECORDER IN (SELECT table_name  FROM user_tables) LOOP
          FOR L_RECORD IN (SELECT ac.table_name, column_name FROM all_cons_columns acc, all_constraints ac
                  WHERE acc.constraint_name = ac.constraint_name
                  AND acc.table_name = ac.table_name
                  AND CONSTRAINT_TYPE IN ('P', 'R')
                   AND ac.table_name = TABLE_NAME_RECORDER.table_name)
          LOOP
                    --dbms_output.put_line(L_RECORD.column_name);
                   SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE into alter_table_name,alter_column_name,dtype from all_tab_columns WHERE table_name = L_RECORD.table_name AND column_name = L_RECORD.column_name;
                   IF dtype = 'NUMBER' then
                     sql_stmt := 'ALTER table ' || '"' || alter_table_name ||'"' || ' modify (' || alter_column_name ||' NUMBER(38))';
                     dbms_output.put_line(sql_stmt||';');
                     execute immediate sql_stmt;
                   END IF;
           END LOOP;
      END LOOP;
   END;