How To List Oracle Tables

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.

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 information_schema.

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;

Output

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 all_tables table.

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 DBA_ and 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.

Conclusion

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