Get list of all tables in Oracle?
SELECT owner, table_name
FROM dba_tables
This is 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 Oracle tables that you probably don't care about.
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.
List all tables of a given user in Oracle
This will get all the tables where the "JOHN" user is the owner:
SELECT * FROM USER_TABLES;
or
SELECT * FROM ALL_TABLES WHERE OWNER = 'JOHN';
([TL;DR] 'JOHN'
typically needs to be in upper-case. Assuming that the user john
was created using the CREATE USER john ...
statement then Oracle's default behaviour is to convert all object names (i.e. tables, columns, users, etc) to upper case. When you query the data-dictionary the table details will be stored in this case (and not the case you used in the original command unless you wrap it in double quotes).)
Get a list of all tables in Oracle Database in Python
If you want only list of tables (without table owner):
cur.execute("SELECT table_name FROM dba_tables")
for row in cur:
print row
How do I list all tables in a schema in Oracle SQL?
To see all tables in another schema, you need to have one or more of the following system privileges:
SELECT ANY DICTIONARY
(SELECT | INSERT | UPDATE | DELETE) ANY TABLE
or the big-hammer, the DBA role.
With any of those, you can select:
SELECT DISTINCT OWNER, OBJECT_NAME
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND OWNER = '[some other schema]'
Without those system privileges, you can only see tables you have been granted some level of access to, whether directly or through a role.
SELECT DISTINCT OWNER, OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND OWNER = '[some other schema]'
Lastly, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):
SELECT DISTINCT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
How to view all tables that are created by me in oracle 11g?
Thanks to @Littlefoot I learned that I was using "System" (default) schema, instead of creating my own. So in that system schema there are tons of default tables that are available. So by querying the "users_tables", I was also getting all the System default tables, along with my created tables.
Getting all tables from an oracle database
ALL_TABLES
will show you all the tables that you have access to SELECT
from. DBA_TABLES
will show you all the tables that exist in the database though you'll need an additional privilege grant to be able to query the DBA*
data dictionary objects.
Listing All Tables and Columns from my Database (ORACLE)
Use listagg
to get all the column names in a table as a list.
create table table_column_list as
SELECT table_name,listagg(column_name,',') within group(order by column_id) column_list
FROM user_tab_cols
group by table_name;
Related Topics
How to Change MySQL Table Names in Linux Server to Be Case Insensitive
Using Group by on Multiple Columns
Count Work Days Between Two Dates
How to Select the Newest Four Items Per Category
How to Update If Exists, Insert If Not (Aka "Upsert" or "Merge") in MySQL
Need to Return Two Sets of Data With Two Different Where Clauses
When Do I Need to Use a Semicolon VS a Slash in Oracle Sql
MySQL Query Finding Values in a Comma Separated String
How to Delete Duplicate Rows in SQL Server
Best Way to Do Multi-Row Insert in Oracle
Passing a Varchar Full of Comma Delimited Values to a SQL Server in Function
How to Implement a Many-To-Many Relationship in Postgresql