31 - [Oracle 19C] v$parameter VS v$parameter2

 

 

SQL>  show con_name

CON_NAME
------------------------------
CDB$ROOT


col name for a20
col open_mode for a20
SQL> select name, open_mode from v$pdbs;

NAME                 OPEN_MODE           
-------------------- --------------------
PDB$SEED             READ ONLY           
ORCLPDB              READ WRITE

 

 

 

• V$PARAMETER show info about the PARAMETERs that are currently in effect for the session

--note: when the issys_modifiable=FALSE, this mean it is static parameter
--this mean it could be changed, but in Spfile only and need restart the DB using Spfile
--when you see issys_modifiable=IMMEDIATE , changes can take effect IMMEDIATE
--when you see issys_modifiable=DEFERRED , changes can take effect for future sessions

 

col name for a20
col value for a100
col isses_modifiable for a20
col issys_modifiable for a20
col ispdb_modifiable for a20

SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,con_id
from V$PARAMETER
where name ='control_files'

NAME                 VALUE                                                                                                ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE         CON_ID
-------------------- ---------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ----------
control_files        /u01/app/oracle/oradata/ORCL/control01.ctl, /u01/app/oracle/oradata/ORCL/control02.ctl               FALSE                FALSE                FALSE                         1

 

 

 

--V$PARAMETER2 show info about the PARAMETERs that are currently in effect for the session
--but it is more readable in case the value has many lines

 

SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,con_id
from V$PARAMETER2
where name ='control_files';

NAME                 VALUE                                               ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE         CON_ID
-------------------- --------------------------------------------------- -------------------- -------------------- -------------------- ----------
control_files        /u01/app/oracle/oradata/ORCL/control01.ctl          FALSE                FALSE                FALSE                         1
control_files        /u01/app/oracle/oradata/ORCL/control02.ctl          FALSE                FALSE                FALSE                         1