Note
This view was introduced in V4.0.0.
Purpose
This view displays the modification information of tables that the current user owns since the last time statistics were collected for the tables.
Applicability
This view applies only to OceanBase Database in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TABLE_NAME | VARCHAR2(128) | NO | The name of the modified table. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the modified partition. |
| SUBPARTITION_NAME | VARCHAR2(128) | NO | The name of the modified subpartition. |
| INSERTS | NUMBER | NO | The approximate number of insert operations since the last statistics collection. |
| UPDATES | NUMBER | NO | The approximate number of update operations since the last statistics collection. |
| DELETES | NUMBER | NO | The approximate number of delete operations since the last statistics collection. |
| TIMESTAMP | DATE | NO | The time when the table was last modified. |
| TRUNCATED | VARCHAR2(3) | NO | Indicates whether the table has been truncated since the last analysis. |
| DROP_SEGMENTS | NUMBER | NO | The number of dropped partitions and subpartitions since the last analysis. |
Sample query
Query the modifications made to the T_SUBPART table since the last statistics collection on this table.
obclient [SYS]> SELECT * FROM SYS.USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_SUBPART';
The return result is as follows:
+------------+----------------+-------------------+---------+---------+---------+-----------+-----------+---------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | INSERTS | UPDATES | DELETES | TIMESTAMP | TRUNCATED | DROP_SEGMENTS |
+------------+----------------+-------------------+---------+---------+---------+-----------+-----------+---------------+
| T_SUBPART | NULL | P3SP2 | 40 | 40 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P1SP0 | 150 | 150 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P1SP1 | 150 | 150 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P1SP2 | 100 | 100 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P2SP3 | 60 | 60 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P1SP3 | 100 | 100 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P2SP1 | 90 | 90 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P2SP2 | 60 | 60 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P3SP1 | 61 | 60 | 0 | 08-APR-25 | NULL | NULL |
| T_SUBPART | NULL | P2SP0 | 90 | 90 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P3SP3 | 40 | 40 | 0 | 25-MAR-25 | NULL | NULL |
| T_SUBPART | NULL | P3SP0 | 60 | 60 | 0 | 25-MAR-25 | NULL | NULL |
+------------+----------------+-------------------+---------+---------+---------+-----------+-----------+---------------+
12 rows in set
References
DBA_TAB_MODIFICATIONS: view the modifications made to all tables since the last statistics collection in the current tenant.
ALL_TAB_MODIFICATIONS: view the modifications made to all tables accessible to the current user since the last statistics collection.