Note
This view is available starting with V4.2.0.
Purpose
This view displays the number of DML operations performed on each table since the last statistics collection and whether the current statistics are stale.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the table. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table. |
| PARTITION_NAME | VARCHAR2(128) | YES | The name of the partition. If this column is NULL for a partitioned table, it indicates that the current record is aggregated at the table level. |
| SUBPARTITION_NAME | VARCHAR2(128) | YES | The name of the subpartition. If this column is NULL for a subpartitioned table, it indicates that the current record is aggregated at the table or partition level. |
| LAST_ANALYZED_ROWS | NUMBER | YES | The number of rows when the statistics were last collected. If this value is NULL, it means that the statistics have never been collected. |
| LAST_ANALYZED_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | YES | The time when the statistics were last collected. If this value is NULL, it means that the statistics have never been collected. |
| INSERTS | NUMBER | NO | The number of rows inserted since the last statistics collection. |
| UPDATES | NUMBER | NO | The number of rows updated since the last statistics collection. |
| DELETES | NUMBER | NO | The number of rows deleted since the last statistics collection. |
| STALE_PERCENT | NUMBER | NO | The percentage used to determine if the statistics are stale. |
| IS_STALE | VARCHAR2(3) | NO | Indicates whether the statistics are stale.
|
Sample query
Query the number of DML operations performed on the T_SUBPART table since the last statistics collection and whether the statistics are stale.
obclient [SYS]> SELECT * FROM SYS.DBA_OB_TABLE_STAT_STALE_INFO WHERE TABLE_NAME='T_SUBPART';
The query result is as follows:
+-------+------------+----------------+-------------------+--------------------+------------------------------+---------+---------+---------+---------------+----------+
| OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | LAST_ANALYZED_ROWS | LAST_ANALYZED_TIME | INSERTS | UPDATES | DELETES | STALE_PERCENT | IS_STALE |
+-------+------------+----------------+-------------------+--------------------+------------------------------+---------+---------+---------+---------------+----------+
| SYS | T_SUBPART | NULL | NULL | 1000 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | NULL | 500 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | NULL | 300 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | NULL | 200 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | P1SP0 | 150 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | P1SP1 | 150 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | P1SP2 | 100 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | P1SP3 | 100 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | P2SP0 | 90 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | P2SP1 | 90 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | P2SP2 | 60 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | P2SP3 | 60 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | P3SP0 | 60 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | P3SP1 | 60 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | P3SP2 | 40 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | P3SP3 | 40 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
+-------+------------+----------------+-------------------+--------------------+------------------------------+---------+---------+---------+---------------+----------+
16 rows in set