|
######## 方法2 通过更改数据字典表 来删除回滚段和undo tablespace
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS3
SQL> conn roger/roger
Connected.
SQL> select count(*) from ht_01;
COUNT(*)
----------
3
SQL> delete from ht_01 where rownum <2;
1 row deleted.
SQL> ---不提交
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
2 5 310 345 7 6
SQL> select usn,name from v$rollname where usn=2;
USN NAME
---------- --------------------------------------------------
2 _SYSSMU2$
SQL> alter system set undo_tablespace=undotbs4;
System altered.
SQL> alter database datafile 7 offline;
Database altered.
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 104860968 bytes
Database Buffers 58720256 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs3 including contents and datafiles;
drop tablespace undotbs3 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace
SQL> select ts# from ts$ where name='UNDOTBS3';
TS#
----------
7
SQL> select file#,block#,TYPE#,TS# from seg$ where ts#=7;
FILE# BLOCK# TYPE# TS#
---------- ---------- ---------- ----------
7 9 10 7
7 25 10 7
7 41 10 7
7 57 10 7
7 73 10 7
7 89 10 7
7 105 10 7
7 121 10 7
7 137 10 7
7 153 10 7
7 265 3 7
7 281 10 7
12 rows selected.
SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS
2 from dba_rollback_segs where file_id=7;
OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS
------ ------------- ---------- ---------- ---------- ----------------
PUBLIC _SYSSMU2$ 2 7 281 NEEDS RECOVERY
PUBLIC _SYSSMU21$ 21 7 9 OFFLINE
PUBLIC _SYSSMU22$ 22 7 25 OFFLINE
PUBLIC _SYSSMU23$ 23 7 41 OFFLINE
PUBLIC _SYSSMU24$ 24 7 57 OFFLINE
PUBLIC _SYSSMU25$ 25 7 73 OFFLINE
PUBLIC _SYSSMU26$ 26 7 89 OFFLINE
PUBLIC _SYSSMU27$ 27 7 105 OFFLINE
PUBLIC _SYSSMU28$ 28 7 121 OFFLINE
PUBLIC _SYSSMU29$ 29 7 137 OFFLINE
PUBLIC _SYSSMU30$ 30 7 153 OFFLINE
11 rows selected.
SQL>
SQL> update seg$ set type# = 3 where ts#=7 and file#=7 and BLOCK#=281;
1 row updated.
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 104860968 bytes
Database Buffers 58720256 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> select US# ,NAME,FILE#,BLOCK# from undo$ where file#=7;
US# NAME FILE# BLOCK#
---------- --------------- ------- ----------
1 _SYSSMU1$ 7 265
2 _SYSSMU2$ 7 281
21 _SYSSMU21$ 7 9
22 _SYSSMU22$ 7 25
23 _SYSSMU23$ 7 41
24 _SYSSMU24$ 7 57
25 _SYSSMU25$ 7 73
26 _SYSSMU26$ 7 89
27 _SYSSMU27$ 7 105
28 _SYSSMU28$ 7 121
29 _SYSSMU29$ 7 137
30 _SYSSMU30$ 7 153
12 rows selected.
SQL>
SQL> delete from undo$ where ts#=7 and US#=2;
1 row deleted.
SQL> delete from seg$ where ts#=7 and file#=7 and block#=281;
1 row deleted.
SQL>
SQL> commit;
Commit complete.
SQL> drop rollback segment "_SYSSMU2$";
drop rollback segment "_SYSSMU2$"
*
ERROR at line 1:
ORA-01545: rollback segment '_SYSSMU2$' specified not available
由于我们已经从几个数据字典表中将该段清除了,所以需要用包检查下
SQL> execute hcheck.full
PL/SQL procedure successfully completed.
SQL> drop rollback segment "_SYSSMU2$";
Rollback segment dropped.
SQL>
SQL> drop tablespace undotbs3 including contents and datafiles;
drop tablespace undotbs3 including contents and datafiles
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
对于该错误,处理起来就非常容易了,如下:
SQL> update seg$ set type# = 3 where ts#=7;
11 rows updated.
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 104860968 bytes
Database Buffers 58720256 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs3 including contents and datafiles;
Tablespace dropped.
SQL> ----Drop Tablespace 成功
简单的总结下,其实我们只要通过如下步骤就能轻易的删除:
1. 将回滚段更改为临时段
2. 重启实例
3. 从seg$中删除记录
4. 从undo$中删除记录
需要注意一下的是,如果不使用hcheck.full 那么直接drop tablespace可能遇到如下错误:
SQL> drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssdrp1], [5], [6], [25], [], [], [], []
########## 方法3 使用bbed 修改元数据
SQL> purge recyclebin;
Recyclebin purged.
SQL> create table ht01 as select owner,object_name,object_id
2 from dba_objects where object_id <100;
Table created.
SQL> select count(*) from ht01;
COUNT(*)
----------
98
SQL> delete from ht01 where object_id <10;
8 rows deleted.
SQL> ----不提交
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS4
SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS
2 from dba_rollback_segs where tablespace_name='UNDOTBS4';
OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS
------ ------------- ---------- ---------- ---------- ----------------
PUBLIC _SYSSMU1$ 1 2 169 ONLINE
PUBLIC _SYSSMU2$ 2 2 185 ONLINE
PUBLIC _SYSSMU3$ 3 2 9 OFFLINE
PUBLIC _SYSSMU4$ 4 2 25 OFFLINE
PUBLIC _SYSSMU5$ 5 2 41 OFFLINE
PUBLIC _SYSSMU6$ 6 2 57 OFFLINE
PUBLIC _SYSSMU7$ 7 2 73 OFFLINE
PUBLIC _SYSSMU8$ 8 2 89 OFFLINE
PUBLIC _SYSSMU10$ 10 2 105 OFFLINE
PUBLIC _SYSSMU11$ 11 2 121 OFFLINE
PUBLIC _SYSSMU12$ 12 2 137 OFFLINE
PUBLIC _SYSSMU31$ 31 2 153 OFFLINE
12 rows selected.
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
2 9 3 191 2 10
SQL> select usn,name from v$rollname where usn=2;
USN NAME
---------- --------------------------------------------------
2 _SYSSMU2$ |
|