select all tables from dba_table except list

Question

I'm missing something in my query. I want to select all tables from dba_tables except a list of tables.

For example, show all except HR.DEPT, SCHEMA.TAB

dba_tables:

OWNER    TABLE_NAME
------   ----------
HR       DEPT
HR       TEST
SCHEMA   TAB
SCHEMA   NAMES
TEM      TBA

Result after query:

OWNER    TABLE_NAME
------   ----------
HR       TEST
SCHEMA   NAMES
TEM      TBA

Query I tried:

select owner, table_name from dba_tables
where (owner != 'HR' and table_name != 'DEPT' )  OR (owner != 'SCHEMA' and table_name != 'TAB' )

Answer 1

select owner, table_name from dba_tables
where (owner, table_name) not in (('HR', 'DEPT'), ('SCHEMA', 'TAB'));

Answer 2

The problem is your OR. When the table is DEPT of the HR schema, the first AND condition is false, but the 2nd AND condition is true, thus the OR results as true and no row is filtered.

Try with NOT + AND:

select 
    owner, 
    table_name 
from 
    dba_tables
where 
    NOT (owner = 'HR' and table_name = 'DEPT') AND 
    NOT (owner = 'SCHEMA' and table_name = 'TAB')