Undo tablespace management.
I have 3 UNDO tablespaces
TableSpace UsedMB FreeMBG TotalMB %Free
UNDOTBS1 23765 8760 32525 27
UNDOTBS2 12240 48 12288 0
UNDOTBS3 4260 768 5028 15
UNDOTBS2 is full.
1. Will a transaction that started in UNDOTBS2 move automatically to UNDOTBS1 or UNDOTBS3
2. How do i identify which program or script is running which is filling up all UNDO tablespace.
TableSpace UsedMB FreeMBG TotalMB %Free
UNDOTBS1 23765 8760 32525 27
UNDOTBS2 12240 48 12288 0
UNDOTBS3 4260 768 5028 15
UNDOTBS2 is full.
1. Will a transaction that started in UNDOTBS2 move automatically to UNDOTBS1 or UNDOTBS3
2. How do i identify which program or script is running which is filling up all UNDO tablespace.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If no active transaction is running then why is the space 100% consumed. The transaction that ended should have automatically reclaimed space from UNDOTBS2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Therefore all the below queries return no rows.
SELECT TO_CHAR(s.sid)||','||TO_CH
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';
SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK
FROM V$SESSION A, V$TRANSACTION B
WHERE A.SADDR=B.SES_ADDR;
SELECT sql_text, sid
FROM v$session a,
v$sql b
WHERE b.address = a.sql_address
AND b.hash_value = a.sql_hash_value
AND a.sid=x;