Oracle回收站及flashback drop(上)

一.1 BLOG文件結構圖

2.1 實驗環境介紹 - 22 -

一.2 前言部分

一.2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① Oracle回收站的介紹(開啟、關閉、清空)

② 閃回體系中flashback drop的介紹

③ job批次刪除回收站中的物件

④ dba_free_space查詢速度慢的問題（MOS: Queries on DBA_FREE_SPACE are Slow (文件 ID 271169.1)）

Tips：

① 本文在ITpub（http://blog.itpub.net/26736162）和部落格園(http://www.cnblogs.com/lhrbest)有同步更新

② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）

③ 若文章程式碼格式有錯亂，推薦使用搜狗、360或QQ瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/

④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

一.2.2 相關參考文章連結

【TSPITR】RMAN表空間基於時間點的自動恢復 http://blog.itpub.net/26736162/viewspace-1671741/

更多閃回知識參考：http://blog.csdn.net/tianlesoftware/article/details/4677378

一.2.3 本文簡介

今天執行健康檢查指令碼的時候指令碼一直卡在了表空間查詢這塊，瞅了一眼SQL，根據經驗小麥苗預估是由於DBA_FREE_SPACE檢視的緣故，這個檢視若回收站的物件很多的話查詢就會非常的慢，接下來單獨執行select count(1) from dba_free_space;果然非常的慢，沒辦法只能先將回收站的資料清理了再來查詢表空間了，而回收站大約有200W的資料量，執行purge DBA_RECYCLEBIN非常慢，那就只能採用job的並行技術了，這個在本文最後給出了指令碼，這個指令碼比較通用，小麥苗以前做開發的時候經常性的用這個指令碼，希望各位朋友能掌握。

說到回收站就涉及到閃回，而閃回分很多類，我們今天著重看看flashback drop和回收站。

一.3 相關知識點掃盲(摘自網路+個人總結)

一.3.1 閃回

當授權使用者犯錯，您需要使用工具來更正這些錯誤。Oracle 資料庫 10g 提供了一系列人為錯誤更正技術，稱為閃回。閃回從根本上改變了資料恢復。過去，資料庫在幾分鐘內就可能損壞，但需要幾小時才能恢復。利用閃回技術，更正錯誤的時間與錯誤發生時間幾乎相同。而且它非常易用，使用一條短命令便可恢復整個資料庫，而不必執行復雜的程式。閃回技術提供了一個 SQL 介面，能夠快速分析和修復人為錯誤。閃回技術為本地資料損壞提供了細粒度外部分析和修復，如當錯誤刪除客戶訂單時。閃回技術還支援修復更多廣泛的損壞，同時快速避免長時間停機，如當本月的所有客戶訂單都被刪除時。閃回技術是 Oracle 資料庫獨有的特性，支援各級恢復，包括行、事務、表、表空間和資料庫範圍。閃回技術是資料庫恢復技術歷史上一次重大的進步，從根本上改變了資料恢復。

Oracle 9i實現了基於回滾段的閃回查詢（Flashback Query）技術，即從回滾段中讀取一定時間內對錶進行操作的資料，恢復錯誤的DML操作。

在Oracle 10g中，除提高了閃回查詢功能，實現了閃回版本查詢、閃回事務查詢外，還實現了閃回表、閃回刪除和閃回資料庫的功能。

採用閃回技術，可以針對行級和事務級發生過變化的資料進行恢復，減少了資料恢復的時間，而且操作簡單，透過SQL語句就可以實現資料的恢復，大大提高了資料庫恢復的效率。

一.3.2 閃回技術分類

1. 閃回查詢（Flashback Query）：查詢過去某個時間點或某個SCN值時表中的資料資訊；

2. 閃回版本查詢（Flashback Version Query）：查詢過去某個時間段或某個SCN段內表中資料的變化情況；

3. 閃回事務查詢（Flashback Transaction Query）：檢視某個事務或所有事務在過去一段時間對資料進行的修改；

4. 閃回表（Flashback Table）：將表恢復到過去的某個時間點或某個SCN值時的狀態；

5. 閃回刪除（Flashback Drop）：將已經刪除的表及其關聯物件恢復到刪除前的狀態；

6. 閃回資料庫（Flashback Database）：將資料庫恢復到過去某個時間點或某個SCN值時的狀態。

注意

