Oracle教程 2023-02-28

Oracle 视图 V$ACTIVITY_TABLE 官方解释，作用，如何使用详细说明

本站中文解释

Oracle视图V$ACTIVITY_TABLE 是以系统内部形式提供的，它提供的是一个系统应用活动会话的表活动信息，每一个活动会话又可以是一个登录会话，或者是一个占用表的操作会话。

用途：

1. 用于查看当前的系统会话的状态，可以用于检测死锁和检测对表操作的情况。

2. 可以用于找出某个表正在被占用的会话，及时释放表资源，释放表锁。

3. 常用于数据库审计，可以通过这张表查询出用户的活动情况来，比如当前活动的SQL语句，查出某个(或者多个)用户的活动。

使用方法：

1. 通过查询V$ACTIVITY_TABLE来查看当前系统的会话和活动情况。

2. 可以使用SELECT是得到表所有字段的信息，或者用SELECT…WHERE条件来统计某个(或者多个)会话的活动情况。

3. 如果需要查看会话正在执行的SQL语句，可以使用 V$SESSION 表来实现，可以使用V$SESSION. PREV_SQL_ADDR 字段来实现。

官方英文解释

V$ACTIVITY_TABLE describes the table activity snapshots that are currently being taken by the Object Activity Tracking System (OATS).

Each row in this view represents one in-progress activity snapshot and describes the table activity that has occurred so far during the snapshot interval.

Column	Datatype	Description
`TABLE_NAME`	`VARCHAR2(128)`	Name of the table for which the activity snapshot is being taken
`TABLE_OWNER`	`VARCHAR2(128)`	Owner of the table for which the activity snapshot is being taken
`USER_CLASS`	`VARCHAR2`	Reserved for future use
`SCANS`	`NUMBER`	Number of table scans
`LOADS`	`NUMBER`	Number of table loads
`LOAD_ROWS`	`NUMBER`	Number of rows that were loaded into the table
`INSERT_ROWS`	`NUMBER`	Number of rows that were inserted into the table
`DELETE_ROWS`	`NUMBER`	Number of rows that were deleted from the table
`UPDATE_ROWS`	`NUMBER`	Number of table rows that were updated
`TRUNCATES`	`NUMBER`	Number of table truncations
`TRUNCATED_ROWS`	`NUMBER`	Number of table rows that were deleted due to table truncations
`PARTITION_TRUNCATES`	`NUMBER`	Number of table partition or subpartition truncations
`PARTITION_TRUNCATED_ROWS`	`NUMBER`	Number of table rows that were deleted due to table partition or subpartition truncations
`PARTITION_CREATES`	`NUMBER`	Number of table partitions or subpartitions that were created
`PARTITION_DROPS`	`NUMBER`	Number of table partition or subpartition drops
`PARTITION_DROPS_ROWS`	`NUMBER`	Number of table rows that were dropped due to table partition or subpartition drops
`PARTITION_MOVES`	`NUMBER`	Number of table partition or subpartition moves
`PARTITION_MOVES_ROWS`	`NUMBER`	Number of table rows that were moved due to table partition or subpartition moves
`PARTITION_SPLITS`	`NUMBER`	Number of table partition or subpartition splits
`PARTITION_SPLITS_ROWS`	`NUMBER`	Number of table rows that were split due to table partition or subpartition splits
`PARTITION_MERGES`	`NUMBER`	Number of table partition or subpartition merges
`PARTITION_MERGES_ROWS`	`NUMBER`	Number of table rows that were merged due to table partition or subpartition merges
`PARTITION_COALESCES`	`NUMBER`	Number of table partition or subpartition coalesces
`PARTITION_COALESCE_ROWS`	`NUMBER`	Number of table rows that were redistributed due to table partition or subpartition coalesces
`PARTITION_EXCHANGES`	`NUMBER`	Number of table partition or subpartition exchanges
`PARTITION_EXCHANGES_ROWS`	`NUMBER`	Number of table rows that were exchanged due to table partition or subpartition exchanges
`CON_ID`	`NUMBER`	The ID of the container to which the data pertains. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data

Note:

The counts in this view are approximate in order to keep system overhead low. Also, there are database activities, such as statistics collection, query compilation, and query optimization, that may cause the counts to be different than expected or to vary slightly across successive queries.

Note:

This view does not contain information related to temporary tables or external tables.

Note:

This view is available starting with Oracle Database 21c.

本站中文解释

官方英文解释

相关推荐