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

This article mainly explains "how to judge and modify the dynamic and static parameters in SQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to judge and modify the dynamic and static parameters in SQL.

The ISSYS_MODIFIABLE column in the view v$parameter can view the parameter properties, ISSYS_MODIFIABLE = 'IMMEDIATE' is a dynamic parameter, ISSYS_MODIFIABLE =' FALSE' is a static parameter, and ISSYS_MODIFIABLE = 'DERERRED' is a delay parameter (the current session is not valid, subsequent sessions are valid).

1 Total number of parameters

SQL > select count (*) from v$parameter where issys_modifiable='IMMEDIATE';-dynamic

COUNT (*)

-

two hundred and twenty eight

SQL > select count (*) from v$parameter where issys_modifiable='FALSE';-static

COUNT (*)

-

one hundred and sixteen

SQL > select count (*) from v$system_parameter where issys_modifiable='DEFERRED';-delay

COUNT (*)

-

eight

2 check whether the oracle parameter is static or dynamic (based on the value of ISSYS_MODIFIABLE)

SQL > select name,value,ISSYS_MODIFIABLE from v$parameter where name = 'undo_retention'

NAME VALUE ISSYS_MOD

Undo_retention 1500 IMMEDIATE

3 the difference of SCOPE=SPFILE/MEMORY/BOTH in ALTER SYSTEM SET:

SCOPE = SPFILE

(this change is written to the initialization parameter file and will take effect the next time you start. Dynamic parameters are as good as static parameters. It is also the only way static parameters can be used. )

SCOPE = MEMORY

It is only modified in memory and takes effect immediately, but it will no longer take effect after reboot because it is not written to the initialization parameter file. Only applicable to dynamic parameters, static parameters are not allowed. )

SCOPE = BOTH.

(both written to the initialization parameter file and modified in memory, effective immediately. Similarly, it is only applicable to dynamic parameters, while static parameters are not allowed. )

4 modification of parameters

A) dynamic parameters (only dynamic parameters can be avoided)

SQL > alter system set undo_retention=10800 scope=both

System altered.

B) static parameters

SQL > alter system set processes=151 scope=spfile

System altered.

If you don't know whether it is static or dynamic, you will report an error after making it, and then correct it.

SQL > alter system set processes=151 scope=both

Alter system set processes=151 scope=both

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

Note: (under scope=spfile or scope=both) any parameter changes can not be automatically synchronized to pfile, but will only be written into spfile, so it is best to create pfile from spfile after parameter modification, and manually synchronize parameters to create pfile to ensure that the database can be started normally through pfile next time.

At this point, I believe you have a deeper understanding of "how to judge and modify the dynamic and static parameters in SQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.