Get a List of all Tables In Oracle

How to show all tables in Oracle? Oracle offers a list of all tables using data dictionary. To get a list of all tables in an Oracle database, use the data dictionary views such as tab, user_tables, all_tables, and dba_tables. The queries listed below are equivalent to the “show tables” command in a mysql database. The queries will list the names of all tables in the Oracle database. The different views will show all tables based on the oracle user’s roles and privileges. The tab will show all tables of the current logged in user. The dba_tables will list all tables from the database.

There are various data dictionary views available to get a list of all Oracle tables. Depending on your privileges, you can query from those data dictionary views. The all tables view is the simplest approach to see all tables in the database. If you do not have sufficient privileges, Oracle will return the error “ORA-00942: table or view does not exist”.

List of All Tables using Tab

The simple way to get the list of all tables in oracle is using the tab data dictionary view. The tab will show all the tables and views available for the current user.

select * from tab;

Using User_tables and User_Objects

User views such as user_tables and user_objects can be used to get a list of all tables. The tables that the current user has created will be listed. The current logged-in schema is the default schema. These data dictionary views are used to retrieve a list of all the tables for the current schema.

select table_name from user_tables;

select object_name from user_objects where object_type='TABLE';

Using all_tables and all_objects

To get a list of all the tables that the current user has access to. The current user may have access to tables from many schemas. To display a list of all tables in Oracle, utilize data dictionary views such as all_tables and all_objects. These tables allow you to view all privileged tables.

SELECT owner, table_name FROM all_tables;
SELECT owner, table_name FROM all_tables where owner='YAWIN';

SELECT DISTINCT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER='YAWIN';

Using dba_tables and dba_objects

These tables, such as dba tables and dba objects, can be accessed by users with database administrator privilege. These tables may be queried to get a list of all available tables in the database. These tables are used for administrative purposes by database administrators. These tables may be used to obtain a list of all tables from all schema.

select owner, table_name from DBA_TABLES where owner='YAWIN';

SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER='YAWIN';

Table name with columns

Using the data dictionary views, you may retrieve a list of all tables with recently added columns. To get tables with columns, use the col view. If a table has more than one column, the table name will appear in multiple columns.

SELECT distinct table_name FROM COLS;
SELECT table_name, column_name, data_type FROM COLS;  
SELECT table_name, column_name, data_type FROM cols WHERE table_name LIKE 'EMP%';

Using views

The required table name may not be found in the list of all tables in the Oracle database. It is possible that it is not a table, but rather a view. The view queries listed below will allow you to see all of the views in the Oracle database.

SELECT view_name FROM USER_VIEWS;
SELECT owner, view_name FROM all_views where owner='YAWIN';
SELECT owner, view_name FROM dba_views where owner='YAWIN';

Using dba_tab_columns

The another oracle data dictionary view name is dba_tab_columns. This view helps to get list of all the tables with columns names in the oracle database.

SELECT distinct table_name FROM dba_tab_columns where owner='YAWIN';
SELECT table_name, column_name, data_type FROM dba_tab_columns where owner='YAWIN';
SELECT table_name, column_name, data_type FROM dba_tab_columns where owner='YAWIN' AND table_name LIKE 'EMP%';