Skip to content

ORA-25152: TEMPFILE cannot be dropped at this time

ORA-25152: TEMPFILE cannot be dropped at this time published on Комментариев к записи ORA-25152: TEMPFILE cannot be dropped at this time нет

hello, here how I dropped tempfile :

start with:

ALTER TABLESPACE temp  DROP TEMPFILE '+DATADG/p00pgp/tempfile/temp.340.882540285';
ALTER TABLESPACE temp  DROP TEMPFILE '+DATADG/p00pgp/tempfile/temp.339.882540283';

but when I try to drop datafiles I’ve got this error:
ORA-25152: TEMPFILE cannot be dropped at this time

helped this sript

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     and b.segfile# in ( 205,206)
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

which helped me to find sessions using tempfile
sessions was idle, so I kill them, after that this command have no errors:

ALTER DATABASE TEMPFILE '+DATADG/p00pgp/tempfile/temp.339.882540283' DROP      INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '+DATADG/p00pgp/tempfile/temp.340.882540285' DROP      INCLUDING DATAFILES;

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Primary Sidebar