①　閃回查詢、閃回版本查詢、閃回事務查詢以及閃回表主要是基於撤銷表空間中的回滾資訊實現的；

②　閃回刪除是基於Oracle 10g中的回收站（Recycle Bin）特性實現的；

③　閃回資料庫是基於閃回恢復區（Flash Recovery Area）中的閃回日誌來實現的；

④　為了使用資料庫的閃回技術，必須啟用撤銷表空間自動管理回滾資訊。

⑤　如果要使用閃回刪除技術和閃回資料庫技術，還需要啟用回收站、閃回恢復區。

一.3.3 閃回刪除（Flashback Drop）

Oracle10g之前，一旦刪除了一個表，那麼該表就會從資料字典裡面刪除。要恢復該表，需要進行不完全恢復。Oracle10g以後，當我們刪除表時，預設Oracle只是在資料庫字典裡面對被刪的表的進行了重新命名，並沒有真正的把表刪除。 Flashback Drop 是從Oracle 10g 開始出現的，用於恢復使用者誤刪除的物件(包括表，索引等)，這個技術依賴於Tablespace Recycle Bin(表空間回收站)，這個功能和windows的回收站非常類似。

回收站：用來維護表被刪除前的名字與刪除後系統生成的名字之間的對應關係的資料字典，表上的相關物件（索引、觸發器等）也會一併進入回收站

被drop掉的表能否閃回來與兩個因素相關：

1、該表所在表空間的大小有關，即如果表空間夠大，用drop語句刪除的表，並不是真正的從資料庫中刪除，而是把表改成BIN$開頭的表，但是如果表空間不夠大，在有新資料要存入該表空間的時候，就會覆蓋這些BIN$表的物理空間，此時也就沒有辦法利用閃回恢復該表了

2、刪除該表的時候是否用的purge，如果在drop的時候使用了purge，則該表就被從表空間中徹底的被刪除了，如果要恢復，必須用以前的備份恢復，可以用TSPITR或12c可以直接從備份集中恢復單張表。

表空間的Recycle Bin 區域只是一個邏輯區域，而不是從表空間上物理的劃出一塊區域固定用於回收站，因此Recycle Bin是和普通物件共用表空間的儲存區域，或者說是Recycle Bin的物件要和普通物件搶奪儲存空間。

當發生空間不夠時，Oracle會按照先入先出的順序覆蓋Recycle Bin中的物件。

Flashback Drop 需要注意的地方：

1). 只能用於非系統表空間和本地管理的表空間

2). 物件的參考約束不會被恢復，指向該物件的外來鍵約束需要重建。

3). 物件能否恢復成功，取決與物件空間是否被覆蓋重用。

4). 當刪除表時，信賴於該表的物化檢視也會同時刪除，但是由於物化檢視並不會被放入recycle bin，因此當你執行flashback table to before drop 時，也不能恢復依賴其的物化檢視，需要dba 手工介入重新建立。

5). 對於Recycle Bin中的物件，只支援查詢.

一.3.4 閃回指定的表

有時可能一個表被反覆的建立和drop，這樣在recycle一個origianl name的有多個記錄相對，預設將是恢復最後一個，如果要恢復指定的一個可以用他們的OBJECT_name透過指定name的方式。

select * from user_recyclebin t where t.original_name='OLD_T';

select * from "BIN$zltzJRsMB0PgRAAY/i3Kdw==$0";

閃回回收站中指定的表：

flashback table "BIN$zltzJRsMB0PgRAAY/i3Kdw==$0" to before drop;

預設採用的是先進後出的方式，總是恢復最後被刪除的表。

一.3.5 執行閃回操作後索引的處理

表被恢復以後，表上的索引，需要重建，雖然索引可以隨著表的閃回而閃回，但是閃回後的索引仍然使用recyclebin的名字，因此我們需要重建索引。

一.3.6 回收空間

既然被刪除的物件沒有被物理的釋放，那麼該物理空間是如何進行回收的呢？Oracle透過兩種方式進行回收。

1、自動回收

當表空間出現壓力時，Oracle會首先使用表空間裡不屬於回收站的物件所佔用的可用空間，如果這部分空間用完，仍然存在空間壓力，則釋放回收站裡面最老的那些物件所佔用的空間。直至釋放完畢所有的空間，然後擴充套件資料檔案（前提是資料檔案支援自動擴充套件）

