[20181002]DBMS_FLASHBACK与函数.txt

[20181002]DBMS_FLASHBACK与函数.txt http://berxblog.blogspot.com/2018/10/seing-your-db-as-it-was-some-minutes-ago.html --//重复测试: DBMS_FLASHBACK Overview DBMS_FLASHBACK provides an interface for the user to view the database at a particular time in the past, with the additional capacity provided by transaction back out features that allow for selective removal of the effects of individual transactions. This is different from a flashback database which moves the database back in time. When DBMS_FLASHBACK is enabled, the user session uses the Flashback version of the database, and applications can execute against the Flashback version of the database. DBMS_FLASHBACK is relevant only for the session in which it's called, so it doesn't change the database. But it's unclear what's meant by database in this context. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0 drop table x1 purge; create table x1 (u number); insert into x1 (u) values (1); CREATE OR REPLACE FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 1 BEGIN     IF ( i = 1 ) THEN         RETURN 'red';     ELSE         RETURN 'blue';     END IF; END return_something; / 2.测试: SCOTT@test01p> select sysdate now, u, return_something(u) c10,current_scn from x1,v$database; NOW                          U C10        CURRENT_SCN ------------------- ---------- ---------- ----------- 2018-10-03 19:46:02          1 red           29429234 SCOTT@test01p> host sleep 60 SCOTT@test01p> update x1 set u = 2 where u = 1; 1 row updated. CREATE OR REPLACE FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; / SCOTT@test01p> select sysdate now, u, return_something(u) c10,current_scn from x1,v$database; NOW                          U C10        CURRENT_SCN ------------------- ---------- ---------- ----------- 2018-10-03 19:50:35          2 zwoa          29429620 3.使用DBMS_FLASHBACK. --//EXEC dbms_flashback.enable_at_time(to_timestamp('2018-10-03 19:46:02','YYYY-MM-DD HH24:MI:SS')); SCOTT@test01p> exec dbms_flashback.enable_at_system_change_number(29429234); PL/SQL procedure successfully completed. SCOTT@test01p> select sysdate now, u, return_something(u) c10,current_scn from x1,v$database; NOW                          U C10        CURRENT_SCN ------------------- ---------- ---------- ----------- 2018-10-03 19:52:24          1 oans          29429665 --//注意函数return_something(u)的返回,使用新定义的函数.sysdate,CURRENT_SCN也是在走. SCOTT@test01p> col text for A50 SCOTT@test01p> select text from dba_source where owner=user and name='RETURN_SOMETHING' order by LINE asc; TEXT -------------------------------------------------- FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; 11 rows selected. --//我这里看到与作者不同,函数的内容是新定义的.打开另外会话: SYS@test01p> select text c100 from dba_source as of scn 29429234 where owner='SCOTT' and name='RETURN_SOMETHING' order by LINE asc; C100 ---------------------------------------------------- FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; 11 rows selected. --//奇怪,12c不能看到前面的版本. --//链接:http://berxblog.blogspot.com/2018/10/seing-your-db-as-it-was-some-minutes-ago.html Here we can see how DBMS_FLASHBACK.ENABLE_AT_TIME is set to a time between the 1st insert & create function and the 2nd block. With this setting, the content of table X1 is as expected. Also DBA_SOURCE shows the code of RETURN_SOMETHING. But the function itself is not changed in memory and works as of it's state NOW, not at the given flashback time. The flashback time version of RETURN_SOMETHING is even visible when you open it in SQLDeveloper (you have to believe me or test it). Don't forget to clean up after the tests: SCOTT@test01p> exec dbms_flashback.disable; PL/SQL procedure successfully completed. SCOTT@test01p> select sysdate now, u, return_something(u) c10,current_scn from x1,v$database; NOW                          U C10        CURRENT_SCN ------------------- ---------- ---------- ----------- 2018-10-03 20:01:04          2 zwoa          29431158 For this testcase no COMMIT was used. ;-) 4.收尾: SCOTT@test01p> drop table x1 purge; Table dropped. SCOTT@test01p> drop FUNCTION "RETURN_SOMETHING"; Function dropped. 5.总结: 其它不解析,但是我的这个12c版本,函数看不到旧版本,重复测试看看. CREATE OR REPLACE FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 1 BEGIN     IF ( i = 1 ) THEN         RETURN 'red';     ELSE         RETURN 'blue';     END IF; END return_something; / SCOTT@test01p> select current_scn,sysdate from v$database ; CURRENT_SCN SYSDATE ----------- -------------------    29431442 2018-10-03 20:07:15 SCOTT@test01p> select text from dba_source as of scn 29431442 where owner=user and name='RETURN_SOMETHING' order by LINE asc; select text from dba_source as of scn 29431442 where owner=user and name='RETURN_SOMETHING' order by LINE asc                  * ERROR at line 1: ORA-01031: insufficient privileges --//没有权限看dba_source视图. SCOTT@test01p> select text from user_source  where  name='RETURN_SOMETHING' order by LINE asc; no rows selected SCOTT@test01p> select text from user_source as of scn 29431442 where  name='RETURN_SOMETHING' order by LINE asc; select text from user_source as of scn 29431442 where  name='RETURN_SOMETHING' order by LINE asc                  * ERROR at line 1: ORA-01031: insufficient privileges --//换一个用户SYS. SYS@test01p> select text from dba_source as of timestamp to_date('2018-10-03 20:07:15','yyyy-mm-dd hh24:mi:ss') where owner=user and name='RETURN_SOMETHING' order by LINE asc; no rows selected --//再次回到原来会话: CREATE OR REPLACE FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; / SCOTT@test01p> select text c50  from user_source  where  name='RETURN_SOMETHING' order by LINE asc; C50 -------------------------------------------------- FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; 11 rows selected. SCOTT@test01p> select text c50  from dba_source  where  name='RETURN_SOMETHING' order by LINE asc; C50 -------------------------------------------------- FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; 11 rows selected. SYS@test01p> select text c50  from dba_source as of scn 29431442 where  name='RETURN_SOMETHING' order by LINE asc; C50 -------------------------------------------------- FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; 11 rows selected. --//看到的是新定义的函数. --//很明显,oracle 12c这个版本有问题.