in the 11g in Altersystem Set recyclebin = Off Error RecycleBin parameters in 11g and the 10g the difference in
in the in Oracle 11g, the RecycleBin parameter has undergone minor changes .
This supported parameter was introduced inoracle 10.2.0
Version parametername Data Type Session modifiable System modifiable
10.2.0 recyclebin String TRUE IMMEDIATE
11.1.0 RecycleBin String TRUE DEFERRED
from the document we can see this change, in the 11g , this parameter in the session can still understand the changes and affect the current session, but if it is at the system level changes, then you need to add the deferred parameter has no effect on the currently connected sesion , but the session of the new connection will be affected
Sql> Select name,isses_modifiable,issys_modifiable from V$parameter where name= ' recyclebin ';
NAME isses_modifiable issys_modifiable
----------- --------------------------------
RecycleBin TRUE DEFERRED
Sql>
Here's a test to see :
at first , I used study users to initiate 2 sessions to connect to my ora11g database.
SESSION 1
Connected to Oracle Database 11g enterpriseedition Release 11.1.0.6.0
Connected as study
Sql> Show Parameter RecycleBin
NAME TYPE VALUE
-------------- ----------- --------------
RecycleBin string on
Sql> alter system set Recyclebin=off;
Alter system set Recyclebin=off
ORA-02096: The specified initialization parameter for this option is not modifiable
Sql> alter session set Recyclebin=off;
Session Altered
Sql> CREATE TABLE ZRP (no int);
Table created
sql> drop table ZRP;
Table dropped
Sql> Flashback table Zrp to before drop;
Flashback table ZRP to before drop
ORA-38305: object is not in recycle bin
As you can see , at the system level , the no -DEFERRED parameter is not allowed to be modified, but can be modified at the session level .
Sql> alter system set Recyclebin=off DEFERRED;
System Altered
Sql>
SESSION 2
Note : the alter system set Recyclebin=off DEFERRED is issued in session 1; command , my session 2 has been connected to the ora11g. in this session , we are used to understand
Alter system set Recyclebin=off DEFERRED;
the command does not affect the currently connected session
C:\Documents Andsettings\zhangrp>sqlplus Study/study
Sql*plus:release 11.1.0.6.0-productionon Friday 2 months 20 16:20:50 2009
Copyright (c) 1982, Oracle. Allrights reserved.
Connect to :
Oracle Database 11g Enterprise editionrelease 11.1.0.6.0-production
With the partitioning, OLAP, Data miningand Real Application Testing Options
Sql> Show Parameter RecycleBin
NAME TYPE VALUE
-------------- ----------- --------------
RecycleBin string on
Sql> CREATE TABLE test_2 (no int);
The table is created.
sql> drop table test_2;
The table has been deleted.
Sql> Flashback table Test_2 to Beforedrop;
Flash back complete.
Sql> desc test_2
name Is it empty ? type
------------ --------- -----------
NO Number (38)
SESSION 3
complete The alter system set Recyclebin=off DEFERRED in session 1; after the command , we start a new connection to see if it is valid for the new connection :
Microsoft Windows XP [ version 5.1.2600]
(C) copyright 1985-2001microsoft Corp.
C:\Documents Andsettings\zhangrp>sqlplus Study/study
Sql*plus:release 11.1.0.6.0-productionon Friday 2 months 20 16:23:46 2009
Copyright (c) 1982, Oracle. Allrights reserved.
Connect to :
Oracle Database 11g Enterprise editionrelease 11.1.0.6.0-production
With the partitioning, OLAP, Data miningand Real Application Testing Options
Sql> Show Parameter RecycleBin
NAME TYPE VALUE
-------------- ----------- --------------
RecycleBin string OFF
This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://pizibaidu.blog.51cto.com/1361909/1691940
Altersystem Set recyclebin = off error recyclebin parameter difference in 11g and 10g