How do I list all tables in a schema in Oracle SQL?

by

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'

More Related Contents:

  1. Why do we combine several select statements
  2. How ROWNUM works in pagination query?
  3. Get list of all tables in Oracle?
  4. Removing duplicate rows from table in Oracle
  5. Oracle: If Table Exists
  6. How do I spool to a CSV formatted file using SQLPLUS?
  7. Oracle SQL escape character (for a ‘&’)
  8. SQL Error: ORA-00933: SQL command not properly ended
  9. CASE vs. DECODE
  10. DATEDIFF function in Oracle [duplicate]
  11. WHERE IN condition not accepting String value
  12. What is the difference between Views and Materialized Views in Oracle?
  13. Pivoting of data using two columns
  14. Check if current date is between two dates Oracle SQL
  15. Benefits Of Using SQL Ordinal Position Notation?
  16. ORA-00907: missing right parenthesis
  17. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  18. Unordered results in SQL
  19. Oracle Dynamic Pivoting
  20. Foreign Key Creation issue in Oracle
  21. create custom function for date difference excluding weekends and holidays in oracle sql
  22. What is it exactly a BLOB in a DBMS context
  23. Custom Order in Oracle SQL
  24. Oracle/SQL: Why does query “SELECT * FROM records WHERE rownum >= 5 AND rownum
  25. How do I list all the columns in a table?
  26. Oracle: SQL query to find all the triggers belonging to the tables?
  27. oracle SQL how to remove time from date
  28. Product() aggregate function
  29. Re-order columns of table in Oracle
  30. Select latest row for each group from oracle

Leave a Comment