Sometimes you will need to list the existing tables of your database. Maybe you want to see all the existing tables before creating a new one so that you know the table name you are going to use is not already used. Maybe you don’t remember the exact name of a table.
👋 Check out our easy to use desktop GUI for Oracle
Beekeeper Studio is a truly cross-platform Oracle GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
Either way, most databases provide an easy way to list tables. For example, you can use the SHOW TABLES command in MySQL. And
ANSI SQL defines a set of standard
information_schema tables for users to find information about the schema objects.
Things are done a bit differently in Oracle, as you’ll see below.
The Oracle Data Dictionary
The data dictionary is a collection of tables that include information on tables, users, views, indexes, sequences, procedures, triggers, etc. The data dictionary is maintained by the Oracle engine itself. This is similar in concept to
There are three tables that contain information about tables according to user accessibility.
user_tables- tables owned by the current user.
all_tables- tables current user has access to.
dba_tables- all the tables in the system.
So to list tables you have to pick the right place to look. Usually querying
all_tables is correct. Let’s see some examples.
When reading this article, you can use Oracle live to test the code.
List Oracle Tables That You Own
Before listing any table, let’s create a test table.
CREATE TABLE people( person_id NUMBER NOT NULL, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, PRIMARY KEY(person_id) )
Now we are ready to list tables. To list all tables owned by the current user, you can query the
user_tables table. The syntax will look like this
SELECT * FROM user_tables ORDER BY table_name;
This returns a long list of columns that contain various information about the table. You can use the following query to just get a list of table names.
SELECT table_name FROM user_tables ORDER BY table_name;
How to List all the tables accessible to the current user?
The above examples showed how to list tables created by the user. To list all the tables that a user has access to (including tables created by other people) we have to query the
SELECT table_name, owner FROM all_tables ORDER BY owner, table_name
You will get the following output once you run the above query on Oracle live.
Of course, you can retrieve more columns or change the order of the table list by changing the SELECT and ORDER BY clauses of the query.
List All Tables in The Whole Oracle Database
You have to use
dba_tables to get all the tables in the system. See the syntax below.
SELECT table_name, owner FROM dba_tables ORDER BY owner, table_name
The above query might give you an error, it’s not typical to give everyone access to this table. If that happens, you can request access to
dba_tables from your database administrator (although they might not grant the request!).
List Oracle Tables Created By A Specific User
You can query and filter these tables much the same as any normal table, that means you can use the
WHERE clause to list all the tables created by a specific user.
SELECT table_name, owner FROM all_tables WHERE owner = 'BEEKEEPER' ORDER BY table_name;
This query will return all the accessible tables created by the user BEEKEEPER.
List Oracle Tables Matching a Substring
Sometimes you don’t need to list all tables. You only want to find a table that starts with a specific letter or list tables that have a specific string in the name. For that, you can again use the
WHERE clause to filter table names.
SELECT table_name, owner FROM all_tables WHERE table_name LIKE 'A%' ORDER BY owner, table_name;
Understanding Who Owns A Table In Oracle
As you can see, the three data dictionary tables have been created around the concept of table owners. The owner of a table is the person who creates that table. Every table has one owner. However, an owner can create as many tables as they like.
Oracle also comes with many System tables. You can find a long list of them here. For example, the table named
ALL_ARGUMENTS stores arguments of the procedures and functions accessible to the current user. There is another table called
ALL_OBJECTS, which stores data about all the objects accessible to the current user. In the same way, you can find tables for procedures, dependencies, errors, directories, etc.
When reading the Oracle documentation you will see that most of these tables has an owner field. The owner field mentions the creator of each object( procedure, error, etc).
Finally, The Oracle documentation refers to the tables accessible to the current user. That’s why they have the
ALL_ prefix. Each of these tables has
USER_ counterparts. It works the same way we discussed above in the article. For example, the
USER_OBJECTS table contains all the objects created by the current user, while the
DBA_OBJECTS table contains all the objects created by every user.
As you can see, listing tables in Oracle is pretty easy. Usually, the only restriction is having appropriate permissions to view what you want.
Thanks for reading all the way down to the bottom. You’d probably be interested to learn about our app - Beekeeper Studio, the best looking and easiest to use Oracle GUI you’ve ever seen. Download for free