1.CurrentActivity.sql

set lines 150
set pages 999
clear col

set termout off
set trimout on
set trimspool on

REM
REM Current transactions
REM
REM Will show only last transaction by a user
REM
REM May need to use 786472.1 for better picture
REM of activity

connect / as sysdba
alter session set nls_date_format=’dd-Mon-yyyy hh24:mi’;

col username format a10 wrapped heading “User”
col name format a22 wrapped heading “Undo Segment Name”
col xidusn heading “Undo|Seg #”
col xidslot heading “Undo|Slot #”
col xidsqn heading “Undo|Seq #”
col ubafil heading “File #”
col ubablk heading “Block #”
col start_time format a10 word_wrapped heading “Started”
col status format a8 heading “Status”
col blk format 999,999,999 heading “KBytes”
col used_urec heading “Rows”

spool undoactivity.out

prompt
prompt ############## RUNTIME ##############
prompt

col rdate head “Run Time”

select sysdate rdate from dual;

prompt
prompt ############## Current Uncommitted Transactions ##############
prompt

select start_time, username, r.name,
ubafil, ubablk, t.status, (used_ublk*p.value)/1024 blk, used_urec
from v$transaction t, v$rollname r, v$session s, v$parameter p
where xidusn=usn
and s.saddr=t.ses_addr
and p.name=’db_block_size’
order by 1;
spool off

set termout on
set trimout off
set trimspool off
clear col

2.LobData.sql

REM List table and column information for LOBs for a specific user
REM
REM UNDO handling with LOBs is not designed for frequent updates
REM Frequent updates are best handled with PCTVERSION at 100
REM This means you must have a lot of space available in the LOB
REM tablespace as all UNDO will be maintained over time.
REM
REM Using RETENTION does not work as expected
REM It is set to UNDO_RETENTION at the time of the creation of the
REM object. It does not change over time as UNDO_RETENTION
REM or auto-tuned undo retention changes.

set pages 999
set lines 110

spool lobdata.out

col column_name format a25 head “Column”
col table_name format a25 head “Table”
col tablespace_name format a25 head “Tablespace”
col pctversion format 999 head “PCTVersion %”
col segment_space_management format a30 head “Space|Mngmnt”
col retention format 999,999,999 head “Retention”

select l.table_name, l.column_name, l.tablespace_name, l.pctversion, l.retention,
t.segment_space_management
from dba_lobs l, dba_tablespaces t
where owner=upper(‘&USER’)
and l.tablespace_name = t.tablespace_name
/

spool off