In Oracle Database, there are a number of views that we can query to find out whether a table exists.

Example

For example, USER_TABLES describes the relational tables owned by the current user.

SELECT TABLE_NAME 
FROM USER_TABLES 
WHERE TABLE_NAME = 'COUNTRIES';

Result:

COUNTRIES

In this example, I checked to see if there’s a relational table called COUNTRIES that is owned by the current user.

We could adjust this query to only return the count:

SELECT COUNT(TABLE_NAME)
FROM USER_TABLES 
WHERE TABLE_NAME = 'COUNTRIES';

Result:

1

In that case, the table name exists and so the count is 1.

Here’s what happens when the table doesn’t exist:

SELECT COUNT(TABLE_NAME)
FROM USER_TABLES 
WHERE TABLE_NAME = 'USERS';

Result:

0

The following table lists other views that contain information about tables in Oracle Database.

View	Description
USER_TABLES	Describes the relational tables owned by the current user (this view is used in the above example).
ALL_TABLES	Describes the relational tables accessible to the current user.
DBA_TABLES	Describes all relational tables in the database.
USER_ALL_TABLES	Describes the object tables and relational tables owned by the current user.
DBA_ALL_TABLES	Describes all object tables and relational tables in the database.
ALL_ALL_TABLES	Describes the object tables and relational tables accessible to the current user.