2、手工回收

使用purge命令來釋放回收站裡的物件所佔用的空間

一.3.7 回收站物件的大小

表空間的佔用大小中dba_free_space中不包括回收站中的物件的大小。

SELECT nvl(a.owner, '合計') owner,

round(SUM(a.space *

(SELECT value FROM v$parameter WHERE name = 'db_block_size')) / 1024 / 1024,

2) recyb_size_M,

count(1) recyb_cnt

FROM dba_recyclebin a

GROUP BY ROLLUP(a.owner);

一.3.8 開啟回收站

Property	Description
Parameter type	String
Syntax	RECYCLEBIN = { on \| off }
Default value	on
Modifiable	ALTER SESSION, ALTER SYSTEM ... DEFERRED
Basic	No

RECYCLEBIN is used to control whether the Flashback Drop capability is turned on or off. If the parameter is set to off, then dropped tables do not go into the recycle bin. If this parameter is set to on, then dropped tables go into the recycle bin and can be recovered

alter system set recyclebin = on scope=spfile;

alter session set recyclebin= on;

一.3.9 回收站相關SQL命令

select * from "BIN$zltzJRsMB0PgRAAY/i3Kdw==$0";

select * from user_recyclebin;

select * from dba_recyclebin;

flashback table "BIN$zltzJRsMB0PgRAAY/i3Kdw==$0" to before drop;

flashback table t to before drop rename to old_t;

show recyclebin

purge table test;//我們purge回收站中表的時候，相對應的索引也會被刪除。

purge index “索引名字”;

purge tablespace users;清除回收站裡面屬於users表空間的物件所佔用的空間

purge user_recyclebin：清除回收站裡面屬於當前使用者的所有物件所佔用的空間

purge dba_recyclebin：清除回收站裡所有物件所佔用的空間

drop table xxxx purge;直接刪除表，不進入回收站。

一.3.10 利用job來清空回收站

若回收站內容較多，則用dba_recyclebin清空回收站比較慢，這個時候可以考慮採用job分割的方法來晴空回收站，指令碼如下：

SELECT D.owner,COUNT(1) FROM dba_recyclebin D GROUP BY D.owner;

CREATE TABLE XB_recyclebin_LHR NOLOGGING AS

SELECT ROWNUM RN, 'PURGE ' || A.type || ' ' || A.owner || '."' || A.object_name || '"' EXEC_SQL

FROM dba_recyclebin A

where a.type = 'TABLE';

CREATE INDEX IDX_recyclebin_rn on XB_recyclebin_LHR(rn) NOLOGGING PARALLEL;

create table XB_SPLIT_JOB_LHR

(

startrownum NUMBER(18),

endrownum NUMBER(18),

flag NUMBER(1)

);

SELECT * FROM xb_split_job_lhr;

CREATE OR REPLACE PROCEDURE pro_split_job_lhr AUTHID CURRENT_USER IS

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

-- copy on 2012/4/2 23:28:21 by lhr

--function：該存過用來分隔資料來建立job

--需要進行處理的資料量 ,需要處理的表加rn列，值取rownum，rn列加索引

--alter table tmp_dp_idp_lhr add rn number;

/* CREATE INDEX IDX_tmp_dp_idp_lhr_rn on tmp_dp_idp_lhr(rn)

TABLESPACE SDH_INDEX ONLINE NOLOGGING COMPUTE STATISTICS PARALLEL;*/

/* create table XB_SPLIT_JOB_LHR

(

startrownum NUMBER(18),

endrownum NUMBER(18),

flag NUMBER(1)

)*/

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

n NUMBER; --建立的job數

j NUMBER := 0;

n_startrownum NUMBER;

n_endrownum NUMBER;

n_patchnum NUMBER := 20000; -- 每批處理的記錄數 ----modify

v_jobname VARCHAR2(200);

v_count NUMBER; --需要處理的表的資料量

BEGIN

SELECT COUNT(1) INTO v_count FROM XB_recyclebin_LHR; ----modify

--需要建立的job個數

n := trunc(v_count / n_patchnum) + 1;

EXECUTE IMMEDIATE 'truncate table xb_split_job_lhr';

WHILE j < n LOOP

--得到rownum

n_startrownum := j * n_patchnum + 1;

IF j = n - 1 THEN

n_endrownum := v_count;

ELSE

