Oracle Show Tables

In this article, we will discuss various methods and techniques we can use to fetch the tables of a given database in Oracle Database Server.

Oracle Show Tables

When working with relational databases, you will come across an instance where you need to view and gather information about the available tables in a given database.

Let us explore.

Oracle Data Dictionaries

Unlike databases such as MySQL or PostgreSQL, Oracle does not provide a simple command such as SHOW TABLES to fetch the available tables.

Therefore, we need to query the Oracle Data Dictionaries to fetch for server information such as tables. Oracle data dictionaries refers to a set of system tables that hold useful information about the databases in the server. They include information such as schema, users, privileges, etc.

When you execute a query on the server, Oracle will update relevant entries in the data dictionaries. This ensures that the server holds accurate and up-to-date information about the various objects in the server.

Oracle Show Current User's Table

To show the tables owned by the current user, we can run the query as shown:

SELECT table_name FROM USER_TABLES;

The command will return all the tables owned by the current user. An example output is as shown:

TABLE_NAME                 |
---------------------------+
LOGMNR_SESSION_EVOLVE$     |
LOGMNR_GLOBAL$             |
LOGMNR_PDB_INFO$           |
LOGMNR_DID$                |
LOGMNR_UID$                |
LOGMNRGGC_GTLO             |
LOGMNRGGC_GTCS             |
LOGMNRC_DBNAME_UID_MAP     |
LOGMNR_LOG$                |
LOGMNR_PROCESSED_LOG$      |
LOGMNR_SPILL$              |
LOGMNR_AGE_SPILL$          |
LOGMNR_RESTART_CKPT_TXINFO$|
LOGMNR_ERROR$              |
LOGMNR_RESTART_CKPT$       |
LOGMNR_FILTER$             |
...........................
LOGMNR_PROPS$              |
LOGMNR_ENC$                |
LOGMNR_REFCON$             |
LOGMNR_IDNSEQ$             |
LOGMNR_PARTOBJ$            |
LOGMNRP_CTAS_PART_MAP      |
LOGMNR_SHARD_TS            |
LOGSTDBY$APPLY_PROGRESS    

Oracle Show Tables Accessible by Current User

To view the tables accessible by the current user, despite the ownership, select the tables from the all_tables command as:

SELECT table_name FROM ALL_ALL_TABLES 

This command should return a lot of tables that are accessible by the current user. To limit the output from the command, we can run the command:

SELECT table_name FROM ALL_ALL_TABLES WHERE OWNER = 'schema_name' 

You can specify the target owner in the schema_name option.

Oracle Show ALL Tables

if you wish to retrieve all the tables in the server, you can query the dba_tables as shown in the example below:

SELECT table_name FROM DBA_TABLES;

An example output is as shown:

TABLE_NAME                    |
------------------------------+
FILE$                         |
CDEF$                         |
CCOL$                         |
PROXY_DATA$                   |
PROXY_ROLE_DATA$              |
ICOL$                         |
COL$                          |
IND$                          |
TAB$                          |
CLU$                          |
LOB$                          |
COLTYPE$                      |
SUBCOLTYPE$                   |
NTAB$                         |
REFCON$                       |
OPQTYPE$                      |
ICOLDEP$                      |
VIEWTRCOL$                    |
LIBRARY$                      |
ASSEMBLY$                     |
ATTRCOL$                      |
..................................................................

Depending on the server configuration, querying the dba_tables table may require additional privileges than provided by the current user.

Termination

Through this tutorial, you learned how to show tables for the current user, show accessible tables for the current, and all the tables in the Oracle Server.

Stay tuned for more upcoming tutorials. Thanks for reading!!

If you enjoy our content, please consider buying us a coffee to support our work:

Table of Contents
Great! Next, complete checkout for full access to GeekBits.
Welcome back! You've successfully signed in.
You've successfully subscribed to GeekBits.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info has been updated.
Your billing was not updated.