Пересоздать undo

Смена UNDO TABLESPACE Во-первых, необходимо узнать текущие UNDO параметры:

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> select name from v$datafile where NAME like '%undo%';

NAME

--------------------------------------------------------------------------------

/u03/bm7/datafile/undotbs1.dbf /u03/bm7/datafile/undotbs2.dbf

Текущее табличное пространство UNDO в нашем случае UNDOTBS1.

Вторым действием создаем табличное пространство ему на смену, выбирая размер и шаг увеличения в зависимости от потребностей базы:

CREATE UNDO TABLESPACE undotbs DATAFILE '/u02/bm7/datafile/undotbs.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

Устанавливаем вновь созданное табличное пространство пространством по умолчанию:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS SCOPE=BOTH;

Пытаемся удалить старое табличное пространство:

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Появившаяся ошибка говорит нам о том, что UNDOTBS1 все еще используется и будет использоваться в течение времени, указанного в параметре

UNDO_RETENTION — в нашем случае, 900 секунд, или 15 минут. Выполняем попытку удаления спустя 15 минут:

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES; Tablespace dropped.

Если этот фокус не прошел, значит старые транзакции кто-то держит. Проверяем имя UNDO segment  в  UNDOTBS1 tablespace и его статус

set lines 10000
column name format a10

SELECT a.name,b.status 
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN ( 
		  SELECT segment_name
		  FROM dba_segments 
		  WHERE tablespace_name = 'UNDOTBS1'
		 );

NAME       STATUS
---------- ---------------
_SYSSMU8$  PENDING OFFLINE

Теперь найдем пользователя который держит

alter system kill session '422,15452';

ПЕРЕВЕСТИ СТАРЫЙ UNDO В OFFLINE

кто держит 

 /usr/sbin/lsof | grep deleted | grep "/u02/oradata/db/navgt/undo"