/*
 * Title  : last_acc_tab.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 18-Jul-2023
 * Release: 1.0.0
 * Purpose: Shows the last accessed tables.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 180
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

column rownum		format 9999		heading "#"
column own		format a20		heading "Owner"
column tab		format a25		heading "Table"
column part		format a30		heading "Partition"
column inserts		format 9,999,999,999	heading "Iserts"
column updates		format 9,999,999,999	heading "Updates"
column deletes		format 9,999,999,999	heading "Deletes"
column truncated	format a10		heading "Truncated?"
column timestamp	format a16		heading "Timestamp"

accept p_days number prompt "Enter no of days: "

spool &&rpt_dir/last_acc_tab.rpt

select
	rownum,
	own,
	tab,
	part,
	inserts, 
	updates,
	deletes,
	truncated,
	timestamp
from 
	(
		select
			table_owner				own,
			table_name				tab,
			partition_name				part,
			inserts					inserts,
			updates					updates,
			deletes					deletes,
			truncated				truncated,
			to_char(timestamp,'YYYY-MM-DD HH24:MI')	timestamp
		from
			dba_tab_modifications
		where
			timestamp > sysdate-&&p_days
		and	table_owner not in (
				'SYS',
				'SYSTEM',
				'AUDSYS',
				'GSMADMIN_INTERNAL'
				,'GSMCATUSER'
				,'GSMUSER'
				,'OJVMSYS'
				,'SYSBACKUP'
				,'SYSKM'
				,'XS$NULL'
				,'SYSDG',
				'DBSNMP',
				'OUTLN',
				'MDSYS',
				'ORDSYS',
				'ORDPLUGINS',
				'CTXSYS',
				'DSSYS',
				'WKPROXY',
				'WKSYS',
				'WMSYS',
				'XDB',
				'ANONYMOUS',
				'ODM',
				'ODM_MTR',
				'OLAPSYS',
				'TRACESVR',
				'PERFSTAT',
				'REPADMIN',
				'EXFSYS',
				'DMSYS',
				'SYSMAN',
				'DIP',
				'TSMSYS',
				'DVF',
				'DVSYS',
				'WKUSER',
				'WK_TEST',
				'LBACSYS',
				'SI_INFORMTN_SCHEMA',
				'APPQOSSYS',
				'ORACLE_OCM',
				'RMAN',
				'GGS',
				'ORACLE'
			)
		order by 
			table_owner,
			table_name
	)
order by
	1;

spool off

clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off