Altersystem Set recyclebin = off error recyclebin parameter difference in 11g and 10g

Source: Internet
Author: User
Tags sqlplus


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.