Oracle equivalent of information_schema.tables

sql oracle select data-dictionary
Arun picture Arun · Mar 7, 2019 · Viewed 7.5k times · Source

I get a "table or view does not exist" error while I try to execute the query below in Oracle:

SQL QUERY

SELECT table_type,
       table_name
FROM information_schema.tables
WHERE table_rows >= 1;

ERROR

ORA-00942: table or view does not exist

How do we query metadata about tables in Oracle?

Answer

Mureinik picture Mureinik · Mar 7, 2019

Oracle indeed doesn't provide the information_schema views, but has its own data dictionary. You can use all_tables to create a similar query:

SELECT *
FROM   all_tables
WHERE  num_rows > 1

Related questions

DATEDIFF function in Oracle

I need to use Oracle but DATEDIFF function doesn't work in Oracle DB. How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC. SELECT DATEDIFF ('2000-01-01','2000-01-02') AS …

Read More
If statement in select (ORACLE)

Hi I have simply select and works great: select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity, gcrs.Area_name, gcrs.sector_name, substr(gcrs.stream_…

Read More
How to kill a running SELECT statement

How can I stop a running SELECT statement by killing the session? The command is continuously giving me output based on the SELECT statement, I want to stop it in between.

Read More