Oracle equivalent of information_schema.tables

Question

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 1

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

Answer 2

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.