oracle回滚段该如何实现? 如何配置呢 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 --修改现有回滚段,使之失效,下线alter rollback segment rb1 offline;alter rollback segment rb2 offline;alter rollback segment rb3 offline;alter rollback segment rb4 offline;alter rollback segment rb5 offline;alter rollback segment rb6 offline;alter rollback segment rb7 offline;alter rollback segment rb8 offline;alter rollback segment rb9 offline;alter rollback segment rb10 offline;alter rollback segment rb11 offline;alter rollback segment rb12 offline;alter rollback segment rb13 offline;alter rollback segment rb14 offline;alter rollback segment rb15 offline;alter rollback segment rb16 offline;--删除原有回滚段drop rollback segment rb1;drop rollback segment rb2;drop rollback segment rb3;drop rollback segment rb4;drop rollback segment rb5;drop rollback segment rb6;drop rollback segment rb7;drop rollback segment rb8;drop rollback segment rb9;drop rollback segment rb10;drop rollback segment rb11;drop rollback segment rb12;drop rollback segment rb13;drop rollback segment rb14;drop rollback segment rb15;drop rollback segment rb16;以下表空间的名称应尽量有定的含义;下面只是举例说明所有名称任意取得;--建数据表空间1--出口create tablespace USER_DATA1 datafile '/oradata1/user1_1.ora' size 512M, '/oradata1/user1_2.ora' size 512M, '/oradata1/user1_3.ora' size 512M, '/oradata1/user1_4.ora' size 512M, AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);--initial 128K,因为,用户建在表空间上,而表建在用户里,为用户所拥有,--用户继承数据表空间的存储参数,表继承用户的存储参数--如果initial设的过大,如:5M,则每建一个空表就要占用5M的空间,即使一条记录也没有--AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,设置数据文件自动扩展,每一次扩展增加5M,最大空间不受限--建数据表空间2--进口create tablespace USER_DATA2 datafile '/oradata2/user2_1.ora' size 512M,'/oradata2/user2_2.ora' size 512M,'/oradata2/user2_3.ora' size 512M,'/oradata2/user2_4.ora' size 512M,AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);--建数据表空间3--报刊create tablespace USER_DATA3 datafile '/oradata3/user3_1.ora' size 512M,'/oradata3/user3_2.ora' size 512M,'/oradata3/user3_3.ora' size 512M,'/oradata3/user3_4.ora' size 512M,AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);--建数据表空间4--帐务和管理等create tablespace USER_DATA4 datafile '/oradata4/user4_1.ora' size 512M,'/oradata4/user4_2.ora' size 512M,'/oradata4/user4_3.ora' size 512M,'/oradata4/user4_4.ora' size 512M,AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);--建索引表空间1--建索引表空间2--建索引表空间3--建索引表空间4与上述创建过程类似--建回滚表空间1--设置初始值40M(initial 40M),则每在这个表空间中建一个回滚段,--此回滚段自动继承此回滚表空间的存储参数,也即默认文件为40Mcreate tablespace ROLLBACK_DATA1 datafile '/oradata1/roll1_1.ora' size 512M,'/oradata1/roll1_2.ora' size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);--建回滚表空间2create tablespace ROLLBACK_DATA2 datafile '/oradata2/roll2_1.ora' size 512M,'/oradata/roll2_2.ora' size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);--建临时表空间1create tablespace TEMPORARY_DATA1 datafile '/oradata3/temp1_1.ora' size 512M default storage (initial 10M next 3M pctincrease 0);--建临时表空间2create tablespace TEMPORARY_DATA2 datafile '/oradata4/temp2_1.ora' size 512M default storage (initial 10M next 3M pctincrease 0);--使其真正成为临时的alter tablespace TEMPORARY_DATA1 temporary;alter tablespace TEMPORARY_DATA2 temporary;--建立新的回滚段,每个都一样大,不同大小的回滚段没有什么意义,系统是随机选择的。(但可以在编程时指定使用回滚段)--建多少个,根据并发访问用户的多少,--如果每天有300-1000个人员使用Oracle系统开发的管理软件,应该100个以上create public rollback segment rb01 tablespace rollback_data1;create public rollback segment rb02 tablespace rollback_data1;create public rollback segment rb03 tablespace rollback_data1;create public rollback segment rb04 tablespace rollback_data1;create public rollback segment rb05 tablespace rollback_data1;create public rollback segment rb06 tablespace rollback_data1;create public rollback segment rb07 tablespace rollback_data1;create public rollback segment rb08 tablespace rollback_data1;create public rollback segment rb09 tablespace rollback_data1;create public rollback segment rb10 tablespace rollback_data1;。--前50个建在回滚表空间1中,后50个在回滚表空间2create public rollback segment rb11 tablespace rollback_data2;create public rollback segment rb12 tablespace rollback_data2;create public rollback segment rb13 tablespace rollback_data2;create public rollback segment rb14 tablespace rollback_data2;create public rollback segment rb15 tablespace rollback_data2;create public rollback segment rb16 tablespace rollback_data2;create public rollback segment rb17 tablespace rollback_data2;create public rollback segment rb18 tablespace rollback_data2;create public rollback segment rb19 tablespace rollback_data2;create public rollback segment rb20 tablespace rollback_data2;。--使回滚段online,即有效alter rollback segment rb01 online;alter rollback segment rb02 online;alter rollback segment rb03 online;alter rollback segment rb04 online;alter rollback segment rb05 online;alter rollback segment rb06 online;alter rollback segment rb07 online;alter rollback segment rb08 online;alter rollback segment rb09 online;alter rollback segment rb10 online;alter rollback segment rb11 online;alter rollback segment rb12 online;alter rollback segment rb13 online;alter rollback segment rb14 online;alter rollback segment rb15 online;alter rollback segment rb16 online;alter rollback segment rb17 online;alter rollback segment rb18 online;alter rollback segment rb19 online;alter rollback segment rb20 online;。。--查看现有回滚段及其状态col segment format a30SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;--查看数据文件及其所在表空间、大小、状态col file_name format a40col tablespace_name format a20select file_name,file_id,tablespace_name,bytes,status from dba_data_files;至此,表空间重新规划完毕,这里讲的比较通俗,还有好多参数值得设置,能够把Oracle设置到最优的境界, 安装完ORACLE 11g之后运行sqlplus出现错误!还有ORACLE的1521端口没有启动! 急问一个oracle字段设置的问题 PL/SQL过程 关于oracle 日期查询问题 弱弱的问一句,为什么所有的用户都能用As sysdba登录啊? Oracle10g 的SQL*Plus出错 对表更新后,分批提交的问题,急,在线等 从某种意义上来说,数据库具备增删改查的功能一般应用其实就足够用了,不知有人认同我的观点不? 求oracle中一个Select语句 各位兄弟帮忙啊~~~ ORACLE范围分区如何创建 有关v$session表中状态的问题?!
alter rollback segment rb1 offline;
alter rollback segment rb2 offline;
alter rollback segment rb3 offline;
alter rollback segment rb4 offline;
alter rollback segment rb5 offline;
alter rollback segment rb6 offline;
alter rollback segment rb7 offline;
alter rollback segment rb8 offline;
alter rollback segment rb9 offline;
alter rollback segment rb10 offline;
alter rollback segment rb11 offline;
alter rollback segment rb12 offline;
alter rollback segment rb13 offline;
alter rollback segment rb14 offline;
alter rollback segment rb15 offline;
alter rollback segment rb16 offline;--删除原有回滚段
drop rollback segment rb1;
drop rollback segment rb2;
drop rollback segment rb3;
drop rollback segment rb4;
drop rollback segment rb5;
drop rollback segment rb6;
drop rollback segment rb7;
drop rollback segment rb8;
drop rollback segment rb9;
drop rollback segment rb10;
drop rollback segment rb11;
drop rollback segment rb12;
drop rollback segment rb13;
drop rollback segment rb14;
drop rollback segment rb15;
drop rollback segment rb16;以下表空间的名称应尽量有定的含义;下面只是举例说明所有名称任意取得;
--建数据表空间1
--出口
create tablespace USER_DATA1 datafile
'/oradata1/user1_1.ora' size 512M,
'/oradata1/user1_2.ora' size 512M,
'/oradata1/user1_3.ora' size 512M,
'/oradata1/user1_4.ora' size 512M,
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--initial 128K,因为,用户建在表空间上,而表建在用户里,为用户所拥有,
--用户继承数据表空间的存储参数,表继承用户的存储参数
--如果initial设的过大,如:5M,则每建一个空表就要占用5M的空间,即使一条记录也没有
--AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,设置数据文件自动扩展,每一次扩展增加5M,最大空间不受限--建数据表空间2
--进口
create tablespace USER_DATA2 datafile
'/oradata2/user2_1.ora' size 512M,
'/oradata2/user2_2.ora' size 512M,
'/oradata2/user2_3.ora' size 512M,
'/oradata2/user2_4.ora' size 512M,
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--建数据表空间3
--报刊
create tablespace USER_DATA3 datafile
'/oradata3/user3_1.ora' size 512M,
'/oradata3/user3_2.ora' size 512M,
'/oradata3/user3_3.ora' size 512M,
'/oradata3/user3_4.ora' size 512M,
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--建数据表空间4
--帐务和管理等
create tablespace USER_DATA4 datafile
'/oradata4/user4_1.ora' size 512M,
'/oradata4/user4_2.ora' size 512M,
'/oradata4/user4_3.ora' size 512M,
'/oradata4/user4_4.ora' size 512M,
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);--建索引表空间1
--建索引表空间2
--建索引表空间3
--建索引表空间4
与上述创建过程类似--建回滚表空间1
--设置初始值40M(initial 40M),则每在这个表空间中建一个回滚段,
--此回滚段自动继承此回滚表空间的存储参数,也即默认文件为40M
create tablespace ROLLBACK_DATA1 datafile
'/oradata1/roll1_1.ora' size 512M,
'/oradata1/roll1_2.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 40M next 5M pctincrease 0);--建回滚表空间2
create tablespace ROLLBACK_DATA2 datafile
'/oradata2/roll2_1.ora' size 512M,
'/oradata/roll2_2.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 40M next 5M pctincrease 0);--建临时表空间1
create tablespace TEMPORARY_DATA1 datafile
'/oradata3/temp1_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);--建临时表空间2
create tablespace TEMPORARY_DATA2 datafile
'/oradata4/temp2_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);--使其真正成为临时的
alter tablespace TEMPORARY_DATA1 temporary;
alter tablespace TEMPORARY_DATA2 temporary;--建立新的回滚段,每个都一样大,不同大小的回滚段没有什么意义,系统是随机选择的。(但可以在编程时指定使用回滚段)
--建多少个,根据并发访问用户的多少,
--如果每天有300-1000个人员使用Oracle系统开发的管理软件,应该100个以上create public rollback segment rb01 tablespace rollback_data1;
create public rollback segment rb02 tablespace rollback_data1;
create public rollback segment rb03 tablespace rollback_data1;
create public rollback segment rb04 tablespace rollback_data1;
create public rollback segment rb05 tablespace rollback_data1;
create public rollback segment rb06 tablespace rollback_data1;
create public rollback segment rb07 tablespace rollback_data1;
create public rollback segment rb08 tablespace rollback_data1;
create public rollback segment rb09 tablespace rollback_data1;
create public rollback segment rb10 tablespace rollback_data1;
。
--前50个建在回滚表空间1中,后50个在回滚表空间2
create public rollback segment rb11 tablespace rollback_data2;
create public rollback segment rb12 tablespace rollback_data2;
create public rollback segment rb13 tablespace rollback_data2;
create public rollback segment rb14 tablespace rollback_data2;
create public rollback segment rb15 tablespace rollback_data2;
create public rollback segment rb16 tablespace rollback_data2;
create public rollback segment rb17 tablespace rollback_data2;
create public rollback segment rb18 tablespace rollback_data2;
create public rollback segment rb19 tablespace rollback_data2;
create public rollback segment rb20 tablespace rollback_data2;
。--使回滚段online,即有效
alter rollback segment rb01 online;
alter rollback segment rb02 online;
alter rollback segment rb03 online;
alter rollback segment rb04 online;
alter rollback segment rb05 online;
alter rollback segment rb06 online;
alter rollback segment rb07 online;
alter rollback segment rb08 online;
alter rollback segment rb09 online;
alter rollback segment rb10 online;
alter rollback segment rb11 online;
alter rollback segment rb12 online;
alter rollback segment rb13 online;
alter rollback segment rb14 online;
alter rollback segment rb15 online;
alter rollback segment rb16 online;
alter rollback segment rb17 online;
alter rollback segment rb18 online;
alter rollback segment rb19 online;
alter rollback segment rb20 online;
。。--查看现有回滚段及其状态
col segment format a30
SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;--查看数据文件及其所在表空间、大小、状态
col file_name format a40
col tablespace_name format a20
select file_name,file_id,tablespace_name,bytes,status from dba_data_files;至此,表空间重新规划完毕,这里讲的比较通俗,还有好多参数值得设置,能够把Oracle设置到最优的境界,