showroll.sql

--
-- Title :       showroll.sql 
-- Description : To display all configured 
--               rollback segments. 
-- 
-- Usage/Notes : 
-- 
-- Copyright :   ABCdba.com 2002 
-- 
 
SET TERMOUT OFF 
STORE SET save_env.sql REPLACE 
SET TERMOUT ON 
 
CLEAR BREAKS 
CLEAR COLUMNS 
CLEAR COMPUTES 
 
COL seg FOR a10 HEA " Name " 
COL mine FOR a5 HEA " Min " 
COL maxe FOR a5 HEA " Max " 
 
COL tsp FOR a10 HEA "Tablespace" 
COL stat FOR a9 HEA " Status " 
COL owner FOR a5 HEA "Owner" 
 
COL iext FOR 9999.999 HEA " Initial " 
COL next FOR 999.999 HEA " Next " 
COL oext FOR a9 HEA " Optimal " 
 
SET PAGES 20 
SET WRAP ON 
 
SELECT 
LPAD( d.segment_name, 8 ) seg, 
LPAD( d.tablespace_name, 8 ) tsp, 
( d.initial_extent / 1048576 ) iext, 
( d.next_extent / 1048576 ) next, 
DECODE( d.min_extents, 32765, 'Unlmt', 
LPAD( d.min_extents, 4 ) ) mine, 
DECODE( d.max_extents, 32765, 'Unlmt', 
LPAD( d.max_extents, 4 ) ) maxe, 
DECODE( v.optsize, NULL, ' Not Set ', 
LPAD( ( v.optsize / 1048576 ), 7 ) ) oext, 
LPAD( INITCAP( LOWER( d.status ) ), 8 ) stat 
FROM 
dba_rollback_segs d, v$rollstat v 
WHERE 
d.segment_id = v.usn 
ORDER BY 
d.segment_id 
/ 
 
@save_env.sql 
host rm save_env.sql 
SET TERMOUT ON

© copyright 2001-2014 ABCdba.com | all rights reserved