This website use cookies to help you have a superior and more admissible browsing experience on the website.
Loading...
By:
MySQL and PostgreSQL users often reach for SHOW TABLES; or \dt out of habit. In Oracle, neither command exists, and running them will throw an error.
Oracle uses a Data Dictionary instead, a set of read-only system views that store metadata about everything in the database. Querying these views is more verbose than a one-line shortcut, but also far more powerful. Beyond just table names, you can pull storage details, partition info, row counts, and last-analyzed dates from the same query.
| Database | How to List Tables |
|---|---|
| MySQL | SHOW TABLES; |
| PostgreSQL | \dt or query information_schema |
| Oracle | Query Data Dictionary Views (USER_TABLES, ALL_TABLES, DBA_TABLES) |
The Data Dictionary is organized into three levels of scope, each tied to a different level of database access. The view you query depends on what your account has permission to see, and that is exactly what the next section covers.
Oracle organizes its Data Dictionary views into three levels based on ownership and permissions. Knowing which level applies to your account is the first step before writing any query.
Use USER_TABLES when you only need to see tables created within your own schema. This is the most common starting point for developers working with their own objects.
SELECT table_name, tablespace_name, num_rows, last_analyzed
FROM user_tables
ORDER BY table_name;
Including num_rows and last_analyzed gives you a quick sense of whether a table has data and when the optimizer last collected statistics on it.
In multi-schema environments, you may need to see tables owned by other users that you have been granted permission to read or modify. ALL_TABLES covers all tables accessible to your account, not just your own.
To filter by a specific schema, add a WHERE clause:
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'SALES_DEPT'
ORDER BY owner, table_name;
One important detail: Oracle stores table names and usernames in UPPERCASE by default. Querying WHERE owner = 'sales_dept' will return zero results. Always use uppercase inside the single quotes, unless the schema was created with a quoted mixed-case name.
DBA_TABLES gives you a complete view of every table across all schemas. You need either the DBA role or the SELECT ANY DICTIONARY privilege to query it.
Because Oracle ships with many internal system schemas, a raw query returns a lot of noise. Filter them out with an exclusion list:
SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'APPQOSSYS')
ORDER BY owner, table_name;
If you get ORA-00942: table or view does not exist, your account lacks the required privileges. Ask your DBA to grant you the SELECT_CATALOG_ROLE, which is the standard way to enable read access to dictionary views without full DBA permissions.
Standard queries get you a basic list. These queries go further, helping you find tables by column, size, or partial name, and pick the right tool for the job.
| Use Case | View to Query | Privilege Needed |
|---|---|---|
| Find tables by column name | ALL_TAB_COLUMNS | Standard |
| Check row counts and last analyzed date | USER_TABLES | Standard |
| Search tables by partial name | USER_TABLES | Standard |
| List tables by size | DBA_SEGMENTS | DBA Role |
| List tables excluding system schemas | DBA_TABLES | DBA Role |
| Visual exploration | SQL Developer / dbForge | User access |
If you know a column name but cannot remember which tables use it, query ALL_TAB_COLUMNS. This is especially useful in complex schemas with many related tables.
SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE column_name = 'USER_ID'
ORDER BY owner, table_name;
The Oracle optimizer relies on statistics to choose the most efficient execution plan. If num_rows is inaccurate or last_analyzed is months old, query performance can degrade quietly without any obvious error.
SELECT table_name, num_rows, last_analyzed
FROM user_tables
WHERE num_rows > 0
ORDER BY num_rows DESC;
DBMS_STATS.GATHER_TABLE_STATS on the relevant tables.When you only remember part of a table name, use the LIKE operator with the % wildcard. Keep the search string in UPPERCASE to match how Oracle stores identifiers.
SELECT table_name
FROM user_tables
WHERE table_name LIKE '%INVENTORY%';
Table data in Oracle is stored in segments, so you need to query DBA_SEGMENTS to get accurate size figures. This query returns each table’s total size in megabytes, largest first.
SELECT segment_name AS table_name,
owner,
SUM(bytes) / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
GROUP BY segment_name, owner
ORDER BY size_mb DESC;
This is a practical starting point for storage audits or identifying large tables before a migration.
A raw query against DBA_TABLES includes Oracle’s own internal tables, which are rarely what you need. Filter them out to get a clean view of your application tables.
SELECT owner, table_name
FROM dba_tables
WHERE owner NOT IN (
'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP',
'APPQOSSYS', 'CTXSYS', 'XDB', 'WMSYS'
)
ORDER BY owner, table_name;
The right tool depends on how you work:
SET LINESIZE.Even experienced DBAs run into access issues or unexpected empty results when querying Oracle’s Data Dictionary. Here are the four most common problems and how to fix them.
SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY privilege.SELECT on those objects to your user.CREATE TABLE "Sales_Data", Oracle preserves the exact casing. A standard query using 'SALES_DATA' will return nothing.SELECT * FROM "Sales_Data";ALTER SESSION SET CONTAINER = pdb_name; before running your query.Once you have a clear picture of your table structure, the natural next question is how to keep that data protected and in sync across systems. For teams running Oracle in production, that means having a reliable replication layer in place, one that can handle high transaction volumes, schema changes, and cross-platform environments without disrupting live operations.
i2Stream is an enterprise-grade database replication solution built for exactly these scenarios. Rather than treating replication as a one-size-fits-all process, it maps directly to the challenges Oracle environments face in practice.
For teams that need broader data resilience beyond replication, Info2Soft’s product suite covers the full range. i2Backup handles centralized backup across physical, virtual, and cloud environments, while i2Migration supports non-disruptive cross-platform migration for Oracle and other systems.
Q1: Can I list tables in Oracle without DBA privileges?
Yes. USER_TABLES and ALL_TABLES are both accessible to standard users without any special privileges. USER_TABLES shows tables you own, and ALL_TABLES shows any tables you have been granted access to. You only need elevated privileges when querying DBA_TABLES.
Q2: Why does USER_TABLES return no results?
This usually means the current user does not own any tables. If you are using a dedicated connection account or a newly created user, try querying ALL_TABLES instead to see what objects your account has permission to access.
Q3: What is the difference between USER_TABLES, ALL_TABLES, and DBA_TABLES?
USER_TABLES shows only the tables you own. ALL_TABLES includes those plus any tables other users have granted you access to. DBA_TABLES covers every table in the entire database, but requires administrative privileges to query.
Q4: How do I list tables by size in Oracle?
Query DBA_SEGMENTS, filtering by segment_type = ‘TABLE’ and summing the bytes per segment. This requires DBA privileges. See the query in the Advanced Queries section above for the full syntax.
Listing tables in Oracle comes down to knowing which Data Dictionary view fits your situation. Use USER_TABLES for your own schema, ALL_TABLES when you need to see across schemas you have access to, and DBA_TABLES when you need a full database-wide view with the right privileges in place.
From there, the advanced queries in this guide help you go further: finding tables by column name, checking statistics, filtering by size, or narrowing results by partial name. Most access issues trace back to either a privilege gap or a case sensitivity mismatch, both of which are straightforward to resolve once you know what to look for.
If your work goes beyond querying table structure into keeping data in sync across systems, Info2soft’s i2Stream is worth exploring. It handles real-time Oracle replication at the transaction level, supports schema changes without manual intervention, and works across heterogeneous database environments without touching your production server.
