30 - [Oracle 19C] Alter session example, save state

 

Save the PDB state:

 

SQL> select con_name, state from dba_pdb_saved_states;

CON_NAME	     STATE
-------------------- --------------
ORCLPDB 	     OPEN


SQL> alter pluggable database pdb10 save state;

Pluggable database altered.


SQL> select con_name, state from dba_pdb_saved_states;

CON_NAME	     STATE
-------------------- --------------
ORCLPDB 	     OPEN
PDB10		     OPEN

 

 

 

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  MOUNTED
SQL>


SQL> alter pluggable database orclpdb open;

Pluggable database altered.


SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  READ WRITE NO
SQL>

 

 

 

SQL> select name,value from V$PARAMETER where name like 'nls%' order by name;

NAME						   VALUE
-------------------------------------------------- --------------------------------------------------
nls_calendar
nls_comp					   BINARY
nls_currency
nls_date_format
nls_date_language
nls_dual_currency
nls_iso_currency
nls_language					   AMERICAN
nls_length_semantics				   BYTE
nls_nchar_conv_excp				   FALSE
nls_numeric_characters

 

 

 

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

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE
-------------------- -------------------- -------------------- -------------------- --------------------
nls_date_format 			  TRUE		       FALSE		    TRUE



SQL> select sysdate from dual;

SYSDATE
---------
21-NOV-23


SQL> alter session set nls_date_format='yyyy-mm-dd';

Session altered.


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

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE
-------------------- -------------------- -------------------- -------------------- --------------------
nls_date_format      yyyy-mm-dd 	  TRUE		       FALSE		    TRUE


SQL> select sysdate from dual;

SYSDATE
----------
2023-11-21

 

 

• To query all the parameter that we can change it on session level

 

Select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable 
from V$PARAMETER
where isses_modifiable='TRUE'