180 lines
6.5 KiB
SQL
180 lines
6.5 KiB
SQL
--------------------------------------------------------------------------------
|
|
--
|
|
-- File name: lastchanged.sql
|
|
-- Purpose: Detect when a datablock in table was last changed
|
|
--
|
|
-- Author: Tanel Poder
|
|
-- Copyright: (c) http://www.tanelpoder.com
|
|
--
|
|
-- Usage: @lastchanged <table_name> <filter_conditions>
|
|
--
|
|
-- @lastchanged obj$ name='MYTABLE'
|
|
-- @lastchanged emp empno=100
|
|
-- @lastchanged scott.emp rowid='AAAMlsAAEAAAAAfAAJ'
|
|
-- @lastchanged emp salary>100000
|
|
-- @lastchanged trades "trader_id=123 and counterparty_id=456"
|
|
--
|
|
-- Other: This script uses the ORA_ROWSCN pseudocolumn which relies on the
|
|
-- last change SCN in datablock header if ROWDEPENDENCIES are not
|
|
-- enabled. Thus without rowdependencies the last change information
|
|
-- is known at BLOCK LEVEL, not row level.
|
|
--
|
|
-- If someone has deleted the row, then this script will not find it
|
|
-- thus report nothing for that row. In such case you can dump the
|
|
-- datablock where this row used to be if you know its address
|
|
-- (taken from a backup for example), read the last change SCN from
|
|
-- block header and use smon_scn_time or v$loghistory to map it to
|
|
-- real time.
|
|
--
|
|
-- Some things like delayed block cleanout or ALTER TABLE MOVE
|
|
-- or ALTER TABLE SHRINK SPACE will alter the last update SCN so
|
|
-- you may get false positives in these cases.
|
|
--
|
|
--------------------------------------------------------------------------------
|
|
|
|
PROMPT
|
|
PROMPT -- LastChanged.sql v1.0 by Tanel Poder ( http://www.tanelpoder.com )
|
|
PROMPT
|
|
|
|
PROMPT Running this query:
|
|
PROMPT
|
|
PROMPT . select MAX(ora_rowscn)
|
|
PROMPT . from &1
|
|
PROMPT . where &2;;
|
|
|
|
PROMPT
|
|
|
|
@@saveset
|
|
SET FEEDBACK OFF
|
|
|
|
VAR max_date VARCHAR2(50)
|
|
|
|
BEGIN
|
|
SELECT TO_CHAR(SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)), 'YYYY-MM-DD HH24:MI:SS') INTO :max_date
|
|
|
|
FROM &1
|
|
WHERE &2;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN NULL;
|
|
END;
|
|
/
|
|
|
|
|
|
COL runme NOPRINT NEW_VALUE runme
|
|
|
|
SELECT
|
|
'scn_to_timestamp ' data_source
|
|
, CASE
|
|
WHEN :max_date IS NULL THEN 'SCN_TO_TIMESTAMP couldn''t convert SCN to time. See next section below'
|
|
ELSE :max_date
|
|
END last_changed
|
|
, CASE WHEN :max_date IS NULL THEN '--' ELSE '' END runme
|
|
FROM dual
|
|
/
|
|
|
|
COL runme CLEAR
|
|
|
|
|
|
WITH
|
|
sq_smon_scn_time AS (
|
|
SELECT * FROM sys.smon_scn_time
|
|
),
|
|
sq_loghistory AS (
|
|
SELECT * FROM v$log_history
|
|
),
|
|
sq_maxscn AS (
|
|
SELECT
|
|
MAX(ORA_ROWSCN) max_scn
|
|
FROM &1
|
|
WHERE &2
|
|
),
|
|
sq_smon AS (
|
|
SELECT
|
|
'sys.smon_scn_time' source
|
|
, MIN(t1.time_dp) first_known_change_after
|
|
, MAX(t2.time_dp) last_known_change_before
|
|
FROM
|
|
sq_smon_scn_time t1
|
|
, sq_smon_scn_time t2
|
|
WHERE
|
|
(t1.scn_wrp * (POWER(2,32)-1) + t1.scn_bas) > (SELECT max_scn FROM sq_maxscn)
|
|
AND (t2.scn_wrp * (POWER(2,32)-1) + t2.scn_bas) < (SELECT max_scn FROM sq_maxscn)
|
|
),
|
|
sq_log AS (
|
|
SELECT
|
|
'v$log_history' source
|
|
, MIN(t1.first_time) first_known_change_after
|
|
, MAX(t2.first_time) last_known_change_before
|
|
FROM
|
|
sq_loghistory t1
|
|
, sq_loghistory t2
|
|
WHERE
|
|
(t1.first_change#) > (SELECT max_scn FROM sq_maxscn)
|
|
AND (t2.first_change#) < (SELECT max_scn FROM sq_maxscn)
|
|
)
|
|
SELECT
|
|
source data_source
|
|
, CASE WHEN last_changed IS NULL THEN 'No matching rows found. Adjust your filter condition' ELSE last_changed END last_changed
|
|
FROM (
|
|
SELECT
|
|
source
|
|
, CASE
|
|
WHEN sq_smon.first_known_change_after IS NULL OR sq_smon.last_known_change_before IS NULL THEN
|
|
CASE WHEN (SELECT sq_maxscn.max_scn FROM sq_maxscn ) <
|
|
( SELECT MIN(scn_wrp * (POWER(2,32)-1) + scn_bas)
|
|
FROM sq_smon_scn_time )
|
|
THEN
|
|
(SELECT 'Before '||TO_CHAR(MIN(time_dp),'YYYY-MM-DD HH24:MI:SS')||
|
|
' (earlier than '||ROUND((SYSDATE - MIN(time_dp)))||' days ago)'
|
|
FROM sq_smon_scn_time)
|
|
WHEN (SELECT sq_maxscn.max_scn FROM sq_maxscn ) >
|
|
( SELECT MAX(scn_wrp * (POWER(2,32)-1) + scn_bas)
|
|
FROM sq_smon_scn_time )
|
|
THEN
|
|
(SELECT 'After '||TO_CHAR(MAX(time_dp),'YYYY-MM-DD HH24:MI:SS')||
|
|
' (between '||ROUND(((SYSDATE - MAX(time_dp))*24*60))||' minutes ago and now)'
|
|
FROM sq_smon_scn_time)
|
|
END
|
|
ELSE
|
|
'Between '||TO_CHAR(last_known_change_before, 'YYYY-MM-DD HH24:MI:SS')||
|
|
' and '||TO_CHAR(first_known_change_after, 'YYYY-MM-DD HH24:MI:SS')||
|
|
' ('||ROUND((first_known_change_after - last_known_change_before)*24*60) || ' minute range)'
|
|
END last_changed
|
|
FROM (
|
|
sq_smon
|
|
)
|
|
UNION ALL
|
|
SELECT
|
|
source data_source
|
|
, CASE
|
|
WHEN sq_log.first_known_change_after IS NULL OR sq_log.last_known_change_before IS NULL THEN
|
|
CASE WHEN (SELECT sq_maxscn.max_scn FROM sq_maxscn ) <
|
|
( SELECT MIN(first_change#) FROM sq_loghistory )
|
|
THEN
|
|
(SELECT 'Before '||TO_CHAR(MIN(first_time),'YYYY-MM-DD HH24:MI:SS')||
|
|
' (earlier than '||ROUND((SYSDATE - MIN(first_time)))||' days ago)'
|
|
FROM sq_loghistory)
|
|
WHEN (SELECT sq_maxscn.max_scn FROM sq_maxscn ) >
|
|
( SELECT MAX(first_change#) FROM sq_loghistory )
|
|
THEN
|
|
(SELECT 'After '||TO_CHAR(MAX(first_time),'YYYY-MM-DD HH24:MI:SS')||
|
|
' (between '||ROUND(((SYSDATE - MAX(first_time))*24*60))||' minutes ago and now)'
|
|
FROM sq_loghistory)
|
|
END
|
|
ELSE
|
|
'Between '||TO_CHAR(last_known_change_before, 'YYYY-MM-DD HH24:MI:SS')||
|
|
' and '||TO_CHAR(first_known_change_after, 'YYYY-MM-DD HH24:MI:SS')||
|
|
' ('||ROUND((first_known_change_after - last_known_change_before)*24*60) || ' minute range)'
|
|
END last_changed
|
|
FROM (
|
|
sq_log
|
|
)
|
|
)
|
|
&runme WHERE 1=0
|
|
/
|
|
|
|
SET HEADING OFF
|
|
SELECT COUNT(*)||' rows analyzed.' FROM &1 WHERE &2;
|
|
SET HEADING ON
|
|
|
|
@@loadset |