ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 10g 中误删除(drop)表的恢复处理

Oracle 10g 中误删除(drop)表的恢复处理

原创 Linux操作系统 作者:regonly1 时间:2009-07-27 13:40:59 0 删除 编辑


在9i中Oracle引入了flashback的概念,可以将数据返回到某个时间点,
但对于诸如drop/truncate等DDL语句却尚不支持。进入Oracle10g,这一
缺陷得到了弥补。可以将丢失掉的表,通过flashback命令直接找回。
非常简单且易用。
首先,可以查看当前schema中的表:
SQL> select * from tab;

TNAME                    TABTYPE  CLUSTERID
------------------------ ------- ----------
RECYCLETEST              TABLE

然后删除该表:
SQL> drop table recycletest;

Table dropped.
检查表状态:
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

由此可知,Oracle将drop掉的表重命名了。而且对应的触发器,索引
等对象,所涉及到的表名,都已经对应修改。

假设现在有N个表都被drop掉了,那么如何查看被drop掉的表和在回收
站里面被重新命名后的名称呢?如下:
SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST      BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31
或者:
select * from dba_recyclebin;
select * from user_recyclebin;

该命令需要在10g以上的版本的sqlplus中执行方可查看。
然后恢复该表:
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.

可以看到删除的表已经被恢复了:
SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

记住,将删除的表放在回收站中不会释放原有的表空间,要释放这些空
间,需要执行:
purge recyclebin;

那么如果10g中要彻底删除一个表,而不要将该表回收放入回收站中呢:
drop table recycletest purge;

由于drop一个表在Oracle中只是对该表重命名,而并没有迁移该表对应
的表空间,因此删除后的表如果没有purge,则会一直占用该表空间。
直到表空间不足时,Oracle首先会去回收站中按照先进先出的原则来清
理回收站中的表。
当然,也可以用手动的方式来清理指定的表:
purge table recycletest;
或者使用该对象在回收站中的名字:
purge table "BIN$04LhcpndanfgMAAAAAANPw==$0";
该命令将清除掉所有跟该表相关的对象,包括索引、约束、触发器等。如果想永
久的删除一个索引,则可以:
purge index in_test_01;
这个命令仅仅删除该表的索引,而不影响该表(回收站中的)。

有一种情况,drop表emp后,又创建了emp表,然后再次drop后再创建emp表,再drop:
create table emp(id number(10));
drop table emp;
create table emp(id number(10), name varchar2(100));
drop table emp;
create table emp(id number(10), name varchar2(100), sex varchar2(2));
drop table emp;
当执行purge table emp后,是purge掉哪个表呢?还是全部purge掉?
答案是跟前面自动清理的原理一样,每次清除第一个被drop掉的emp表,判断的依据是user_recyclebin.dropscn,根据该值的大小决定purge的顺序。

另外,如果要对drop掉的表做恢复处理,而此时又已经创建了表emp,那么如何恢复该表呢(由于已经存在该表名,不能直接恢复)。方法:
flashback table emp to before drop rename to emp_old;
即将恢复的表命名为其他名称的一个表。
恢复的时候跟purge的顺序是相反的,即最后一个被drop掉的最先被恢复。

更多内容可以参考该文档:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/flashptr004.htm#sthref634
http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html
Flashback Table

Reinstating an accidentally dropped table is effortless using the Flashback Table feature in Oracle Database 10g

Here's a scenario that happens more often than it should: a user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible. (In some cases, this unfortunate user may even have been you, the DBA!)

Oracle9i Database introduced the concept of a Flashback Query option to retrieve data from a point in time in the past, but it can't flash back DDL operations such as dropping a table. The only recourse is to use tablespace point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.

Enter the Flashback Table feature in Oracle Database 10g, which makes the revival of a dropped table as easy as the execution of a few statements. Let's see how this feature works.

Drop That Table!

First, let's see the tables in the present schema.

SQL> select * from tab;

TNAME                    TABTYPE  CLUSTERID
------------------------ ------- ----------
RECYCLETEST              TABLE

Now, we accidentally drop the table:

SQL> drop table recycletest;

Table dropped.

Let's check the status of the table now.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

The table RECYCLETEST is gone but note the presence of the new table BIN$04LhcpndanfgMAAAAAANPw==$0. Here's what happened: The dropped table RECYCLETEST, instead of completely disappearing, was renamed to a system-defined name. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$04LhcpndanfgMAAAAAANPw==$0, preserving the complete object structure of the dropped table.

