How to List Tables in Oracle

Galaxy Glossary

How do I list all tables in Oracle?

Query Oracle data dictionary views (USER_TABLES, ALL_TABLES, DBA_TABLES) to return table names and metadata.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

How do I list tables in my current schema?

Run SELECT table_name FROM user_tables ORDER BY table_name;. USER_TABLES shows only objects owned by the logged-in user, so no extra filters are needed.

How do I list tables in another schema?

Use ALL_TABLES. Filter the OWNER column: SELECT table_name FROM all_tables WHERE owner = 'ECOMMERCE' ORDER BY table_name;.Replace ECOMMERCE with the target schema in uppercase.

How do I list every table in the database?

Query DBA_TABLES if you have DBA privileges: SELECT owner, table_name FROM dba_tables ORDER BY owner, table_name;. This view exposes all tables across all schemas.

How do I filter tables by name pattern?

Add a LIKE condition: SELECT table_name FROM user_tables WHERE table_name LIKE 'ORD%';.This returns ORDER-related tables such as ORDERS and ORDERITEMS.

How do I see extra table properties?

Select extra columns such as NUM_ROWS, LAST_ANALYZED, and TABLESPACE_NAME from the same view: SELECT table_name, num_rows, tablespace_name FROM user_tables;.

What are best practices when listing tables?

Use USER_TABLES for routine work, ALL_TABLES for cross-schema joins, and DBA_TABLES only when required. Always filter OWNER and use uppercase to avoid case-sensitivity surprises.

Why How to List Tables in Oracle is important

How to List Tables in Oracle Example Usage


-- List all ecommerce tables that store orders or customers
SELECT table_name
FROM all_tables
WHERE owner = 'ECOMMERCE'
  AND table_name IN ('CUSTOMERS', 'ORDERS', 'ORDERITEMS');

How to List Tables in Oracle Syntax


-- Current schema
SELECT table_name
FROM user_tables
ORDER BY table_name;

-- Specific schema (e.g., ECOMMERCE)
SELECT table_name
FROM all_tables
WHERE owner = 'ECOMMERCE'
ORDER BY table_name;

-- All schemas (DBA privilege required)
SELECT owner, table_name
FROM dba_tables
ORDER BY owner, table_name;

-- Pattern match for order tables
SELECT table_name
FROM user_tables
WHERE table_name LIKE 'ORD%';

Common Mistakes

Trying to run MySQL’s SHOW TABLES command. Oracle has no SHOW keyword; instead, query USER_TABLES, ALL_TABLES, or DBA_TABLES as shown above.
Forgetting to uppercase the OWNER filter. Oracle stores schema names in uppercase by default, so using lowercase returns no rows. Always use capital letters or apply UPPER().