Ggtcf

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?

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

Closest view in Oracle is all_tables. Here's a detailed list of what each column represents. Pick the columns that are most appropriate to you to the get the details you want.

ALL_TABLES

COLUMN NAME COMMENTS
----------- ----------
 OWNER Owner of the table
 TABLE_NAME Name of the table
 TABLESPACE_NAME Name of the tablespace containing the table
 CLUSTER_NAME Name of the cluster, if any, to which the table
 belongs
 IOT_NAME Name of the index-only table, if any, to which the
 overflow or mapping table entry belongs
 STATUS Status of the table will be UNUSABLE if a previous
 DROP TABLE operation failed,VALID otherwise
 PCT_FREE Minimum percentage of free space in a block
 PCT_USED Minimum percentage of used space in a block
 INI_TRANS Initial number of transactions
 MAX_TRANS Maximum number of transactions
 INITIAL_EXTENT Size of the initial extent in bytes
 NEXT_EXTENT Size of secondary extents in bytes
 MIN_EXTENTS Minimum number of extents allowed in the segment
 MAX_EXTENTS Maximum number of extents allowed in the segment
 PCT_INCREASE Percentage increase in extent size
 FREELISTS Number of process freelists allocated in this
 segment
 FREELIST_GROUPS Number of freelist groups allocated in this
 segment
 LOGGING Logging attribute
 BACKED_UP Has table been backed up since last modification?
 NUM_ROWS The number of rows in the table
 BLOCKS The number of used blocks in the table
 EMPTY_BLOCKS The number of empty (never used) blocks in the
 table
 AVG_SPACE The average available free space in the table
 CHAIN_CNT The number of chained rows in the table
 AVG_ROW_LEN The average row length, including row overhead
 AVG_SPACE_FREELIST_BLOC The average freespace of all blocks on a freelist
 NUM_FREELIST_BLOCKS The number of blocks on the freelist
 DEGREE The number of threads per instance for scanning
 the table
 INSTANCES The number of instances across which the table is
 to be scanned
 CACHE Whether the table is to be cached in the buffer
 cache
 TABLE_LOCK Whether table locking is enabled or disabled
 SAMPLE_SIZE The sample size used in analyzing this table
 LAST_ANALYZED The date of the most recent time this table was
 analyzed
 PARTITIONED Is this table partitioned? YES or NO
 IOT_TYPE If index-only table, then IOT_TYPE is IOT or
 IOT_OVERFLOW or IOT_MAPPING else NULL
 TEMPORARY Can the current session only see data that it
 place in this object itself?
 SECONDARY Is this table object created as part of icreate
 for domain indexes?
 NESTED Is the table a nested table?
 BUFFER_POOL The default buffer pool to be used for table
 blocks
 FLASH_CACHE The default flash cache hint to be used for table
 blocks
 CELL_FLASH_CACHE The default cell flash cache hint to be used for
 table blocks
 ROW_MOVEMENT Whether partitioned row movement is enabled or
 disabled
 GLOBAL_STATS Are the statistics calculated without merging
 underlying partitions?
 USER_STATS Were the statistics entered directly by the user?
 DURATION If temporary table, then duration is sys$session
 or sys$transaction else NULL
 SKIP_CORRUPT Whether skip corrupt blocks is enabled or disabled
 MONITORING Should we keep track of the amount of
 modification?
 CLUSTER_OWNER Owner of the cluster, if any, to which the table
 belongs
 DEPENDENCIES Should we keep track of row level dependencies?
 COMPRESSION Whether table compression is enabled or not
 COMPRESS_FOR Compress what kind of operations
 DROPPED Whether table is dropped and is in Recycle Bin
 READ_ONLY Whether table is read only or not
 SEGMENT_CREATED Whether the table segment is created or not
 RESULT_CACHE The result cache mode annotation for the table
 CLUSTERING Whether table has clustering clause or not
 ACTIVITY_TRACKING ILM activity tracking mode
 DML_TIMESTAMP ILM row modification or creation timestamp
 tracking mode
 HAS_IDENTITY Whether the table has an identity column
 CONTAINER_DATA An indicator of whether the table contains
 Container-specific data
 INMEMORY Whether in-memory is enabled or not
 INMEMORY_PRIORITY User defined priority in which in-memory column
 store object is loaded
 INMEMORY_DISTRIBUTE How the in-memory columnar store object is
 distributed

For table_rows, although num_rows is the closest equivalent, it does not always provide you with accurate details unless table statistics are gathered regularly. It's always preferable to use select count(*) in most cases.