View the compaction process

After a compaction is initiated, you can view the progress of the compaction in the corresponding view.

You can view the progress of the compaction in an internal table. The time required for a compaction depends on the amount of data changes between two consecutive compactions. A large amount of data changes between two consecutive compactions results in a longer compaction time. The number of threads for compaction and the cluster pressure during compaction also affect the time required for compaction.

View the major compaction process of all tenants as a sys tenant

The sys tenant can observe the major compaction process of all tenants by querying the oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION and oceanbase.CDB_OB_MAJOR_COMPACTION views.

1. Log in to the sys tenant of the cluster as the root user.

2. Execute the following statement to view the compaction progress.

   • View the major compaction process of all tenants in each zone.

     obclient> SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION;
     

     The query result is as follows:

     +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
     | TENANT_ID | ZONE  | BROADCAST_SCN       | LAST_SCN            | LAST_FINISH_TIME           | START_TIME                 | STATUS |
     +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
     |         1 | zone1 | 1748368801021617000 | 1748368801021617000 | 2025-05-28 02:04:15.351286 | 2025-05-28 02:00:01.051592 | IDLE   |
     |      1001 | zone1 | 1748368802924607000 | 1748368802924607000 | 2025-05-28 02:02:16.412661 | 2025-05-28 02:00:02.965810 | IDLE   |
     |      1002 | zone1 | 1748368802035321000 | 1748368802035321000 | 2025-05-28 02:03:25.019173 | 2025-05-28 02:00:02.090687 | IDLE   |
     |      1003 | zone1 | 1748368802977311000 | 1748368802977311000 | 2025-05-28 02:02:46.565036 | 2025-05-28 02:00:03.009192 | IDLE   |
     |      1004 | zone1 | 1748368801786323000 | 1748368801786323000 | 2025-05-28 02:03:54.629630 | 2025-05-28 02:00:01.820863 | IDLE   |
     +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
     5 rows in set
     

     Observe the status column in the execution result. The following table describes some columns.

     Column	Description
     TENANT_ID	Tenant ID.
     ZONE	Zone name.
     BROADCAST_SCN	Broadcast major compaction version number.
     LAST_SCN	Major compaction version number of the previous major compaction.
     LAST_FINISH_TIME	End time of the previous major compaction.
     START_TIME	Start time of the current major compaction.
     STATUS	Major compaction status: IDLE: not in major compaction. COMPACTING: major compaction in progress. VERIFYING: verifying checksum.

   • View the global major compaction information of all tenants.

     obclient> SELECT * FROM oceanbase.CDB_OB_MAJOR_COMPACTION;
     

     The query result is as follows:

     +-----------+---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+--------+----------+--------------+------+
     | TENANT_ID | FROZEN_SCN          | FROZEN_TIME                | GLOBAL_BROADCAST_SCN | LAST_SCN            | LAST_FINISH_TIME           | START_TIME                 | STATUS | IS_ERROR | IS_SUSPENDED | INFO |
     +-----------+---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+--------+----------+--------------+------+
     |         1 | 1748368801021617000 | 2025-05-28 02:00:01.021617 |  1748368801021617000 | 1748368801021617000 | 2025-05-28 02:04:15.380802 | 2025-05-28 02:00:01.042282 | IDLE   | NO       | NO           |      |
     |      1001 | 1748368802924607000 | 2025-05-28 02:00:02.924607 |  1748368802924607000 | 1748368802924607000 | 2025-05-28 02:02:16.440351 | 2025-05-28 02:00:02.957000 | IDLE   | NO       | NO           |      |
     |      1002 | 1748368802035321000 | 2025-05-28 02:00:02.035321 |  1748368802035321000 | 1748368802035321000 | 2025-05-28 02:03:25.035666 | 2025-05-28 02:00:02.075224 | IDLE   | NO       | NO           |      |
     |      1003 | 1748368802977311000 | 2025-05-28 02:00:02.977311 |  1748368802977311000 | 1748368802977311000 | 2025-05-28 02:02:46.592681 | 2025-05-28 02:00:03.003544 | IDLE   | NO       | NO           |      |
     |      1004 | 1748368801786323000 | 2025-05-28 02:00:01.786323 |  1748368801786323000 | 1748368801786323000 | 2025-05-28 02:03:54.645389 | 2025-05-28 02:00:01.809475 | IDLE   | NO       | NO           |      |
     +-----------+---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+--------+----------+--------------+------+
     5 rows in set
     

     Observe the status column in the execution result. The following table describes some parameters.

     Column	Description
     TENANT_ID	Tenant ID.
     FROZEN_SCN	Major compaction version number of the last major compaction.
     FROZEN_TIME	Readable time type of FROZEN_SCN.
     GLOBAL_BROADCAST_SCN	Global broadcast major compaction version.
     LAST_SCN	Last completed major compaction version.
     LAST_FINISH_TIME	End time of the previous major compaction.
     START_TIME	Start time of the current major compaction.
     STATUS	Major compaction status: IDLE: not in major compaction. COMPACTING: major compaction in progress. VERIFYING: verifying checksum.
     IS_ERROR	YES: errors occurred in the major compaction. NO: no error in the major compaction.
     IS_SUSPENDED	YES: the major compaction is suspended. NO: the major compaction is not suspended.
     INFO	Displays the major compaction information.

View the major compaction process of the user tenant

User tenants can query the DBA_OB_ZONE_MAJOR_COMPACTION and DBA_OB_MAJOR_COMPACTION views to observe the major compaction process in the tenant.

1. The tenant administrator of the user tenant logs in to the database.

