Re: 10g recyclebin with 9i SQL*Plus clients

Michel Cadot wrote:
> "Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: 1164047313.958978.275050_at_m73g2000cwd.googlegroups.com...
> | old dirtbeard wrote:
> | One possibility - login trigger that executes the following for
> | specific users:
> | ALTER SESSION SET RECYCLEBIN=OFF
> |
> | Another possibility is to completely disable the recycle bin using a
> | similar ALTER SYSTEM command.
> |
> | Charles Hooper
> | PC Support Specialist
> | K&M Machine-Fabricating, Inc.
> |
>
> Take care that due to a bug "alter session" acts almost like "alter system",
> that is when you issue "alter session set recyclebin=off" then all sessions
> see their recyclebin turns off even if if they see "on" querying v$parameter.
> I posted a test case referenced as bug 5609913 on Metalink.
>
> Regards
> Michel Cadot

Thanks for the warning. What I see seems to be a much the same as the bug report, even with 10.2.0.2 patch 9 applied: ___Session 1, USER1___:
SQL> SELECT * FROM RECYCLEBIN;
15 rows selected.

SQL> PURGE RECYCLEBIN;
Recyclebin purged.

SQL> ALTER SESSION SET RECYCLEBIN=OFF;
Session altered.

SQL> CREATE TABLE T3 (TEST1 VARCHAR2(10)); Table created.

SQL> DROP TABLE T3;
Table dropped.

SQL> SELECT * FROM RECYCLEBIN;
no rows selected

SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME LIKE 'recyc%'; VALUE
OFF ___Session 2, USER1___:
SQL> SELECT * FROM V$PARAMETER WHERE NAME LIKE 'recyc%'; no rows selected.

SQL> SELECT value FROM V$PARAMETER WHERE NAME LIKE 'recyc%'; VALUE
ON

SQL> CREATE TABLE T3 (TEST1 VARCHAR2(10)); Table created.

SQL> DROP TABLE T3;
Table dropped.

SQL> SELECT * FROM RECYCLEBIN;
no rows selected

SQL> SELECT value FROM V$PARAMETER WHERE NAME LIKE 'recyc%'; VALUE
ON

___Session 3, USER2___:
SQL> SELECT * FROM RECYCLEBIN;
no rows selected

SQL> CREATE TABLE T3 (TEST1 VARCHAR2(10)); Table created.

SQL> DROP TABLE T3;
Table dropped.

SQL> SELECT * FROM RECYCLEBIN;
no rows selected

SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME LIKE 'recyc%'; VALUE
ON

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Nov 20 2006 - 13:37:54 CST