How_To_Monitor_Truncate

View All Public Scripts Login to Run Script

Script Name How_To_Monitor_Truncate
Visibility Unlisted - anyone with the share link can access
Description This script is a simple truncate and my intention is to know the following. - What tis the time truncate happened. - How to know the session which caused the truncate ? - Is there any way to capture all truncates in GV$ACTIVE_SESSION_HISTORY ?
Area SQL General / DDL
Contributor Oracle
Created Sunday March 11, 2018

Statement 1

create table emp2 ( emp_id NUMBER)

Table created.

Statement 2

insert into emp2 values(1002)

1 row(s) inserted.

Statement 3

select * from user_tab_modifications where table_name='EMP2'


TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
EMP2  -  - 1 0 0  - NO 0

TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	INSERTS	UPDATES	DELETES	TIMESTAMP	TRUNCATED	DROP_SEGMENTS
EMP2	-	-	1	0	0	-	NO	0

Statement 5

select * from user_tab_modifications where table_name='EMP2'


TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
EMP2  -  - 1 0 1  - YES 0

TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	INSERTS	UPDATES	DELETES	TIMESTAMP	TRUNCATED	DROP_SEGMENTS
EMP2	-	-	1	0	1	-	YES	0

Additional Information