n_endrownum := (j + 1) * n_patchnum;

END IF;

INSERT INTO xb_split_job_lhr

(startrownum, endrownum)

VALUES

(n_startrownum, n_endrownum);

COMMIT;

j := j + 1;

END LOOP;

--迴圈建立job

j := 0;

FOR cur IN (SELECT * FROM xb_split_job_lhr) LOOP

v_jobname := 'JOB_SUBJOB_SPLIT_LHR' || (j + 1);

dbms_scheduler.create_job(job_name => v_jobname,

job_type => 'STORED_PROCEDURE',

job_action => 'PRO_SUB_SPLIT_LHR', --modify

number_of_arguments => 2,

start_date => SYSDATE + 1 / 5760, -- 15秒後啟動作業

repeat_interval => NULL,

end_date => NULL,

job_class => 'DEFAULT_JOB_CLASS',

enabled => FALSE,

auto_drop => TRUE,

comments => 'to split job_subjob_Split_lhr');

COMMIT;

dbms_scheduler.set_job_argument_value(job_name => v_jobname,

argument_position => 1,

argument_value => cur.startrownum);

COMMIT;

dbms_scheduler.set_job_argument_value(job_name => v_jobname,

argument_position => 2,

argument_value => cur.endrownum);

COMMIT;

dbms_scheduler.enable(v_jobname);

j := j + 1;

END LOOP;

COMMIT;

-----等待所有的子job執行完

LOOP

SELECT COUNT(1)

INTO v_count

FROM xb_split_job_lhr t

WHERE t.flag IS NULL;

IF v_count = 0 THEN

EXIT;

ELSE

dbms_lock.sleep(10); ---存過休息10秒

END IF;

END LOOP;

EXECUTE IMMEDIATE 'purge dba_recyclebin';

EXCEPTION

WHEN OTHERS THEN

NULL;

END pro_split_job_lhr;

create or replace procedure pro_sub_split_lhr(p_startrownum number,

p_endrownum number) is

begin

for cur in (SELECT A.EXEC_SQL

FROM XB_recyclebin_LHR A ---modify

where A.rn <= p_endrownum

and A.rn >= p_startrownum) loop

begin

EXECUTE IMMEDIATE CUR.EXEC_SQL;

exception

when others then

null;

end;

end loop;

commit;

--更新標誌

update xb_split_job_lhr t

set t.flag = 1

where t.startrownum = p_startrownum

and t.endrownum = p_endrownum;

commit;

exception

when others then

null;

end pro_sub_split_lhr;

一.3.12 官方文件

When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACK TABLE statement if you find that you dropped the table in error. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following statement:

DROP TABLE hr.admin_emp PURGE;

一.4 Using Flashback Drop and Managing the Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table. Before discussing the use of the FLASHBACK TABLE statement for this purpose, it is important to understand how the recycle bin works, and how you manage its contents.

This section contains the following topics:

·

一.4.1 What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, because, unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

·

When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.

·

When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.

·

When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

·

Object Naming in the Recycle Bin

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name. This could occur under the following circumstances:

·

A user drops a table, re-creates it with the same name, then drops it again.

·

Two users have tables with the same name, and both users drop their tables.

·

The renaming convention is as follows:

BIN$unique_id$version

where:

·

unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases

·

version is a version number assigned by the database

·

一.4.2 Enabling and Disabling the Recycle Bin

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup).

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

You enable and disable the recycle bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEM statement.

To disable the recycle bin:

1.

Issue one of the following statements:

2.

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;

3.

4.

If you used ALTER SYSTEM, restart the database.

5.

To enable the recycle bin:

1.

Issue one of the following statements:

2.

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE;

3.

4.

If you used ALTER SYSTEM, restart the database.

5.

一.4.3 Viewing and Querying Objects in the Recycle Bin

Oracle Database provides two views for obtaining information about objects in the recycle bin:

View	Description
USER_RECYCLEBIN	This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.
DBA_RECYCLEBIN	This view gives administrators visibility to all dropped objects in the recycle bin

One use for these views is to identify the name that the database has assigned to a dropped object, as shown in the following example:

SELECT object_name, original_name FROM dba_recyclebin

WHERE owner = 'HR';

OBJECT_NAME ORIGINAL_NAME

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

BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES

You can also view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

一.4.4 Purging Objects in the Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

