Get list of all tables in Oracle?

Querying user_tables and dba_tables didn't work.
This one did:

select table_name from all_tables 

Going one step further, there is another view called cols (all_tab_columns) which can be used to ascertain which tables contain a given column name.

For example:

SELECT table_name, column_name FROM cols WHERE table_name LIKE 'EST%' AND column_name LIKE '%CALLREF%'; 

to find all tables having a name beginning with EST and columns containing CALLREF anywhere in their names.

This can help when working out what columns you want to join on, for example, depending on your table and column naming conventions.

For better viewing with sqlplus

If you're using sqlplus you may want to first set up a few parameters for nicer viewing if your columns are getting mangled (these variables should not persist after you exit your sqlplus session ):

set colsep '|' set linesize 167 set pagesize 30 set pagesize 1000 

Show All Tables

You can then use something like this to see all table names:

SELECT table_name, owner, tablespace_name FROM all_tables; 

Show Tables You Own

As @Justin Cave mentions, you can use this to show only tables that you own:

SELECT table_name FROM user_tables; 

Don't Forget about Views

Keep in mind that some "tables" may actually be "views" so you can also try running something like:

SELECT view_name FROM all_views; 

The Results

This should yield something that looks fairly acceptable like:

Simple query to select the tables for the current user:

 SELECT table_name FROM user_tables; 

 select object_name from user_objects where object_type='TABLE'; 

----------------OR------------------

 select * from tab; 

----------------OR------------------

 select table_name from user_tables; 

Execute the below commands:

Show all tables in the Oracle Database

sql> SELECT table_name FROM dba_tables;

Show tables owned by the current user

sql> SELECT table_name FROM user_tables;

Show tables that are accessible by the current user

sql> SELECT table_name FROM all_tables ORDER BY table_name;

Try the below data dictionary views.

tabs dba_tables all_tables user_tables 

Oracle database to display the names of all tables using below query

 SELECT owner, table_name FROM dba_tables; SELECT owner, table_name FROM all_tables; SELECT table_name FROM user_tables;

vist more : http://www.plsqlinformation.com/2016/08/get-list-of-all-tables-in-oracle.html

Try selecting from user_tables which lists the tables owned by the current user.

With any of those, you can select:

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

A new feature available in SQLcl( which is a free command line interface for Oracle Database) is

Tables alias.

Here are few examples showing the usage and additional aspects of the feature. First, connect to a sql command line (sql.exe in windows) session. It is recommended to enter this sqlcl specific command before running any other commands or queries which display data.

SQL> set sqlformat ansiconsole -- resizes the columns to the width of the -- data to save space 

SQL> tables

TABLES ----------- REGIONS LOCATIONS DEPARTMENTS JOBS EMPLOYEES JOB_HISTORY .. 

To know what the tables alias is referring to, you may simply use alias details <alias>

SQL> alias details tables tables - tables <schema> - show tables from schema -------------------------------------------------- select table_name "TABLES" from user_tables 

You don't have to define this alias as it comes by default under SQLcl. If you want to list tables from a specific schema, using a new user-defined alias and passing schema name as a bind argument with only a set of columns being displayed, you may do so using

SQL> alias tables_schema = select owner, table_name, last_analyzed from all_tables where owner = :ownr;

Thereafter you may simply pass schema name as an argument

SQL> tables_schema HR

OWNER TABLE_NAME LAST_ANALYZED HR DUMMY1 18-10-18 HR YOURTAB2 16-11-18 HR YOURTABLE 01-12-18 HR ID_TABLE 05-12-18 HR REGIONS 26-05-18 HR LOCATIONS 26-05-18 HR DEPARTMENTS 26-05-18 HR JOBS 26-05-18 HR EMPLOYEES 12-10-18 .. .. 

A more sophisticated pre-defined alias is known as Tables2, which displays several other columns.

SQL> tables2 Tables ====== TABLE_NAME NUM_ROWS BLOCKS UNFORMATTED_SIZE COMPRESSION INDEX_COUNT CONSTRAINT_COUNT PART_COUNT LAST_ANALYZED AN_IP_TABLE 0 0 0 Disabled 0 0 0 > Month PARTTABLE 0 0 0 1 0 1 > Month TST2 0 0 0 Disabled 0 0 0 > Month TST3 0 0 0 Disabled 0 0 0 > Month MANAGE_EMPLYEE 0 0 0 Disabled 0 0 0 > Month PRODUCT 0 0 0 Disabled 0 0 0 > Month ALL_TAB_X78EHRYFK 0 0 0 Disabled 0 0 0 > Month TBW 0 0 0 Disabled 0 0 0 > Month DEPT 0 0 0 Disabled 0 0 0 > Month 

To know what query it runs in the background, enter

alias details tables2 

This will show you a slightly more complex query along with predefined column definitions commonly used in SQL*Plus.

Jeff Smith explains more about aliases here

select * from dba_tables 

gives all the tables of all the users only if the user with which you logged in is having the sysdba privileges.

I did not find answer which would point to use

DBA_ALL_TABLES (ALL_ALL_TABLES/USER_ALL_TABLES) 

so decided to add my version as well. This view actually returns more that DBA_TABLES as it returns object tables as well (http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1003.htm).

We can get all tables including column details from below query:

SELECT * FROM user_tab_columns; 

You can use Oracle Data Dictionary to get information about oracle objects.

You can get list of tables in different ways:

select * from dba_tables 

or for example:

select * from dba_objects where object_type = 'TABLE' 

Then you can get table columns using table name:

select * from dba_tab_columns 

Then you can get list of dependencies (triggers, views and etc.):

select * from dba_dependencies where referenced_type='TABLE' and referenced_name=:t_name 

Then you can get text source of this objects:

select * from dba_source 

And you can use USER or ALL views instead of DBA if you want.

Including views:

SELECT owner, table_name as table_view FROM dba_tables UNION ALL SELECT owner, view_name as table_view FROM DBA_VIEWS 

Below is a commented snippet of SQL queries describing how options you can make use of:

-- need to have select catalog role SELECT * FROM dba_tables; -- to see tables of your schema SELECT * FROM user_tables; -- tables inside your schema and tables of other schema which you possess select grants on SELECT * FROM all_tables; 

The following query only list the required data, whereas the other answers gave me the extra data which only confused me.

select table_name from user_tables; 

I was looking to get a list of all columns names belonging to a table of a schema sorted by the order of column id.

Here's the query I am using: -

SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = 'schema_owner_username' AND TABLE_NAME='table_name' ORDER BY COLUMN_ID ASC; 

Indeed, it is possible to have the list of tables via SQL queries.it is possible to do that also via tools that allow the generation of data dictionaries, such as ERWIN, Toad Data Modeler or ERBuilder. With these tools, in addition to table names, you will have fields, their types, objects like(triggers, sequences, domain, views...)

Below steps to follow to generate your tables definition:

1. You have to reverse engineer your database
   • In Toad data modeler: Menu -> File -> reverse engineer -> reverse engineering wizard
   • In ERBuilder data modeler: Menu -> File -> reverse engineer

Your database will be displayed in the software as an Entity Relationship diagram.

2. Generate your data dictionary that will contain your Tables definition
   • In Toad data modeler: Menu -> Model -> Generate report -> Run
   • In ERBuilder data modeler: Menu -> Tool -> generate model documentation

To get all the table names, we can use:

Select owner, table_name from all_tables; 

if you have dba permission, you can use:

Select owner, table_name from dba_tables; 

select * from all_all_tables 

this additional 'all' at the beginning gives extra 3 columns which are:

OBJECT_ID_TYPE TABLE_TYPE_OWNER TABLE_TYPE 

I use the ALL_TAB_COLS. ALL_TAB_COLS describes the columns of the tables, views, and clusters accessible to the current user.

Oracle Documentation

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH, GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED, V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN, SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID, HISTOGRAM, QUALIFIED_COL_NAME FROM ALL_TAB_COLS /* WHERE add filters here */ 

Tables in the current user - logon schema

select * from tabs;