ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use
You might get this error, when you tried to drop the undo table. Its because , there are some active transactions
in the undo. The solution 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.
×
ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use
You might get this error, when you tried to drop the undo table. Its because , there are some active transactions
in the undo. The solution 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.
Related Articles
- Warning: ORA-16854: apply lag could not be determined
- ORA-15260: permission denied on ASM disk group
- CRS-4013: This command is not supported in a single-node configuration
- Verifying Daemon “avahi-daemon” not configured and running …FAILED (PRVG-1360)
- OPATCHAUTO-72088: OPatch software version in homes selected for patching are different.
- LsInventorySession failed: RawInventory gets null OracleHomeInfo
- ORA-01442: column to be modified to NOT NULL is already
- ORA-14024: number of partitions of LOCAL index must equal that of the
作者:老应(weikui)
wechat: ywkonline
专注于SAP运维、升级、迁移
出处:http://www.cnblogs.com/weikui/
wechat: ywkonline
专注于SAP运维、升级、迁移
出处:http://www.cnblogs.com/weikui/


浙公网安备 33010602011771号