您可以按以下方式使用条件聚合：

SELECT OWNER, TABLE_NAME, COUNT(CASE WHEN COLUMN_NAME LIKE '%ID%' THEN COLUMN_NAME END) as ID_COUNT,
  COUNT(CASE WHEN COLUMN_NAME LIKE '%NUM%' THEN COLUMN_NAME END) NUM_COUNT
FROM all_tab_cols
GROUP BY OWNER, TABLE_NAME 
HAVING COUNT(CASE WHEN COLUMN_NAME LIKE '%ID%' THEN COLUMN_NAME END)>=1 AND
       COUNT(CASE WHEN COLUMN_NAME LIKE '%NUM%' THEN COLUMN_NAME END)>=1
ORDER BY OWNER, TABLE_NAME ;

请参见demo。
如果你想选择包含两个列名的表，one like ID和 one like NUM，你可以在having子句中用=1替换〉=1。

如果我理解正确的话，您希望返回包含两个（或更多）列的表，这些列的名称包含ID和NUM（sub）字符串。
我的all_tab_colsCTE * 模仿了 * 数据字典视图，只是为了说明问题。

• EMP表包含3个具有ID（sub）字符串的列，但它应计为1（而不是3）;此外，由于该表不包含名称中包含NUM（sub）字符串的任何列，因此EMP表不应该是结果集的一部分
• DEP表包含一个ID和一个NUM列，因此应返回该表

因此：TEMP CTE计算ID和NUM列的数量（重复项被忽略）。最终查询期望表包含这两列。
样本数据：

SQL> with all_tab_cols (table_name, column_name) as
  2    (select 'EMP', 'ID_EMP' from dual union all
  3     select 'EMP', 'ID_MGR' from dual union all
  4     select 'EMP', 'SAL'    from dual union all
  5     select 'EMP', 'DID_ID'  from dual union all
  6     --
  7     select 'DEP', 'ID_DEP' from dual union all
  8     select 'DEP', 'DNUM'   from dual union all
  9     select 'DEP', 'LOC'    from dual
 10    ),

查询从这里开始：

11  temp as
 12    (select table_name, column_name,
 13       sum(case when regexp_count(column_name, 'ID') = 0 then 0
 14                when regexp_count(column_name, 'ID') >= 1 then 1
 15           end) cnt_id,
 16       sum(case when regexp_count(column_name, 'NUM') = 0 then 0
 17                when regexp_count(column_name, 'NUM') >= 1 then 1
 18           end) cnt_num
 19     from all_tab_cols
 20     group by table_name, column_name
 21    )
 22  select table_name
 23  from temp
 24  group by table_name
 25  having sum(cnt_id) = sum(cnt_num)
 26     and sum(cnt_id) = 1;

TABLE_NAME
--------------------
DEP

SQL>

你可以在一个子查询上执行一个UNION ALL，然后执行一个GroupBy和一个Count，通过将你的查询分成不同的结果集来确定你想要的表，一个基于ID，另一个基于NUM：

SELECT *
FROM
(
    SELECT OWNER, TABLE_NAME
    FROM all_tab_cols 
    WHERE column_name LIKE '%ID%'
    GROUP BY OWNER, TABLE_NAME
    UNION ALL
    SELECT OWNER, TABLE_NAME 
    FROM all_tab_cols 
    WHERE column_name LIKE '%NUM%'
    GROUP BY OWNER, TABLE_NAME
) x
GROUP BY x.OWNER, x.TABLE_NAME 
HAVING COUNT(x.TABLE_NAME) >= 2 
ORDER BY x.OWNER, x.TABLE_NAME ;

使函数易于重用：

CREATE OR REPLACE FUNCTION get_user_tables_with_collist( i_collist IN VARCHAR2 )
RETURN SYS.ODCIVARCHAR2LIST
AS
    w_result    SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  w_re VARCHAR2(64) := '[^,;./+=*\.\?%[:space:]-]+' ;
