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')