Oracle回收站表閃回機制研究

 

在資料庫中建立DDL 觸發器和DDL 表時，發現在drop 表的時候，oracle 內部會先把表給rename 後再drop 到回收站內。那這個rename 的操作有什麼特殊的含義，接下來就是研究過程

 

表和DDL 觸發器來跟蹤drop 表的過程

我在研究透過建立ddl 表和ddl 觸發器來記錄資料庫中ddl 的操作日誌的時候發現一個有意思的事：

 

建立DDL 表和DDL 觸發器

---- 儲存DDL 語句的表

create table sys.my_audit_ddl(

opertime timestamp PRIMARY KEY,

ip varchar2(20),

hostname varchar2(30),

operation varchar2(30),

object_type varchar2(30),

object_name varchar2(30),

sql_stmt clob,

db_schema varchar2(30)

);

 

---- 捕獲DDL 語句的觸發器

CREATE OR REPLACE TRIGGER sys.trg_my_audit_ddl

after ddl ON DATABASE

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

n NUMBER;

stmt clob := NULL;

sql_text ora_name_list_t;

BEGIN

n := ora_sql_txt(sql_text);

FOR i IN 1 .. n LOOP

stmt := stmt || sql_text(i);

END LOOP;

INSERT INTO sys.my_audit_ddl

(opertime, ip, hostname, operation, object_type, object_name, sql_stmt,db_schema)

VALUES

(systimestamp,

sys_context('userenv', 'ip_address'),

sys_context('userenv', 'terminal'),

ora_sysevent,

ora_dict_obj_type,

ora_dict_obj_name,

stmt,

user

);

COMMIT;

END;

/

 

在test 使用者下建一張test 表

create table test (

name varchar2(20),id number(2));

 

用sys 使用者查詢那張ddl 記錄表：

SQL> select SQL_STMT,OPERTIME from sys.my_audit_ddl;

 

SQL_STMT                                            OPERTIME

----------------------------------                  ------------------------------

create table test (                                      04-AUG-17 01.46.50.762982 PM

name varchar2(20),id number(2))

 

 

可以看到建立的記錄被記錄下來。

 

用test 使用者刪除這張test 表（不加purge ）：

drop table test;

 

發現ddl 記錄的過程中，多了一行

SQL> select SQL_STMT,OPERTIME from sys.my_audit_ddl;

 

SQL_STMT                                                                     OPERTIME

-------------------------------------------------------------------------- -- ------------------------------

create table test (                                                                04-AUG-17 01.46.50.762982 PM

name varchar2(20),id number(2))

 

ALTER TABLE "TEST"."TEST" RENAME TO "BIN$VegNdsxaqv7gU2UoqMD5iA==$0"        04-AUG-17 01.48.56.035327 PM

drop table test                                                                  04-AUG-17 01.48.56.149173 PM

 

在刪除這張表進入回收站之前，會先將這張表更改名字，然後再刪除這張表。

去回收站內檢視：

SQL> select owner,object_name from dba_recyclebin;

OWNER                          OBJECT_NAME                    ORIGINAL_NAME

------------------------------ ------------------------------ ----------------------

TEST                           BIN$VegNdsxaqv7gU2UoqMD5iA ==$0  TEST

 

 

可以看到，在回收站內看到的表名，被改成BIN 開頭的名字，同時原名也記錄在其中。

那這個object_name 有什麼用呢？

我們在使用flashback table 的時候，語句一般是這樣的：

flashback table test to before drop;

 

這個時候，回收站內的test 表，會被還原到原使用者下。

但如果如果一個使用者，連續兩次刪除了一張同名表呢？

還原的時候指定這兩張同名表的時候，回收站會預設還原距離當前時間最近被刪除的一張表：

 

SQL> select object_name,original_name,droptime,dropscn from user_recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME               DROPSCN

------------------------------ -------------------------------- ------------------- ----------

BIN$VehKONsSsmrgU2UoqMAAqA==$0 TEST01                           2017-08-04:14:12:00    1171519

BIN$Vek/0SbO3ODgU2UoqMBrzQ==$0 TEST01                           2017-08-04:15:22:30    1175274

 

SQL>flashback table test01 to before drop;

 

SQL> select object_name,original_name,droptime,dropscn from user_recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME               DROPSCN

------------------------------ -------------------------------- ------------------- ----------

BIN$VehKONsSsmrgU2UoqMAAqA==$0 TEST01                           2017-08-04:14:12:00    1171519

 

那我們如果想還原的不是這張距離時間最近的表呢？

那麼這個時候就需要用到object_name 了。

透過指定object_name ，我們可以還原任意一張表:

 

SQL> select object_name,original_name,droptime,dropscn from user_recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME               DROPSCN

------------------------------ -------------------------------- ------------------- ----------

BIN$VehKONsSsmrgU2UoqMAAqA==$0 TEST01                           2017-08-04:14:12:00    1171519

BIN$Vek/0SbO3ODgU2UoqMBrzQ==$0 TEST01                           2017-08-04:15:22:30    1175274

 

SQL>flashback table “ BIN$VehKONsSsmrgU2UoqMAAqA==$0” to before drop;

這就是在drop 的時候，為什麼oracle 會先把表給rename 成一個特殊的名字的原因之一。