Whoever Resembles a People is One of Them. He is not one of us who resembles other than us,
nor who resembles the Jews nor the Christians.(jami-at-tirmidhi-vol-5-ahadith-2695)
O you who believe (who wish to reach ALLAH C.C. before death)! Do not take my and
your enemies for friends!
And although they have denied what has come to you
from the Truth, you still love them (treating them as your friends).
If you go forth to struggle hard in My Way, seeking My Pleasure, (then why) you manifest
love to them and share with them your secrets.
And I know what you conceal and what you manifest. And whoever of you does that, then he
indeed has gone astray from the right Way. 60/Al-Mumtahanah-1
They are driving out the Messenger and yourselves from your land because you
believe in ALLAH C.C., your Lord.
Recite (read and explain to others) that which has been revealed to you of the Book
and keep up the Prayer.
Surely the Prayer keeps away from Al-Fahsh (to do what ALLAH C.C. prohibits) and Al-Munkar
(to deny what ALLAH C.C. commands).
Whoever obeys me, obeys ALLAH C.C., and whoever disobeys me, disobeys ALLAH C.C.
Whoever obeys the ruler, obeys me, and whoever disobeys the ruler, disobeys me. " (Sahih)

Undo TableSpace Resize (Recreate&Drop)

Undo Tablespace’ini resize komutu ile küçültebilirsiniz ama çoğu zaman işe yaramamaktadır.

{code class=”brush: xml;”}
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/smp/UNDOTBS02.dbf’ RESIZE 200;
{/code}

Resize işe yaramadığında istenilen boyutta undo tablespace oluşturulup eskisi silinir.

UndoTablespace in bilgileri alınır.

{code class=”brush: xml;”}
SELECT name,value FROM v$parameter WHERE name IN (‘undo_management’,’undo_tablespace’);
{/code}

1.Create a new UNDO tablespace

{code class=”brush: xml;”}
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
{/code}

2.Modify the database parameter to use the new UNDO tablespace.
{code class=”brush: xml;”}
ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;
{/code}

3.Önceki TableSpace silinir (drop)
{code class=”brush: xml;”}
drop tablespace undotbs1 including contents and datafiles;
{/code}

{code class=”brush: xml;”}
host ls -l /u01/app/oracle/oradata/smp/
{/code}

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.

{code class=”brush: xml;”}
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-ORACLE\bsriniva-pc bsriniva-pc\bsriniva{/code}

{code class=”brush: xml;”}Alter system kill session ‘sid numarası,serial# numarası’;{/code}

rollback segments
{code class=”brush: xml;”}select segment_name
, status
from dba_rollback_segs{/code}
{code class=”brush: xml;”}alter system set undo_retention=500 scope=memory; {/code}
{code class=”brush: xml;”}select tablespace_name
, status
, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name
, status{/code}
{code class=”brush: xml;”}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{/code}

{jcomments on}

Copyright © 2015 Sysdba All Rights Reserved Web Designer