intro
Learn to list tables in Oracle using DbVisualizer SQL Client data and dictionary views (USER_TABLES, ALL_TABLES, DBA_TABLES) with SQL examples.
When working with Oracle database systems, database administrators and developers frequently need to explore table structures and review ownership metadata.
In this comprehensive guide, we'll explore the three different approaches to listing Oracle tables. By the end of this tutorial, you'll understand when and how to use each method, ensuring you can quickly find the table information you need regardless of your database role or requirements.
Understanding Oracle Data Dictionaries
Before going into the specific methods, you need to understand how Oracle organizes its data structures and metadata.
Oracle's Hierarchical Organization
Oracle databases use a hierarchical organization where:
What are Oracle Data Dictionaries?
Oracle data dictionaries consist of read-only database objects that store essential metadata about your database environment. These comprehensive repositories include information such as:
Oracle automatically maintains and refreshes these dictionary entries whenever database-modifying operations occur. This ensures that the metadata always reflects the current state of your database objects.
These data dictionary views are your gateway to listing tables and understanding their properties. The three primary categories of data dictionary views for table information are:
Time to look at three methods for listing tables in Oracle including using the DbVisualizer SQL client!
Before that, if you happen to use a silicon chip Macbook, this tutorial will help you set up Oracle database on your mac. Windows users typically have a more straightforward installation process.
After successfully setting up the Oracle database on your computer, you’re going to see a lot of already built-in system Schemas that Oracle automatically creates as shown below:
Each of these Schemas above owns the tables, code, and other objects needed for a specific internal feature or optional component of the database.
What are some of these Schemas for? Here are a few examples from our list:
You can see the pattern: the Schema names often correspond to a specific database feature.
This means we’ll have to create our own Schema. The most important rule is that we do not modify or add your own tables to these system Schemas as doing so can corrupt our database.
Run the following commands in the SQL commander of an Oracle database client like DbVisualizer:
1
-- You must be connected as a powerful user like SYS or SYSTEM to run this
2
CREATE USER dbvisualizer_demos IDENTIFIED BY "a_very_strong_password";
3
4
-- Grant the basic roles to connect and create tables, views, etc.
5
GRANT CONNECT, RESOURCE TO dbvisualizer_demos;
6
7
-- Give the user unlimited space in the default tablespace
8
ALTER USER dbvisualizer_demos QUOTA UNLIMITED ON USERS;
This setup creates a fully functional development user (Schema) that can connect to the database, create tables, indexes, sequences, and other objects, and store unlimited data in the USERS tablespace. That is a typical setup for application development or demo purposes, giving the user enough privileges to work with data without full DBA access.
The next thing to do is to open your Oracle database connection tree, and then the “Schemas” tree. Right-click on the “Schemas” and select the “Refresh Objects Tree” option. The newly created user (schema) should be available:
Now, let’s begin.
Method #1: Using USER_TABLES View
USER_TABLES is your go-to view when you need to list tables that belong to your current Schema. This approach is perfect for developers working within their own Schema boundaries or when building applications that need to discover their own table structures.
This is how to show tables owned by the current user:
1
SELECT table_name, tablespace_name, num_rows, last_analyzed
2
FROM USER_TABLES
3
ORDER BY table_name;
It can be seen that we've successfully executed the USER_TABLES query and it's showing interesting results. Notice the table names: ACCESS, ACCHK_EVENTS, ACLMVS_REFLOG, etc. and table spaces such as: SYSTEM, SYSAUX.
If we want to return only our user-created tables from our dbvisualizer_demos schema and not from the SYSAUX and SYSTEM tables, we will have to:
Step 1: Connect as dbvisualizer_demos instead of SYS by creating a new database connection in DbVisualizer with username and password like we earlier did here. This will ensure that our default Schema is dbvisualizer_demos.
Step 2: Create some test tables to see the views in action:
1
CREATE TABLE customers (
2
customer_id NUMBER PRIMARY KEY,
3
customer_name VARCHAR2(100),
4
email VARCHAR2(100),
5
created_date DATE DEFAULT SYSDATE
6
);
7
8
CREATE TABLE orders (
9
order_id NUMBER PRIMARY KEY,
10
customer_id NUMBER,
11
order_date DATE DEFAULT SYSDATE,
12
total_amount NUMBER(10,2)
13
);
14
15
INSERT INTO customers VALUES (1, 'John Doe', 'john@email.com', SYSDATE);
16
INSERT INTO customers VALUES (2, 'Jane Smith', 'jane@email.com', SYSDATE);
17
18
INSERT INTO orders VALUES (100, 1, SYSDATE, 250.00);
19
INSERT INTO orders VALUES (101, 2, SYSDATE, 175.50);
20
21
COMMIT;
Then USER_TABLES would show only that user's (dbvisualizer_demos) tables like this:
Method #2: Using ALL_TABLES View
The ALL_TABLES view shows all tables accessible to the current user (both owned tables and tables you have privileges to access). This is particularly valuable when working across multiple Schemas.
Let’s note that although we can either connect to the dbvisualizer_demos Schema or connect to the one with SYS Schema as default, it is advisable to stay connected as SYS. This is because:
This method is designed to show cross-schema visibility, which is why staying connected as SYS is actually more appropriate.
Step 1: Change the connection back to Oracle Thin 19 c CDB connection and the Schema to the SYS Schema:
Step 2: Run the query below:
1
SELECT owner, table_name, tablespace_name, num_rows, last_analyzed
2
FROM ALL_TABLES
3
WHERE owner = 'DBVISUALIZER_DEMOS'
4
ORDER BY table_name;
Bingo! Time to look at the method #3 which involves using the DBA_TABLES view.
Method #3: Using DBA_TABLES View
The DBA_TABLES view provides the most comprehensive view of all tables in the Oracle database, regardless of ownership, your specific access rights to the data within those tables or Schema boundaries. This view requires DBA privileges and is essential for database administration, security audits, and complete database inventory tasks.
For this method, you must be connected as SYS (or another user with DBA privileges).
1
SELECT owner, table_name, tablespace_name, status, num_rows
2
FROM DBA_TABLES
3
WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DIP')
4
ORDER BY owner, table_name;
The bottom portion shows "513/5" indicating there are 513 total results but only showing the first page. This confirms DBA_TABLES gives you comprehensive database-wide table visibility that neither USER_TABLES nor ALL_TABLES can provide.
What happens if you try without DBA privileges? Well, if you connect as dbvisualizer_demos and try to query DBA_TABLES , you’re going to get an “ORA-00942: table or view does not exist" error, like this:
And that’s a wrap!
Conclusion
Knowing how to list Oracle tables efficiently is essential, whether you're exploring a new database or managing enterprise systems. The three methods we covered: USER_TABLES, ALL_TABLES, and DBA_TABLES, each serve different purposes based on your access level and needs.
DbVisualizer makes this even easier with its visual Object View for quick browsing and SQL Commander for running the advanced queries we discussed. You get the best of both worlds: point-and-click simplicity when you need it, and full SQL power when you want precise control. Start with your most common scenarios, use the method that fits your situation, and don't forget to leverage DbVisualizer's GUI features to speed up your workflow.
Thanks for reading—and for using DbVisualizer. We’ll be back with more, very soon.
FAQ
What's the difference between USER_TABLES, ALL_TABLES, and DBA_TABLES?
USER_TABLES shows only tables you own, ALL_TABLES shows tables you can access (owned + granted privileges), and DBA_TABLES shows all tables in the entire database (requires DBA privileges).
Think of it as: USER = "mine", ALL = "mine + accessible", DBA = "everything in the database".
Why do I still see system tables when using USER_TABLES even after changing the Schema dropdown in DbVisualizer?
The Schema dropdown in DbVisualizer only changes the default Schema context for object references. It doesn't change your actual database connection user. If you're connected as SYS, USER_TABLES will always show SYS-owned tables (which are system tables) regardless of the dropdown setting.
To truly see your application tables, either create a new connection as the target user or use ALL_TABLES/DBA_TABLES with a WHERE clause to filter by owner.
Do I need special privileges to use these views?
