数据库的一张表被DROP掉了，而且RECYCLEBIN功能没开，回收站里肯定没这张表。

secooler

针对数据库默认开启闪回被删除的表功能，简单做了一个实验《【recyclebin】彻底禁用 Oracle回收站功能》分享在此。
http://space.itpub.net/519536/viewspace-660038

secooler

Yong Huang

原帖由 secooler 于 2010-4-24 20:24 发表
针对数据库默认开启闪回被删除的表功能，简单做了一个实验《【recyclebin】彻底禁用 Oracle回收站功能》分享在此。
http://space.itpub.net/519536/viewspace-660038

secooler

> sec@ora10g> alter session set recyclebin=off;

In 10g, that will turn off other sessions' recyclebin as well. See Note.444032.1 (Ext/Mod "Alter Session Set Recyclebin=Off" Seems To Impact Other Sessions). Although it doesn't mention workaround, all you need to do is use "alter system" to set it to on, or even "alter session", to take advantage of this bug.

Since the note mentions an SGA variable, I checked its value in SGA. Test in 10.2.0.4.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kd_recyclebin
ub4 KD_RECYCLEBIN_ [060020060, 060020064) = 00000001
SQL> alter session set recyclebin = off;

Session altered.

SQL> oradebug dumpvar sga kd_recyclebin
ub4 KD_RECYCLEBIN_ [060020060, 060020064) = 00000000
SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

This session's recyclebin is indeed OFF. But the problem is that the SGA variable kd_recyclebin is also off. So open a new session to this database:

sqlplus yhuang@mydb
...
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
...
SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
recyclebin                           string      on
SQL> create table testdrop (x int);

Table created.

SQL> drop table testdrop;

Table dropped.

SQL> flashback table testdrop to before drop;
flashback table testdrop to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

If I now alter session set recyclebin = on, even in the first session, this second session can un-drop a dropped table now.

Note that kd_recyclebin is an SGA variable; if you log into another session, you can see its current value as well. (To use oradebug, you have to login as sys.) The value in v$parameter (or show parameter) or v$system_parameter for recyclebin is "ON", instead of "on", although isdefault is still TRUE.

The problem does not occur in 11g. Also, the SGA variable no longer exists in 11g.

Yong Huang

[ 本帖最后由 Yong Huang 于 2010-4-26 07:57 编辑 ]

xsmdel

Learning to Huang Yong

secooler

感谢Huang版的指正与分享。

附一下MOS中的描述，方便没有账号的朋友们参考。
"Alter Session Set Recyclebin=Off" Seems To Impact Other Sessions [ID 444032.1]

           Modified  17-SEP-2009     Type PROBLEM     Status MODERATED           

In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.
Symptoms

When execute "alter session set recyclebin=off;" in a session, behaves as though it is "alter system" statement and makes changes system wide instead of confining it to session wide.

EXAMPLE:
-------
SYSTEM> create user pat1 identified by pat1 quota unlimited on ts_d01;

User created.

SYSTEM> grant create session, create table to pat1;

Grant succeeded.

SYSTEM> create user pat2 identified by pat2 quota unlimited on ts_d01;

User created.

SYSTEM> grant create session, create table to pat2;

Grant succeeded.

SYSTEM> connect / as sysdba
Connected.
***SYS***> grant select on v_$parameter to pat1;

Grant succeeded.

***SYS***> grant select on v_$parameter to pat2;

Grant succeeded.

***SYS***> connect pat1/pat1
Connected.

PAT1> select value from v$parameter where name='recyclebin';
VALUE
----------
on

1 row selected.

PAT1> create table t (col number);

Table created.

PAT1> select count(*) from user_recyclebin;

COUNT(*)
--------
0

1 row selected.

PAT1> drop table t;

Table dropped.

PAT1> select count(*) from user_recyclebin;

COUNT(*)
--------
1

1 row selected.

PAT1> create table t (col number);

Table created.

PAT1> alter session set recyclebin=off;

Session altered.

PAT1> select value from v$parameter where name='recyclebin';

VALUE
----------
OFF

1 row selected.

PAT1> drop table t;

Table dropped.

PAT1> select count(*) from user_recyclebin;

COUNT(*)
--------
1

1 row selected.

PAT1> connect pat2/pat2
Connected.

PAT2> -- ###### RECYCLE BIN IS ON... ######
PAT2> select value from v$parameter where name='recyclebin';
VALUE
---------------------------------------------------------------------
on

1 row selected.

PAT2> create table t (col number);

Table created.

PAT2> select count(*) from user_recyclebin;

COUNT(*)
--------
0

1 row selected.

PAT2> -- ###### BUT WHEN WE DROP THE TABLE IT DOES NOT GO INTO IT ######
PAT2> drop table t;

Table dropped.

PAT2> select count(*) from user_recyclebin;

COUNT(*)
--------
0

1 row selected.

PAT2> create table t (col number);

Table created.

PAT2> -- ###### IF WE EXPLICITLY ENABLE THE RECYCLE BIN... ######
PAT2> alter session set recyclebin=on;

Session altered.

PAT2> select value from v$parameter where name='recyclebin';
VALUE
--------------------------------------------------------------------
ON

1 row selected.

PAT2> -- ###### THEN IT WORKS ######
PAT2> drop table t;

Table dropped.

PAT2> select count(*) from user_recyclebin;

COUNT(*)
--------
1

1 row selected.
Cause
Bug 5609913
Abstract ''ALTER SESSION SET RECYCLEBIN=OFF'' SEEMS TO IMPACT OTHER SESSIONS

There is no differentiation made between alter session/alter system and SGA variable KD_RECYCLEBIN was changed because of which when we run the above alter command in session 1 and user 1 - it behaves just like alter system.

Precisely,"alter session set recyclebin=..." behaves as though it is "alter system" statement and makes the changes system wide instead of confining it to session wide.
Solution
The Bug 5609913 is fixed in the version 11g.

NOTE:Backport is not feasible as the SGA needs to be rebuilt due to init param "recyclebin" change.
References
BUG:5609913 - ''ALTER SESSION SET RECYCLEBIN=OFF'' SEEMS TO IMPACT OTHER SESSIONS


Show Related Information Related
Products

    * Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

[ 本帖最后由 secooler 于 2010-4-26 22:24 编辑 ]