Sure, you can join all_tables to any other table/view:
create table t as
select * from dba_objects;
select at.owner, count(*)
from all_tables at
join t
on t.object_name = at.table_name
and t.object_type = 'TABLE'
group by at.owner;
OWNER COUNT(*)
APEX_040200 452
MDSYS 130
CHRIS 78
OUTLN 6
CTXSYS 53
OLAPSYS 2
FLOWS_FILES 1
HR 9
SYSTEM 182
DVSYS 36
SCOTT 4
DBSNMP 20
GSMADMIN_INTERNAL 19
OJVMSYS 6
ORDSYS 5
OE 11
PM 2
SH 19
APPQOSSYS 4
XDB 29
ORDDATA 90
IX 8
SYS 1256
WMSYS 40
LBACSYS 22
Though I suspect that's not what you're trying to do...
In your query:
select *
from table1 a
where a.columns in (SELECT DISTINCT
A.COLUMN_NAME
FROM SYS.ALL_TAB_COLUMNS A
WHERE A.OWNER = 'ME'
AND A.TABLE_NAME = 'Table1'
AND A.COLUMN_NAME LIKE '%CONF%')
You're getting the list of column names in me.table1 like '%CONF%'. Then returning the rows in table1 that store these values in table1.columns. Not the columns with these
names.
If you want to join based on the name of the column, you'll need dynamic SQL.