SOLVED

ORA-30013: Undo Tablespace ‘UNDOTBS1’ Is Currently In Use

Asked by OracleDba••17 views•oracle
1
2
3
4
5
ORA-30013: Undo Tablespace ‘UNDOTBS1’ Is Currently In Use

You may get this error, when you tried to drop the undo table. Its because , there are some active transactions in the undo.

The
#oracle#error

Solutions(1)

Accepted Solution
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
is to find that transaction and kill the same.

set pagesize 200

set lines 200

set long 999

col username for a9

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE a.usn = b.usn

AND a.usn = c.xidusn

AND c.ses_addr = d.saddr

AND a.name IN (

SELECT segment_name

FROM dba_segments

WHERE tablespace_name = 'UNDOTBS1'

);

NAME STATUS USERNAME SID SERIAL#

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

_SYSSMU691$ PENDING OFFLINE SCOTT 20 30

Now kill this sessions:

alter system kill session '20,30' immediate;

system altered

Now try to drop the undo tablespace.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

Hope it Helps!
OracleDba•

Post Your Solution