Shulou(Shulou.com)05/31 Report--

Editor to share with you in oracle11g altersystem set recyclebin = off error recyclebin parameters in 11g and 10g what are the differences, I believe most people do not know much, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

In Oracle 11g, the parameters of recyclebin changed slightly.

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 documentation, we can see that in 11g, this parameter can still be understood and modified in session and affect the current session, but if it is modified at the system level, it is necessary to add the deferred parameter, which will not affect the currently connected sesion, but the newly connected session 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 >

Let's do a test:

At first, I used the study user to initiate two session connections 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 parameters for this option cannot be modified

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: the object is not in the recycle bin

As you can see, at the system level, modification is not allowed without DEFERRED parameters, but at the session level.

SQL > alter system set recyclebin=off DEFERRED

System altered

SQL >

SESSION 2

Description: when I issued the alter system set recyclebin=off DEFERRED; command in session 1, my session 2 was already connected to ora11g. In this session, we use it to experience

Alter system set recyclebin=off DEFERRED

Command has no effect on the currently connected session

C:\ Documents andSettings\ zhangrp > sqlplus study/study

SQL*Plus: Release 11.1.0.6.0-Productionon Friday February 20 16:20:50 2009

Copyright (c) 1982, 2007, 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 has been created.

SQL > drop table test_2

Table has been deleted.

SQL > flashback table test_2 to beforedrop

Flashback complete.

SQL > desc test_2

Is the name empty? Types

NO NUMBER (38)

SESSION 3

After completing the alter system set recyclebin=off DEFERRED; command in session 1, let's initiate a new connection to see if it works for the new connection:

Microsoft Windows XP [version 5.1.2600]

Copyright 1985-2001Microsoft Corp.

C:\ Documents andSettings\ zhangrp > sqlplus study/study

SQL*Plus: Release 11.1.0.6.0-Productionon Friday February 20 16:23:46 2009

Copyright (c) 1982, 2007, 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

The above is all the contents of this article entitled "what is the difference between 11g and 10g in altersystem set recyclebin = off recyclebin parameters in oracle11g". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.