View the major compaction process

After a major compaction is initiated, you can view the compaction process through views.

You can view the major compaction process by using internal tables. The time required for a major compaction depends on the data changes between two major compactions. A large amount of data changes between two major compactions results in a longer major compaction time. The number of threads for the major compaction and the cluster pressure during the major compaction also affect the major compaction time.

View the major compaction process of all tenants in the sys tenant

The sys tenant can query the oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION and oceanbase.CDB_OB_MAJOR_COMPACTION views to view the major compaction information of all tenants.

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.
     FROZEN_TIME	Readable time type of FROZEN_SCN.
     GLOBAL_BROADCAST_SCN	Global 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.
     IS_ERROR	YES: An error occurred during the major compaction. NO: No error occurred during 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 current tenant

The users of a user tenant can observe the major compaction process of the tenant by querying the DBA_OB_ZONE_MAJOR_COMPACTION and DBA_OB_MAJOR_COMPACTION views.

1. Log in to the database as the tenant administrator of the user tenant.

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

   MySQL mode

   Oracle mode

   • View the major compaction process of each zone in the current 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 last major compaction.
     LAST_FINISH_TIME	The end time of the last major compaction.
     START_TIME	The start time of the current major compaction.
     STATUS	The major compaction status. IDLE: The major compaction is not in progress. COMPACTING: The major compaction is in progress. VERIFYING: The checksum is being verified.

   • View the global information about the major compaction of the current 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 globally broadcasted major compaction version.
     LAST_SCN	The major compaction version of the last completed major compaction.
     LAST_FINISH_TIME	The end time of the last major compaction.
     START_TIME	The start time of the current major compaction.
     STATUS	The major compaction status. IDLE: The major compaction is not in progress. COMPACTING: The major compaction is in progress. VERIFYING: The checksum is being verified.
     IS_ERROR	YES: An error occurred in the major compaction. NO: No error occurred 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 each zone in the current 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 last major compaction.
     LAST_FINISH_TIME	The end time of the last major compaction.
     START_TIME	The start time of the current major compaction.
     STATUS	The major compaction status. IDLE: The major compaction is not in progress. COMPACTING: The major compaction is in progress. VERIFYING: The checksum is being verified.

   • View the global information about the major compaction of the current 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 globally broadcasted major compaction version.
     LAST_SCN	The major compaction version of the last completed major compaction.
     LAST_FINISH_TIME	The end time of the last major compaction.
     START_TIME	The start time of the current major compaction.
     STATUS	The major compaction status. IDLE: The major compaction is not in progress. COMPACTING: The major compaction is in progress. VERIFYING: The checksum is being verified.
     IS_ERROR	YES: An error occurred in the major compaction. NO: No error occurred 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.