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:
