Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

-------------Problems Reply------------

SELECT owner, table_name
FROM dba_tables

assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table or that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role either of which would allow you to query any data dictionary table. Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of tables that you probably don't care about because those are all delivered by Oracle.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view

SELECT owner, table_name
FROM all_tables

although that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES

SELECT table_name
FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column-- the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

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.

Simple query to select the tables for the current user:

SELECT table_name FROM user_tables;

Try the below data dictionary views.


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

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.

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:

Get list of all tables in Oracle?

With any of those, you can select:



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;

select object_name from user_objects where object_type='TABLE';


select * from tab;


select table_name from user_tables;

Category:sql Views:0 Time:2008-10-15

Related post

  • What is the dual table in Oracle? 2008-09-16

    I've heard people referring to this table and was not sure what it was about. --------------Solutions------------- It's a sort of dummy table with a single record used for selecting when you're not actually interested in the data, but instead want th

  • Hiding/Locking tables in oracle 2008-12-05

    How do I hide/lock a few tables in Oracle so that only my application has access to modify those data in the table? --------------Solutions------------- Create a user/login to the DB that you can control. Assign that user as the owner (using grants)

  • How can I get column names from a table in Oracle? 2009-01-16

    I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would want to retrieve those field name

  • Local Temporary table in Oracle 10 (for the scope of Stored Procedure) 2009-07-28

    I am new to oracle. I need to process large amount of data in stored proc. I am considering using Temporary tables. I am using connection pooling and the application is multi-threaded. Is there a way to create temporary tables in a way that different

  • How to update single table in oracle? 2009-11-12

    I am new to oracle. I have created one table in oracle i want to update that tables with all columns without any content..I want all columns in that table.How to do this?Can anyone explain me? Regards, Raman --------------Solutions------------- DESC

  • Function Table IN ORACLE 2009-12-28

    I've some prob with the function TABLE in ORACLE. SET SERVEROUTPUT ON SIZE 100000; DECLARE int_position NUMBER(20); TYPE T_REC_EMP IS RECORD ( nameFile VARCHAR2(200) ); R_EMP T_REC_EMP ; -- variable enregistrement de type T_REC_EMP TYPE TAB_T_REC_EMP

  • Login Access 2007 with ODBC linked table from Oracle 2010-03-03

    I'm migration Access 2003 to 2007. There are a lot of linked table from Oracle. I create a ODBC point to Oracle instance, then in Access I link all table through ODBC. I use this code login, so the "Oracle ODBC Driver Connect" dialog box would not po

  • How to create a temporary table in Oracle 2010-04-19

    I would like to create a temporary table in a Oracle database something like Declare table @table (int id) In SQL server And than populate it with a select statement Is it possible? Thanks --------------Solutions------------- Yep, oracle has temporar

  • ways to avoid global temp tables in oracle 2010-05-27

    We just converted our sql server stored procedures to oracle procedures. Sql Server SP's were highly dependent on session tables (INSERT INTO #table1...) these tables got converted as global temporary tables in oracle. We ended up with aroun 500 GTT'

  • joining table of oracle 2010-05-31

    Hi friends i am having problem in joining two tables in oracle my two tables are shown bellow table1 looks like id Name Jan 7001 Deven 22 7002 Clause 55 7004 Monish 11 7010 Dipesh 55 7070 Dipika 100 table2 looks like id Name Feb 7001 Deven 12 7002 Cl

  • How to find top three highest salary in emp table in oracle? 2010-05-31

    How to find top three highest salary in emp table in oracle? --------------Solutions------------- SELECT *FROM ( SELECT *FROM emp ORDER BY Salary desc ) WHERE rownum <= 3 ORDER BY Salary ; You can try. SELECT * FROM ( SELECT EMPLOYEE, LAST_NAME, S

  • How do I specify a candidate key and a foreign key when creating a table in Oracle 10g? 2010-09-19

    How do I specify a candidate key and a foreign key when creating a table in Oracle 10g? --------------Solutions------------- Following on from rics: CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, CONSTR

  • How to find tables having foreign key to a table in Oracle? 2010-09-20

    I plan to delete data from a table, i would like to know how many and which tables have a foreign key reference to this particular table in Oracle. As i will have to set the foreign keys to null. I would like to know list of all tables which have a F

  • Export MySQL tables to Oracle DB 2010-11-22

    I would like to migrate my existing mysql db (ver 5) to Oracle DB (11g). The export scripts provided by phpmyadmin do not seem to work. --------------Solutions-------------

  • Select Nth Row From A Table In Oracle 2010-12-22

    How can I select the Nth row from a table in Oracle? I tried SELECT PRICE FROM AAA_PRICING WHERE ROWNUM = 2 but that didn't work. Please help! --------------Solutions------------- Based on the classic answer:

  • Count number of tables in Oracle 2011-02-21

    Like MySQL has "SHOW TABLES" , how do you count tables in Oracle DB. Little bit of research gave me this query select owner, count(*) from dba_tables So like MySQL has a standard command, does Oracle have one ? --------------Solutions------------- Ye

  • Import Access table to oracle 2011-03-30

    I have a problem importing a table to oracle. When I import it a field is import for example: In access I have '20,200' and import to oracle '20' or ‘125.35’ in access and import to oracle as '12535' In access it is declared as number, and in oracle

  • creating a table in oracle using sql server table 2011-05-11

    I have a table in sql server 2008 r2, it contains 1M or more records now i want to create the table in oracle with same contents thats in sql. --------------Solutions------------- There are several ways of doing that. You can first look on the follow

  • IOT vs Heap tables in Oracle 2011-08-20

    Having worked with SQLServer for a long time, I'm used to think that clustered table (table with clustered index) is usually a better choice compared to heap table. Now I'm also working with Oracle and I don't really understand why their tables are h

  • Alternatives for temporary tables in Oracle 2011-09-29

    Create a temporary table inside a stored procedure, say '#Temp'. Insert values into 'Temp' table using a select statement, eg. Insert Into #Temp Select * from Employees. Now extract data from this Temp table, eg. Select * from #Temp where #Temp.Id =

Copyright (C), All Rights Reserved.

processed in 0.159 (s). 11 q(s)