Skip to content

Alter System Scope, SID and Container

  • by

ALTER SYSTEM SET

When we issue ALTER SYSTEM SET to modify parameter, some scope clauses can be used to specify how new value is applied in the database. For example:

  1. SCOPE Clause
  2. SID Clause
  3. CONTAINER Clause

Without specifying any of these clauses, they fall into their own default values according to the context, which define the final applicable range of the parameter.

1. SCOPE Clause

If you do not specify this clause, then:

  • SPFILE
  • If a server parameter file (SPFILE) was used to start up the database, then BOTH is the default.

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

Started bySCOPE
SPFILEBOTH
PFILEMEMORY

2. SID Clause

If you do not specify this clause, then:

  • SPFILE
  • If the instance was started up with a SPFILE (server parameter file), then Oracle Database assumes SID = '*'.

  • PFILE
  • If the instance was started up with a PFILE (traditional plain-text initialization parameter file), then Oracle Database assumes the SID of the current instance.

Started bySID
SPFILE* (All instances)
PFILE(Current instance)

3. CONTAINER Clause

If you omit this clause, then CONTAINER=CURRENT is the default, not CONTAINER=ALL.

Leave a Reply

Your email address will not be published. Required fields are marked *