Note
This view is introduced in V4.0.0.
Purpose
This view displays the modification information of tables that the current user can access since the last statistics collection on the tables.
Applicability
This view applies only to OceanBase Database running in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the modified table. |
| 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 insertions since the last statistics collection. |
| UPDATES | NUMBER | NO | The approximate number of updates since the last statistics collection. |
| DELETES | NUMBER | NO | The approximate number of deletions since the last statistics collection. |
| TIMESTAMP | DATE | NO | The time of the last modification of the table. |
| 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 for tables accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_TAB_MODIFICATIONS WHERE TABLE_NAME='T_SUBPART';
The return result is as follows:
+-------------+------------+----------------+-------------------+---------+---------+---------+-----------+-----------+---------------+
| TABLE_OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | INSERTS | UPDATES | DELETES | TIMESTAMP | TRUNCATED | DROP_SEGMENTS |
+-------------+------------+----------------+-------------------+---------+---------+---------+-----------+-----------+---------------+
| SYS | T_SUBPART | NULL | P3SP2 | 40 | 40 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P1SP0 | 150 | 150 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P1SP1 | 150 | 150 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P1SP2 | 100 | 100 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P2SP3 | 60 | 60 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P1SP3 | 100 | 100 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P2SP1 | 90 | 90 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P2SP2 | 60 | 60 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P3SP1 | 61 | 60 | 0 | 08-APR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P2SP0 | 90 | 90 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P3SP3 | 40 | 40 | 0 | 25-MAR-25 | NULL | NULL |
| SYS | 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.
USER_TAB_MODIFICATIONS: view the modifications made to all tables since the last statistics collection for tables owned by the current user.