How to List Tables in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Query USER_TABLES for your own tables, ALL_TABLES for ones you can access, or DBA_TABLES for every table. USER_TAB_COLUMNS lists columns.
-- Tables you own
SELECT table_name FROM user_tables ORDER BY table_name;USER_TABLES vs ALL_TABLES vs DBA_TABLES
These three data dictionary views cover the same underlying metadata at different scopes. USER_TABLES shows only tables in your own schema and has no OWNER column, since it is implied. ALL_TABLES shows every table you have been granted privileges on, across schemas, and includes OWNER. DBA_TABLES shows every table in the entire database and requires the SELECT_CATALOG_ROLE privilege or DBA access. Pick the narrowest one that answers your question.
-- Tables you can see across schemas
SELECT owner, table_name FROM all_tables WHERE owner = 'HR';
-- Every table in the database (requires DBA privileges)
SELECT owner, table_name FROM dba_tables WHERE owner = 'HR';Listing columns
SELECT column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;Table and column names in these views are stored in uppercase unless the object was created with a quoted, case-sensitive identifier, so a WHERE table_name = 'employees' filter silently returns nothing against the default naming.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free