Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The DBA_TAB_MODIFICATIONS view displays the modification history of all tables of the database since the table statistics were collected the last time.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TABLE_OWNER | VARCHAR2(128) | NO | The database to which the modified table belongs. |
| 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 statistic collection operation. |
| UPDATES | NUMBER | NO | The approximate number of updates since the last statistic collection operation. |
| DELETES | NUMBER | NO | The approximate number of deletions since the last statistic collection operation. |
| 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 partition and subpartition segments that have been dropped since the last analysis. |
Sample query
Query the modifications made to the T_SUBPART table since the last statistics collection.
obclient [SYS]> SELECT * FROM SYS.DBA_TAB_MODIFICATIONS WHERE TABLE_NAME='T_SUBPART';
The query result is as follows:
+-------------+------------+----------------+-------------------+---------+---------+---------+-----------+-----------+---------------+
| TABLE_OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | INSERTS | UPDATES | DELETES | TIMESTAMP | TRUNCATED | DROP_SEGMENTS |
+-------------+------------+----------------+-------------------+---------+---------+---------+-----------+-----------+---------------+
| SYS | T_SUBPART | NULL | P3SP2 | 0 | 40 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P1SP0 | 0 | 150 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P1SP1 | 0 | 150 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P1SP2 | 0 | 100 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P2SP3 | 0 | 60 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P1SP3 | 0 | 100 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P2SP1 | 0 | 90 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P2SP2 | 0 | 60 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P3SP1 | 0 | 60 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P2SP0 | 0 | 90 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P3SP3 | 0 | 40 | 0 | 22-MAR-25 | NULL | NULL |
| SYS | T_SUBPART | NULL | P3SP0 | 0 | 60 | 0 | 22-MAR-25 | NULL | NULL |
+-------------+------------+----------------+-------------------+---------+---------+---------+-----------+-----------+---------------+
12 rows in set
References
ALL_TAB_MODIFICATIONS: view the modifications made to all tables accessible to the current user since the last statistics collection.
USER_TAB_MODIFICATIONS: view the modifications made to all tables owned by the current user since the last statistics collection.