Undo TableSpace Resize (Recreate&Drop)
Undo Tablespace’ini resize komutu ile küçültebilirsiniz ama çoğu zaman işe yaramamaktadır.
|
1 |
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/smp/UNDOTBS02.dbf' RESIZE 200; |
Resize işe yaramadığında istenilen boyutta undo tablespace oluşturulup eskisi silinir.
UndoTablespace in bilgileri alınır.
|
1 |
SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace'); |
1.Create a new UNDO tablespace
|
1 2 3 4 5 6 7 8 9 |
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/smp/UNDOTBS02.dbf' SIZE 100M; veya CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oradata/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 3G |
2.Modify the database parameter to use the new UNDO tablespace.
|
1 |
ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH; |
3.Önceki TableSpace silinir (drop)
|
1 |
drop tablespace undotbs1 including contents and datafiles; |
|
1 |
host ls -l /u01/app/oracle/oradata/smp/ |
Alert.log daki "Undo Tablespace X moved to Pending Switch-Out state"
Rollback Segment Number, rollback segment ismi, rollback segment statusu, undo tablespace name, transaction state object adresi, session identifier, session serial number , transaction ı çalıştıran username, program, machine id and operating system bilgilerini veren aşağıdaki sorgu çalıştırılır.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SELECT a.usn, a.name, b.status, c.tablespace_name, d.addr, e.sid, e.serial#, e.username, e.program, e.machine, e.osuser FROM v$rollname a, v$rollstat b, dba_rollback_segs c, v$transaction d, v$session e WHERE a.usn=b.usn AND a.name=c.segment_name AND a.usn=d.xidusn AND d.addr=e.taddr AND b.status='PENDING OFFLINE'; Ex: USN NAME STATUS TABLESPACE_NAME ADDR SID SERIAL# USERNAME PROGRAM --- --------- --------------- --------------- -------- ---- -------- -------- ----------- 5 _SYSSMU5$ PENDING OFFLINE UNDOTBS1 1477F404 9 2976 SYS sqlplus.exe MACHINE OS_USER ---------------------- -------------------- IDC-ORACLEbsriniva-pc bsriniva-pcbsriniva |
|
1 |
Alter system kill session ‘sid numarası,serial# numarası’; |
rollback segments
|
1 2 3 |
select segment_name , status from dba_rollback_segs |
|
1 |
alter system set undo_retention=500 scope=memory; |
|
1 2 3 4 5 6 |
select tablespace_name , status , count(*) as HOW_MANY from dba_undo_extents group by tablespace_name , status |
|
1 2 3 4 5 6 7 8 9 |
set lines 100 pages 999 col username format a15 col command format a20 select ses.username , substr(ses.program, 1, 19) command , tra.used_ublk from v$session ses , v$transaction tra where ses.saddr = tra.ses_addr |
Kategori seçin...