Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test1 (id_test1 number
                      constraint pk_test1
                        primary key using index);
create table test2 (id_test2 number
                      constraint pk_test2
                        primary key using index,
                    id_test1 number references test1(id_test1));
SELECT *
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'TEST2'
  ORDER BY COLUMN_ID;


TABLE_NAME
COLUMN_NAME
DATA_TYPE
DATA_TYPE_MOD
DATA_TYPE_OWNER
DATA_LENGTH
DATA_PRECISION
DATA_SCALE
NULLABLE
COLUMN_ID
DEFAULT_LENGTH
DATA_DEFAULT
NUM_DISTINCT
LOW_VALUE
HIGH_VALUE
DENSITY
NUM_NULLS
NUM_BUCKETS
LAST_ANALYZED
SAMPLE_SIZE
CHARACTER_SET_NAME
CHAR_COL_DECL_LENGTH
GLOBAL_STATS
USER_STATS
AVG_COL_LEN
CHAR_LENGTH
CHAR_USED
V80_FMT_IMAGE
DATA_UPGRADED
HISTOGRAM


TEST2
ID_TEST2
NUMBER
null
null
22
null
null
N
1
null
null
null
0x
0x
null
null
null
null
null
null
null
NO
NO
null
0
null
NO
YES
NONE

TEST2
ID_TEST1
NUMBER
null
null
22
null
null
Y
2
null
null
null
0x
0x
null
null
null
null
null
null
null
NO
NO
null
0
null
NO
YES
NONE


WITH cteIndexes AS (select ui.TABLE_NAME, ui.INDEX_NAME, ui.INDEX_TYPE, ui.UNIQUENESS,
                           ic.COLUMN_NAME, ic.COLUMN_POSITION
                      from user_indexes ui
                      INNER JOIN USER_IND_COLUMNS ic
                        ON ic.TABLE_NAME = ui.TABLE_NAME
                      WHERE ui.uniqueness = 'UNIQUE'),
     cteCandidate_keys AS (SELECT c.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_ID
                             FROM cteIndexes i
                             INNER JOIN USER_TAB_COLUMNS c
                               ON c.COLUMN_NAME = i.COLUMN_NAME
                             WHERE c.TABLE_NAME <> i.TABLE_NAME
                             ORDER BY c.TABLE_NAME, c.COLUMN_ID)
SELECT 'UNIQUE KEYS' AS TABLE_NAME, NULL AS COLUMN_NAME,
       NULL AS COLUMN_POSITION, NULL AS COLUMN_ID
  FROM DUAL
UNION ALL
SELECT TABLE_NAME, COLUMN_NAME, NULL, NULL
  FROM cteIndexes
UNION ALL
SELECT NULL, NULL, NULL, NULL FROM DUAL
UNION ALL
SELECT 'CANDIDATE FOREIGN KEYS', NULL, NULL, NULL FROM DUAL
UNION ALL
SELECT TABLE_NAME, COLUMN_NAME, NULL, COLUMN_ID
  FROM cteCandidate_keys


TABLE_NAME
COLUMN_NAME
COLUMN_POSITION
COLUMN_ID


UNIQUE KEYS
null
null
null

TEST1
ID_TEST1
null
null

TEST2
ID_TEST2
null
null

null
null
null
null

CANDIDATE FOREIGN KEYS
null
null
null

TEST2
ID_TEST1
null
2

TABLE_NAME	COLUMN_NAME	COLUMN_POSITION	COLUMN_ID
UNIQUE KEYS	null	null	null
TEST1	ID_TEST1	null	null
TEST2	ID_TEST2	null	null
null	null	null	null
CANDIDATE FOREIGN KEYS	null	null	null
TEST2	ID_TEST1	null	2

TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_TYPE_MOD	DATA_TYPE_OWNER	DATA_LENGTH	DATA_PRECISION	DATA_SCALE	NULLABLE	COLUMN_ID	DEFAULT_LENGTH	DATA_DEFAULT	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	DENSITY	NUM_NULLS	NUM_BUCKETS	LAST_ANALYZED	SAMPLE_SIZE	CHARACTER_SET_NAME	CHAR_COL_DECL_LENGTH	GLOBAL_STATS	USER_STATS	AVG_COL_LEN	CHAR_LENGTH	CHAR_USED	V80_FMT_IMAGE	DATA_UPGRADED	HISTOGRAM
TEST2	ID_TEST2	NUMBER	null	null	22	null	null	N	1	null	null	null	0x	0x	null	null	null	null	null	null	null	NO	NO	null	0	null	NO	YES	NONE
TEST2	ID_TEST1	NUMBER	null	null	22	null	null	Y	2	null	null	null	0x	0x	null	null	null	null	null	null	null	NO	NO	null	0	null	NO	YES	NONE