【oracle11g，13】表空间管理2：undo表空间管理(调优)，闪回原理 - 数据库编程

) then 14 exit; 15 end if; 16 end loop; 17 end; 18 /
Procedure created. #执行存储过程 SQL> exec proc_insert_data_t;
③.分页插入：
SQL> truncate table emp_cu2;
Table truncated.
SQL> declare 2 i number; 3 v_total number; 4 v_pagesize number:=1000; 5 v_totalpage number; 6 v_startindex number; 7 v_endindex number; 8 v_pageindex number; --current page num 9 begin 10 select count(*) into v_total from emp_cu; 11 v_totalpage:= CEIL(v_total/v_pagesize); 12 dbms_output.put_line(v_total); 13 dbms_output.put_line(v_totalpage); 14 for i in 1..v_totalpage loop 15 v_pageindex:=i; 16 v_startindex:=(v_pageindex-1)*v_pagesize + 1; 17 v_endindex:=v_pageindex*v_pagesize; 18 if v_endindex>v_total then 19 v_endindex:=v_total; 20 end if; 21 --dbms_output.put_line(v_startindex||' '||v_endindex); 22 insert into emp_cu2 23 select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from ( 24 select a.*,rownum rn 25 from (select * from emp_cu order by empno) a 26 ) where rn>=v_startindex and rn<=v_endindex; 27 commit; 28 end loop; 29 end; 30 /
④.重排数据并插入：
SQL> truncate table emp_cu2;
Table truncated. SQL> create or replace procedure proc_insert_data_t 2 as 3 current_time date; 4 i number; 5 v_total number; 6 v_pagesize number:=1000; 7 v_totalpage number; 8 v_startindex number; 9 v_endindex number; 10 v_pageindex number; --current page num 11 begin 12 current_time:=to_date('2010-01-01 00:00:00','yyyy-mm-dd HH24:MI:SS'); 13 for count in 1..1000000 loop 14 15 16 select count(*) into v_total from emp_cu t 17 where t.hiredate>=current_time 18 and t.hiredatev_total then 27 v_endindex:=v_total; 28 end if; 29 --dbms_output.put_line(v_startindex||' '||v_endindex); 30 insert into emp_cu2 31 select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from ( 32 select a.*,rownum rn 33 from (select * from emp_cu t 34 where t.hiredate>=current_time 35 and t.hiredate=v_startindex and rn<=v_endindex; 38 commit; 39 end loop; 40 41 current_time:=current_time+1; 42 commit; 43 if current_time>=to_date('2015-01-01','yyyy-mm-dd') then 44 exit; 45 end if; 46 end loop; 47 end; 48 /
Procedure created.
SQL> exec proc_insert_data_t;
十二.undo段调优总结：

第二部分：闪回原理：
一.开启闪回：
1.开启闪回步骤： ①.正常关库 ②.创建目录 ③.修改初始参数文件(注意两个参数的顺序：先设置size，再设置地址) db_recovery_file_dest_size= db_recovery_file_dest= ④启动到mount状态执行开启闪回命令： alter database flashback on; ⑤.启动数据库到open状态

注意：数据库在归档模式下，才能开启闪回，否则首先要开启归档。当要关闭归档时，首先关不闪回再关闭归档。
例：使用spfile启动时，开启闪回 # SQL> select name,flashback_on from v$database;
NAME FLASHBACK_ON --------- ------------------ PROD NO # SQL> alter system set db_recovery_file_dest_size='1G';
System altered. # SQL> alter system set db_recovery_file_dest='/home/oracle/prodfalshback';
System altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /home/oracle/prodfalshback db_recovery_file_dest_size big integer 1G #db_flashback_retention_target 是闪回最大时间 (单位为秒，默认1440秒一天的时间) SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
SQL> startup mount ORACLE instance started.
Total System Global Area 418484224 bytes Fixed Size 1336932 bytes Variable Size 281020828 bytes Database Buffers 130023424 bytes Redo Buffers 6103040 bytes Database mounted. #开启闪回 SQL> alter database flashback on;
Database altered. # SQL> alter database open;
Database altered. #查询闪

【oracle11g，13】表空间管理2：undo表空间管理(调优)，闪回原理(六)