Command Overview

The SHOW OPEN TABLES statement retrieves a list of all non-temporal tables currently opened within the database instance.

Important Distinction

When comparing this command to MySQL, note that while MySQL populates the Database field with the catalog name, openGauss fills this column with the schema name associated with the table.

Syntax Reference

SHOW OPEN TABLES
    [{FROM | IN} schema_name]
    [LIKE 'pattern' | WHERE expr]

Parameter Details

• schema_name: The target schema can be specified using either FROM or IN. These keywords are functionally equivalent in this context.
• Filtering Options: Use LIKE 'pattern' for wildcard searches or WHERE expr for logical conditions. Standard usage examples include:
  • Filtering by schema: show open tables where Database = 'my_schema';
  • Filtering by object: show open tables where "Table" = 'target_table';

Column Definitions

• Database: Identifies the schema owner of the displayed table.
• Table: Displays the specific name of the relation object.
• In_use: Tracks active locks and pending lock requests. For example, if one client holds a WRITE lock on a table, this value is 1. If another client waits for that same lock, the count rises to 2. A zero indicates the table is open but not current contested.
• Name_locked: Indicates weather an ACCESS EXCLUSIVE lock is currently applied to the table.

Practical Scenarios

Listing All Opened Objects

postgres=# SHOW OPEN TABLES;
 Database |      Table      | In_use | Name_locked
----------+-----------------+--------+-------------
 public   | users           |      0 |           0
 public   | orders          |      0 |           0
 pg_catalog | settings      |      0 |           0
(3 rows)

Filtering by Schema

postgres=# SHOW OPEN TABLES IN production_db;
       Database       |     Table      | In_use | Name_locked 
----------------------+----------------+--------+-------------
 production_db        | customer_list  |      0 |             0
 production_db        | transaction_log|      0 |             0
(2 rows)

Pattern Matching

postgres=# SHOW OPEN TABLES LIKE '%log%';
       Database       |     Table      | In_use | Name_locked 
----------------------+----------------+--------+-------------
 production_db        | audit_log      |      1 |             0
(1 row)

Conditional Queries

postgres=# SHOW OPEN TABLES WHERE "Table" = 'audit_log';
       Database       |     Table      | In_use | Name_locked 
----------------------+----------------+--------+-------------
 production_db        | audit_log      |      1 |             0
(1 row)

Lock State Verification

-- Acquire a read lock on a specific table
LOCK TABLE audit_log READ;

-- Check lock status (In_use > 0 confirms activity)
SHOW OPEN TABLES WHERE In_use > 0;
      Database       |    Table    | In_use | Name_locked 
---------------------+-------------+--------+-------------
 production_db       | audit_log   |      1 |             0
(1 row)

-- Release the lock
UNLOCK TABLES;
COMMIT;