[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這個版本有問題.