The table and its associated objects are placed in a logical container known as the "recycle bin," which is similar to the one in your PC. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The recycle bin is merely a logical structure that catalogs the dropped objects. Use the following command from the SQL*Plus prompt to see its content (you'll need SQL*Plus 10.1 to do this):

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST      BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31

This shows the original name of the table, RECYCLETEST, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:

SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

Voila! The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:

PURGE RECYCLEBIN;

But what if you want to drop the table completely, without needing a flashback feature? In that case, you can drop it permanently using:

DROP TABLE RECYCLETEST PURGE;

This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been pre-10g.

Managing the Recycle Bin

If the tables are not really dropped in this process--therefore not releasing the tablespace--what happens when the dropped objects take up all of that space?

The answer is simple: that situation does not even arise. When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed.

Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.

In addition, there are several ways you can manually control the recycle bin. If you want to purge the specific table named TEST from the recycle bin after its drop, you could issue

PURGE TABLE TEST;

or using its recycle bin name:

PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";

This command will remove table TEST and all dependent objects such as indexes, constraints, and so on from the recycle bin, saving some space. If, however, you want to permanently drop an index from the recycle bin, you can do so using:

purge index in_test1_01;

which will remove the index only, leaving the copy of the table in the recycle bin.

Sometimes it might be useful to purge at a higher level. For instance, you may want to purge all the objects in recycle bin in a tablespace USERS. You would issue:

PURGE TABLESPACE USERS;

You may want to purge only the recycle bin for a particular user in that tablespace. This approach could come handy in data warehouse-type environments where users create and drop many transient tables. You could modify the command above to limit the purge to a specific user only:

PURGE TABLESPACE USERS USER SCOTT;

A user such as SCOTT would clear his own recycle bin with

PURGE RECYCLEBIN;

You as a DBA can purge all the objects in any tablespace using

PURGE DBA_RECYCLEBIN;

As you can see, the recycle bin can be managed in a variety of different ways to meet your specific needs.

Table Versions and Flashback

Oftentimes the user might create and drop the same table several times, as in:

CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (1);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (2);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (3);
COMMIT;
DROP TABLE TEST;

At this point, if you were to flash-back the table TEST, what would the value of the column COL1 be? Conventional thinking might suggest that the first version of the table is retrieved from the recycle bin, where the value of column COL1 is 1. Actually, the third version of the table is retrieved, not the first. So the column COL1 will have the value 3, not 1.

At this time you can also retrieve the other versions of the dropped table. However, the existence of a table TEST will not let that happen. You have two choices:

  • Use the rename option:

    FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;
    FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;
    

    which will reinstate the first version of the table to TEST1 and the second versions to TEST2. The values of the column COL1 in TEST1 and TEST2 will be 1 and 2 respectively. Or,
  • Use the specific recycle-bin names of the table to restore. To do that, first identify the table's recycle bin names and then issue:

    FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2;
    FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST1;
    

    That will restore the two versions of the dropped table.

Be Warned...

The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table.

The information is kept in the view named USER_RECYCLEBIN. Before flashing-back the table, use the following query to retrieve the old names.

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'RECYCLETEST')
AND ORIGINAL_NAME != 'RECYCLETEST';

OBJECT_NAME                    ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpnianfgMAAAAAANPw==$0 IN_RT_01   INDEX
BIN$04LhcpnganfgMAAAAAANPw==$0 TR_RT      TRIGGER

After the table is flashed-back, the indexes and triggers on the table RECYCLETEST will be named as shown in the OBJECT_NAME column. From the above query, you can use the original name to rename the objects as follows:

ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;

One notable exception is the bitmap indexes. When they are dropped, they are not placed in the recycle bin--hence they are not retrievable. The constraint names are also not retrievable from the view. They have to be renamed from other sources.

Other Uses of Flashback Tables

Flashback Drop Table is not limited to reversing the drop of the table. Similar to flashback queries, you can also use it to reinstate the table to a different point in time, replacing the entire table with its "past" version. For example, the following statement reinstates the table to a System Change Number (SCN) 2202666520.

FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;

This feature uses Oracle Data Pump technology to create a different table, uses flashback to populate the table with the versions of the data at that SCN, and then replaces the original table with the new table. To find out how far you can flashback the table, you could use the versioning feature of Oracle Database 10g. (See the Week 1 installment of this series for more details.) It is also possible to specify a timestamp instead of SCN in the flashback clause.

You can read more about the Flashback Table feature in the Oracle Database Administrator's Guide 10g Release 1 (10.1).

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12932950/viewspace-610519/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle写本地文件
请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1022094