Phil's Notes

Finding the Unique Column in an Oracle Table

sqlpl/sqloracle

If you ever find yourself looking for the unique column of a table in an Oracle database, but don’t see a primary key assigned and you don’t want to run a query against all the records in the table because it is too large, you can try querying against DBA_TABLES or ALL_TABLES to get the number of rows in that table.

SELECT NUM_ROWS
FROM DBA_TABLES --you can also use ALL_TABLES
WHERE TABLE_NAME = 'your_table_name';

Once you have the number of rows, query DBA_TAB_COLUMNS or ALL_TAB_COLUMNS to see which column has the same distinct number of rows (NUM_DISTINCT) as the NUM_ROWS from the previous query.

SELECT COLUMN_NAME, NUM_DISTINCT
FROM DBA_TAB_COLUMNS --you can also use ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'your_table_name'
ORDER BY NUM_DISTINCT DESC;

Source: https://stackoverflow.com/a/4010826/991383