2. Execute the following statement to view the compaction progress.

   MySQL mode

   Oracle mode

   • View the major compaction process in each zone of the user tenant.

     obclient> SELECT * FROM oceanbase.DBA_OB_ZONE_MAJOR_COMPACTION;
     

     The query result is as follows:

     +-------+---------------------+---------------------+----------------------------+----------------------------+--------+
     | ZONE  | BROADCAST_SCN       | LAST_SCN            | LAST_FINISH_TIME           | START_TIME                 | STATUS |
     +-------+---------------------+---------------------+----------------------------+----------------------------+--------+
     | zone1 | 1748455202538454000 | 1748455202538454000 | 2025-05-29 02:04:34.174642 | 2025-05-29 02:00:02.679713 | IDLE   |
     +-------+---------------------+---------------------+----------------------------+----------------------------+--------+
     1 row in set
     

     Observe the status column in the execution result. The following table describes some parameters.

     Parameter	Description
     ZONE	The name of the zone.
     BROADCAST_SCN	The broadcasted major compaction version.
     LAST_SCN	The major compaction version of the previous major compaction.
     LAST_FINISH_TIME	The end time of the previous major compaction.
     START_TIME	The start time of the current major compaction.
     STATUS	The major compaction status. Valid values: IDLE: not in major compaction. COMPACTING: major compaction in progress. VERIFYING: verifying checksum.

   • View the global information about the major compaction in the user tenant.

     obclient> SELECT * FROM oceanbase.DBA_OB_MAJOR_COMPACTION;
     

     The query result is as follows:

     +---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+--------+----------+--------------+------+
     | FROZEN_SCN          | FROZEN_TIME                | GLOBAL_BROADCAST_SCN | LAST_SCN            | LAST_FINISH_TIME           | START_TIME                 | STATUS | IS_ERROR | IS_SUSPENDED | INFO |
     +---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+--------+----------+--------------+------+
     | 1748455202538454000 | 2025-05-29 02:00:02.538454 |  1748455202538454000 | 1748455202538454000 | 2025-05-29 02:04:34.171690 | 2025-05-29 02:00:02.670090 | IDLE   | NO       | NO           |      |
     +---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+--------+----------+--------------+------+
     1 row in set
     

     Observe the status column in the execution result. The following table describes some parameters.

     Parameter	Description
     FROZEN_SCN	The major compaction version of the last major compaction.
     FROZEN_TIME	The readable time type of FROZEN_SCN.
     GLOBAL_BROADCAST_SCN	The global broadcasted major compaction version.
     LAST_SCN	The major compaction version of the last completed major compaction.
     LAST_FINISH_TIME	The end time of the previous major compaction.
     START_TIME	The start time of the current major compaction.
     STATUS	The major compaction status. Valid values: IDLE: not in major compaction. COMPACTING: major compaction in progress. VERIFYING: verifying checksum.
     IS_ERROR	YES: major compaction error exists. NO: no error in major compaction.
     IS_SUSPENDED	YES: major compaction is suspended. NO: major compaction is not suspended.
     INFO	Displays the major compaction information.

   • View the major compaction process in each zone of the user tenant.

     obclient> SELECT * FROM SYS.DBA_OB_ZONE_MAJOR_COMPACTION;
     

     The query result is as follows:

     +-------+---------------------+----------+------------------+------------------+------------+
     | ZONE  | BROADCAST_SCN       | LAST_SCN | LAST_FINISH_TIME | START_TIME       | STATUS     |
     +-------+---------------------+----------+------------------+------------------+------------+
     | zone1 | 1748487596765126000 |        1 |                0 | 1748487596921347 | COMPACTING |
     +-------+---------------------+----------+------------------+------------------+------------+
     1 row in set
     

     Observe the status column in the execution result. The following table describes some parameters.

     Parameter	Description
     ZONE	The name of the zone.
     BROADCAST_SCN	The broadcasted major compaction version.
     LAST_SCN	The major compaction version of the previous major compaction.
     LAST_FINISH_TIME	The end time of the previous major compaction.
     START_TIME	The start time of the current major compaction.
     STATUS	The major compaction status. Valid values: IDLE: not in major compaction. COMPACTING: major compaction in progress. VERIFYING: verifying checksum.

   • View the global information about the major compaction in the user tenant.

     obclient> SELECT * FROM SYS.DBA_OB_MAJOR_COMPACTION;
     

     The query result is as follows:

     +---------------------+----------------------+----------+------------------+------------------+------------+----------+--------------+------+
     | FROZEN_SCN          | GLOBAL_BROADCAST_SCN | LAST_SCN | LAST_FINISH_TIME | START_TIME       | STATUS     | IS_ERROR | IS_SUSPENDED | INFO |
     +---------------------+----------------------+----------+------------------+------------------+------------+----------+--------------+------+
     | 1748487596765126000 |  1748487596765126000 |        1 |                0 | 1748487596900693 | COMPACTING | NO       | NO           | NULL |
     +---------------------+----------------------+----------+------------------+------------------+------------+----------+--------------+------+
     1 row in set
     

     Observe the status column in the execution result. The following table describes some parameters.

     Parameter	Description
     FROZEN_SCN	The major compaction version of the last major compaction.
     GLOBAL_BROADCAST_SCN	The global broadcasted major compaction version.
     LAST_SCN	The major compaction version of the last completed major compaction.
     LAST_FINISH_TIME	The end time of the previous major compaction.
     START_TIME	The start time of the current major compaction.
     STATUS	The major compaction status. Valid values: IDLE: not in major compaction. COMPACTING: major compaction in progress. VERIFYING: verifying checksum.
     IS_ERROR	YES: major compaction error exists. NO: no error in major compaction.
     IS_SUSPENDED	YES: major compaction is suspended. NO: major compaction is not suspended.
     INFO	Displays the major compaction information.