Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The description of deferred and scope keyword is listed below (extracted from Oracle documentation). The meaning of them gives me an indication that deferred can just use the memory scope. Is my understanding right? Thanks.

DEFERRED

The DEFERRED keyword sets or modifies the value of the parameter for future sessions that connect to the database. Current sessions retain the old value.

SCOPE

The SCOPE clause lets you specify when the change takes effect. Scope depends on whether you started up the database using a traditional plain-text parameter file (pfile) or server parameter file (spfile).

MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.

SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference.

BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

share|improve this question
add comment

1 Answer 1

I've checked experimentally (Oracle 11.1) that actually ALTER SYSTEM ... DEFERRED SCOPE=SPFILE is allowed, although it seems to me that DEFERRED is ignored in this context. Also SCOPE=BOTH and SCOPE=MEMORY are allowed, obviously.

What you cannot do (the manual that you quote overlooked it) is ALTER SYSTEM ... DEFERRED on the parameter that has (V$PARAMETER.ISSYS_MODIFIABLE, V$PARAMETER.ISSES_MODIFIABLE) = (IMMEDIATE, FALSE). This results in:

ORA-02096: specified initialization parameter is not modifiable with this option
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.