skript
****************************************************************
* Start Skript *
****************************************************************
CREATE TABLESPACE rbs01
DATAFILE ‚rbs01.dbf‘ – deine Pfadangabe nicht vergessen
SIZE 50M
DEFAULT STORAGE (INITIAL 200k
NEXT 200k
MINEXTENTS 20
PCTINCREASE 0)
/
CREATE ROLLBACK SEGMENT rbsA1
TABLESPACE rbs01
STORAGE (OPTIMAL 4000k)
/
CREATE ROLLBACK SEGMENT rbsA2
TABLESPACE rbs01
STORAGE (OPTIMAL 4000k)
/
CREATE ROLLBACK SEGMENT rbsA3
TABLESPACE rbs01
STORAGE (OPTIMAL 4000k)
/
CREATE ROLLBACK SEGMENT rbsA4
TABLESPACE rbs01
STORAGE (OPTIMAL 4000k)
/
CREATE ROLLBACK SEGMENT rbsA5
TABLESPACE rbs01
STORAGE (OPTIMAL 4000k)
/
ALTER ROLLBACK SEGMENT rbsA1 ONLINE;
ALTER ROLLBACK SEGMENT rbsA2 ONLINE;
ALTER ROLLBACK SEGMENT rbsA3 ONLINE;
ALTER ROLLBACK SEGMENT rbsA4 ONLINE;
ALTER ROLLBACK SEGMENT rbsA5 ONLINE;
****************************************************************
* Ende Skript *
****************************************************************
Die RBS sind jetzt angelegt und online
(SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs).
Um sie auch nach einem Neustart der Instanz verfügbar zu haben, musst du sie nun noch in deiner Parameterdatei (init.ora) eintragen.
ROLLBACK_SEGMENTS=(rbsA1,rbsA2,rbsA3,rbsA4,rbsA5)
Aber Vorsicht : Den bestehenden Eintrag nicht überschreiben - nur ergänzen.
Probleme, Fragen und eventuelle Fehlermeldungen kannst du mir auch direkt mailen.
Gruß der Janus
PS: Normalerweise empfiehlt man bei einer (produktiven) OLTP-DB 1 RBS pro 10 User oder max. 4 parallele Transaktionen pro RBS.
Daher solltest du vielleicht noch mehr RBS anlegen.
Nun noch 2 Skripte zum Thema:
****************************************************************
* Start Skript *
****************************************************************
–
– This script provides TRANSACTION INFORMATION in a ROLLBACK SEGMENT for a
– particular OBJECT (Query v$locked_object, v$transaction).
– DESCRIPTION:
– OS USER = OS User
– DB USER = DB User
– SCHEMA = Object Owner
– OBJECT Name = Name of Object
– TYPE = Type of Object
– RBS = Rollback Segment Being Used
– # of Records = # of Records for this object in this transaction.
SELECT substr(a.os_user_name,1,8) „OS User“ ,
substr(a.oracle_username,1,8) „DB User“ ,
substr(b.owner,1,8) „Schema“ ,
substr(b.object_name,1,20) „Object Name“ ,
substr(b.object_type,1,10) „Type“ ,
substr(c.segment_name,1,5) „RBS“
FROM v$locked_object a,
dba_objects b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE a.object_id = b.object_id
AND a.xidusn = c.segment_id
AND a.xidusn = d.xidusn
AND a.xidslot = d.xidslot
AND d.addr = e.taddr
/
–
– The following is a short script which returns the username, OS login name
– and terminal from the dynamic performance tables for any user with a lock
– on a rollback segment.
COLUMN rr HEADING ‚RB Segment‘ FORMAT a18
COLUMN us HEADING ‚Username‘ FORMAT a15
COLUMN os HEADING ‚OS User‘ FORMAT a10
COLUMN te HEADING ‚Terminal‘ FORMAT a10
SELECT r.name rr,
nvl(s.username,‚no transaction‘) us,
s.osuser os,
s.terminal te
FROM v$lock l, v$session s,v$rollname r
WHERE l.sid = s.sid(+)
AND trunc(l.id1/65536) = r.usn
AND l.type = ‚TX‘
AND l.lmode = 6
ORDER BY r.name
/
–
– The following script provides detailed information about rollback segments
– including: Segment Name, Owner, Tablespace, Header file and status.
COLUMN nm FORMAT a7 HEADING ‚Name‘ trunc
COLUMN ex FORMAT 999 HEADING ‚NrEx‘
COLUMN rs FORMAT a7 HEADING ‚Size‘
COLUMN init FORMAT 999,999 HEADING ‚Init‘
COLUMN next FORMAT 999,999 HEADING ‚Next‘
COLUMN mi FORMAT 999 HEADING ‚MinE‘
COLUMN ma FORMAT 999 HEADING ‚MaxE‘
COLUMN op FORMAT 99,999,999 HEADING ‚Opt size‘
COLUMN pct FORMAT 990 HEADING ‚PctI‘
COLUMN st FORMAT a4 HEADING ‚Stat‘
COLUMN sn FORMAT a15 HEADING ‚Segm Name‘
COLUMN ts FORMAT a12 HEADING ‚In TabSpace‘
COLUMN fn FORMAT a75 HEADING ‚File containing header of rbs‘
COLUMN ow FORMAT a4 HEADING ‚Ownr‘
PROMPT
PROMPT All Rollback Segments
SELECT segment_name sn,
decode(owner,‚PUBLIC‘,‚Publ‘,‚Priv‘) ow,
tablespace_name ts, name fn
FROM sys.dba_rollback_segs d,
v$datafile f
WHERE d.file_id = f.file#;
PROMPT
PROMPT Online Rollback Segments:
SELECT d.segment_name nm,
s.extents ex,
(s.rssize/1024)||‚K‘ rs,
d.initial_extent init,
d.next_extent next,
d.pct_increase pct,
d.min_extents mi,
d.max_extents ma,
optsize op,
decode(d.status,‚ONLINE‘,‚OnL‘,‚OFFLINE‘,‚OffL‘) st
FROM v$rollname n,
v$rollstat s,
sys.dba_rollback_segs d
WHERE n.usn = s.usn
AND d.segment_name = n.name(+)
/
****************************************************************
* Ende Skript *
****************************************************************
