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.
