没有活动事物，太好了，可以放心的drop回滚段了，这正是我想要的结果。
接下来查找回滚段存储参数信息:
SQL> col tablespace_name format a10
SQL> col SEGMENT_NAME format a12
SQL> set line 120
SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,initial_extent,NEXT_EXTENT,
MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_rollback_segs;
SEGMENT_NAME OWNER? TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
SYSTEM?????? SYS??? SYSTEM????????????? 57344?????? 57344?????????? 2???????? 505??????????? 0
RBS0???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS1???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS2???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS3???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS4???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS5???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS6???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS7???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS8???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS9???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS10??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS11??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS12??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS13??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS14??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS15??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS16??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS17??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS18??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS19??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS20??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS21??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS22??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS23??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS24??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS26??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS27??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS28??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS25??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
APPRBS?????? PUBLIC RBS01???????????? 2097152??? 10485760????????? 50?????? 32765??????????? 0
31 rows selected. |
把initial_extent,next_extent,min_extents,max_extents,pct_increase的值都记录下来，留做以后创建新的回滚段使用。
创建LMT管理方式的回滚段表空间(我的数据库是oracle817):
SQL> create tablespace rbs01 datafile '/opt/oracle/db02/oradata/ORCL/rbs01.dbf'
size 1024M?autoextend on next 1M maxsize unlimited extent management local;
Tablespace created. |
先在该表空间下建立一个回滚段rbs31做一个测试:
SQL> create public rollback segment RBS31 tablespace rbs01
storage(initial 524288 next 524288 MINEXTENTS 8 MAXEXTENTS
4096 OPTIMAL 4194304);
create public rollback segment RBS31 tablespace rbs01
storage(initial 524288 next 524288 MINEXTENTS 8 MAXEXTENTS
4096 OPTIMAL 4194304)
*
ERROR at line 1:
ORA-25151: Rollback Segment cannot be created in this tablespace |
出错了，居然没有建成功,shit.
查了一下metalink发现对于oracle8i来讲在LMT方式管理的表空间下不能创建回滚段，但9i解决了该问题。
metalink上的解释:
Explanation
-----------
Rollback segments cannot be created in locally managed tablespaces
(a new feature in Oracle 8.1) with allocation type of AUTOALLOCATE.
They must be created in locally managed tablespaces with allocation
type of UNIFORM or in dictionary managed tablespaces.
NOTE: This restriction has been lifted in Oracle 9. |
查看本文来源
京公网安备 11010802021500号