When you use the PURGE statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in . The following hypothetical example purges the table hr.int_admin_emp, which was renamed to BIN$jsleilx392mk2=293$0 when it was placed in the recycle bin:

PURGE TABLE "BIN$jsleilx392mk2=293$0";

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp;

You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified tablespace or only the tablespace objects belonging to a specified user, as shown in the following examples:

PURGE TABLESPACE example;

PURGE TABLESPACE example USER oe;

Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:

PURGE RECYCLEBIN;

If you have the SYSDBA privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the previous statement.

You can also use the PURGE statement to purge an index from the recycle bin or to purge from the recycle bin all objects in a specified tablespace.

一.4.5 Restoring Tables from the Recycle Bin

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. An optional RENAME TO clause lets you rename the table as you recover it. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in . To use the FLASHBACK TABLE ... TO BEFORE DROP statement, you need the same privileges required to drop the table.

The following example restores int_admin_emp table and assigns to it a new name:

FLASHBACK TABLE int_admin_emp TO BEFORE DROP

RENAME TO int2_admin_emp;

The system-generated recycle bin name is very useful if you have dropped a table multiple times. For example, suppose you have three versions of the int2_admin_emp table in the recycle bin and you want to recover the second version. You can do this by issuing two FLASHBACK TABLE statements, or you can query the recycle bin and then flashback to the appropriate system-generated name, as shown in the following example. Including the create time in the query can help you verify that you are restoring the correct table.

SELECT object_name, original_name, createtime FROM recyclebin;

OBJECT_NAME ORIGINAL_NAME CREATETIME

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

BIN$yrMKlZaLMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:21:05:52

BIN$yrMKlZaVMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:21:25:13

BIN$yrMKlZaQMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:22:05:53

FLASHBACK TABLE "BIN$yrMKlZaVMhfgNAgAIMenRA==$0" TO BEFORE DROP;

Restoring Dependent Objects

When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table.

The following is an example of restoring the original names of some of the indexes of the dropped table JOB_HISTORY, from the HR sample schema. The example assumes that you are logged in as the HR user.

1.

After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

2.

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME TYPE

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

BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX INDEX

BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX INDEX

BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX INDEX

BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK INDEX

BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY TABLE

3.

4.

Restore the table with the following command:

5.

FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;

6.

7.

Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

8.

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';

INDEX_NAME

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

BIN$DBo9UChwZSbgQFeMiAdCcQ==$0

BIN$DBo9UChtZSbgQFeMiAdCcQ==$0

BIN$DBo9UChuZSbgQFeMiAdCcQ==$0

BIN$DBo9UChvZSbgQFeMiAdCcQ==$0

9.

10.

Restore the original names of the first two indexes as follows:

11.

ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;

ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;

12.

Note that double quotes are required around the system-generated names.

13.

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

About Me

..........................................................................................................................................................................................................

本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

本文在ITpub（http://blog.itpub.net/26736162）和部落格園(http://www.cnblogs.com/lhrbest)有同步更新

本文地址：http://blog.itpub.net/26736162/viewspace-2121136/

本文pdf版：（提取碼：ed9b）

小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/

聯絡我請加QQ好友(642808185)，註明新增緣由

於 2016-06-24 10:00~ 2016-06-27 19:00 在中行完成

..........................................................................................................................................................................................................

Oracle回收站及flashback drop(上)

一.1 BLOG文件結構圖

一.2 前言部分

一.2.1 導讀和注意事項

一.2.2 相關參考文章連結

一.2.3 本文簡介

一.3 相關知識點掃盲(摘自網路+個人總結)

一.3.1 閃回

一.3.2 閃回技術分類

一.3.3 閃回刪除（Flashback Drop）

一.3.4 閃回指定的表

一.3.5 執行閃回操作後索引的處理

一.3.6 回收空間

一.3.7 回收站物件的大小

一.3.8 開啟回收站

一.3.9 回收站相關SQL命令

一.3.10 利用job來清空回收站

一.3.12 官方文件

一.4 Using Flashback Drop and Managing the Recycle Bin

一.4.1 What Is the Recycle Bin?

一.4.2 Enabling and Disabling the Recycle Bin

一.4.3 Viewing and Querying Objects in the Recycle Bin

一.4.4 Purging Objects in the Recycle Bin

一.4.5 Restoring Tables from the Recycle Bin

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

相關文章