Oracle SQL: How do I find the table name given the column names?

Question

If I know the names of every column of a table but not the name of the table, how do I find the name of the table I need?

Answer 1

Based on @Roobie's solution, the code below searches in all schemas you have access to, in case the table is not in your own schema. Also added case-insensitive matching.

SELECT owner, table_name
  FROM all_tab_columns
  WHERE UPPER(column_name) = UPPER('MYCOL');

Answer 2

Try this (one known column):

CREATE TABLE mytab(mycol VARCHAR2(30 CHAR));

SELECT table_name FROM user_tab_columns WHERE column_name='MYCOL';

Note MYCOL is in upper case in column_name='MYCOL';

Cheers!