BEGIN
  WITH collist(colname) AS (
    SELECT REGEXP_SUBSTR( UPPER(i_collist), w_re, 1, LEVEL ) FROM DUAL
    CONNECT BY REGEXP_SUBSTR( UPPER(i_collist), w_re, 1, LEVEL ) IS NOT NULL
  )
  SELECT table_name BULK COLLECT INTO w_result FROM (
    SELECT table_name, COUNT(column_name) AS n FROM user_tab_columns 
    WHERE EXISTS(
      SELECT 1 FROM collist 
      WHERE colname = column_name
    )
    GROUP BY table_name
  ) d
  WHERE d.n = (SELECT COUNT(*) FROM collist)
  ;
    RETURN w_result;
END ;
/

CREATE OR REPLACE FUNCTION get_all_tables_with_collist( i_owner IN VARCHAR2, i_collist IN VARCHAR2 )
RETURN SYS.ODCIVARCHAR2LIST
AS
    w_result    SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  w_re VARCHAR2(64) := '[^,;./+=*\.\?%[:space:]-]+' ;
BEGIN
    WITH collist(colname) AS (
      SELECT REGEXP_SUBSTR( UPPER(i_collist), w_re, 1, LEVEL ) FROM DUAL
      CONNECT BY REGEXP_SUBSTR( UPPER(i_collist), w_re, 1, LEVEL ) IS NOT NULL
    )
    SELECT table_name BULK COLLECT INTO w_result FROM (
      SELECT table_name, COUNT(column_name) AS n FROM all_tab_columns 
      WHERE EXISTS(
        SELECT 1 FROM collist 
        WHERE colname = column_name
      )
      AND owner = UPPER(i_owner) 
      GROUP BY table_name
    ) d
    WHERE d.n = (SELECT COUNT(*) FROM collist)
  ;
  
    RETURN w_result;
END ;
/

select * from get_all_tables_with_collist('sys', 'table_name;column_name') ;

ALL_COL_COMMENTS
ALL_COL_PENDING_STATS
ALL_COL_PRIVS
...

这基本上是对Littlefoot的回答的“编辑”，我相信这会使事情变得更好。我给予应有的信任，但我被要求将其作为一个单独的回答，所以我这样做了。

11  temp as -- USE WITH IF not using the data part above
 12    (select table_name, column_name,
 13       sum(case when regexp_count(column_name, 'ID') = 0 then 0
 14                when regexp_count(column_name, 'ID') >= 1 then 1
 15           end) cnt_id,
 16       sum(case when regexp_count(column_name, 'NUM') = 0 then 0
 17                when regexp_count(column_name, 'NUM') >= 1 then 1
 18           end) cnt_num
 19     from all_tab_cols
 20     group by table_name, column_name
 21    )
 22  select table_name
 23  from temp
 24  group by table_name
 25  having sum(cnt_id) >= 1
 26     and sum(cnt_num) >= 1;

这是Ahmed使用条件聚合的答案的变体。我只是更新了它以使用变量。这在Toad中有效。它可能不适用于其他Oracle系统。
我认为p3consulting也给出了一个很好的答案，但下面的代码更短，更容易阅读（在我看来）。
有关我如何在Toad中添加变量的信息，请参阅Alan的回答：How do I declare and use variables in PL/SQL like I do in T-SQL?此外，要使用脚本变量，请在Toad中使用“作为脚本运行”，否则，将输入变量，这对我来说不是很可取。

var searchVal1 varchar2(20);
var searchVal2 varchar2(20);

exec :searchVal1 := '%ID%';
exec :searchVal2 := '%NUM%';

SELECT OWNER, TABLE_NAME 
 , COUNT(CASE WHEN COLUMN_NAME LIKE :searchVal1 THEN COLUMN_NAME END) as COUNT_1,
   COUNT(CASE WHEN COLUMN_NAME LIKE :searchVal2 THEN COLUMN_NAME END) as COUNT_2 
FROM all_tab_cols
GROUP BY OWNER, TABLE_NAME 
HAVING COUNT(CASE WHEN COLUMN_NAME LIKE :searchVal1 THEN COLUMN_NAME END)>=1 AND  
       COUNT(CASE WHEN COLUMN_NAME LIKE :searchVal2 THEN COLUMN_NAME END)>=1 
ORDER BY OWNER, TABLE_NAME ;