Search This Blog

Total Pageviews

Sunday, 23 April 2017

How to change Pluggable databse parameter ?


How to change pluggable databse parameter ?  



 
The parameters are set for a PDB and are stored in table PDB_SPFILE$ , across PDB .

[oracle@vbgeneric ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 22 09:49:19 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


or 

alter session set container=cdb$root;


SET LINESIZE 200 PAGESIZE 100 SERVEROUTPUT ON
COLUMN "DB DETAILS" FORMAT A100
SELECT
 'DB_NAME: ' ||sys_context('userenv', 'db_name') ||
 ' \CDB:-' ||(select cdb from v$database) ||
 ' \AUTH_ID: ' ||sys_context('userenv', 'authenticated_identity') ||
 ' \USER: ' ||sys_context('userenv', 'current_user') ||
 ' \CONTAINER:'||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB') "DB DETAILS..."
 FROM DUAL ;

DB DETAILS
----------------------------------------------------------------------------------------------------
DB_NAME: orcl12c \CDB:-YES \AUTH_ID: oracle \USER: SYS \CONTAINER:ANUJ


SQL> show pdbs

 CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 ORCL READ WRITE NO
 4 ORDS READ WRITE NO
 5 ANUJ READ WRITE NO


SET SERVEROUTPUT ON
COLUMN "RESTRICTED" FORMAT A10
select 
 v.name, 
 v.open_mode, 
 nvl(v.restricted, 'n/a') "RESTRICTED", 
 d.status from v$pdbs v , dba_pdbs d
where 1=1
and v.guid=d.guid
order by v.create_scn;

NAME OPEN_MODE RESTRICTED STATUS
------------------------- ---------- ---------- ---------
PDB$SEED READ ONLY NO NORMAL
ORCL READ WRITE NO NORMAL
ORDS READ WRITE NO NORMAL
ANUJ READ WRITE NO NORMAL



Pluggable database parameter ..

col container_name for a20
col parameter for a20
col value$ for a30
select container.con_id,container.name container_name, par.name PARAMETER,par.value$ from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
order by 1

 CON_ID CONTAINER_NAME PARAMETER VALUE$
---------- -------------------- -------------------- ------------------------------
 2 PDB$SEED max_string_size 'EXTENDED'
 3 ORCL max_string_size 'EXTENDED'
 3 ORCL job_queue_processes 2
 3 ORCL db_securefile 'PREFERRED'
 3 ORCL open_cursors 50
 4 ORDS max_string_size 'EXTENDED'
 5 ANUJ max_string_size 'EXTENDED'
 5 ANUJ open_cursors 300

8 rows selected.



SQL> alter session set container=ANUJ;

Session altered.



set linesize 200 
col name for a25
col VALUE for a20
select name, con_id, value from v$system_parameter where name='open_cursors';

NAME CON_ID VALUE
------------------------- ---------- --------------------
open_cursors 0 300


SQL> alter system set open_cursors=301 scope=both;

System altered.

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 301



set linesize 200
col name for a25
col VALUE for a20
select name, con_id, value from v$system_parameter where name='open_cursors';

NAME CON_ID VALUE
------------------------- ---------- --------------------
open_cursors 5 301



set linesize 200
col name for a25
col VALUE for a20
col CON_NAME for a20
select s.name, s.con_id, p.name CON_NAME,s.value from v$system_parameter s,v$pdbs p 
where 1=1
and s.con_id=p.con_id
and s.name='open_cursors';

NAME CON_ID CON_NAME VALUE
------------------------- ---------- -------------------- --------------------
open_cursors 5 ANUJ 301


set linesize 200
col name for a25
col VALUE for a20
col CON_NAME for a20
select s.name, s.con_id, p.name CON_NAME,s.value from v$system_parameter s,v$pdbs p 
where 1=1
and s.con_id=p.con_id
-- and s.name='open_cursors';

NAME CON_ID CON_NAME VALUE
------------------------- ---------- -------------------- --------------------
resource_manager_plan 5 ANUJ
open_cursors 5 ANUJ 301
max_string_size 5 ANUJ EXTENDED



alter session set container=cdb$root;


SET LINESIZE 200 PAGESIZE 100 SERVEROUTPUT ON
COLUMN "DB DETAILS" FORMAT A100
SELECT
 'DB_NAME: ' ||sys_context('userenv', 'db_name') ||
 ' \CDB:-' ||(select cdb from v$database) ||
 ' \AUTH_ID: ' ||sys_context('userenv', 'authenticated_identity') ||
 ' \USER: ' ||sys_context('userenv', 'current_user') ||
 ' \CONTAINER:' ||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB') "DB DETAILS"
 FROM DUAL ;

DB DETAILS
----------------------------------------------------------------------------------------------------
DB_NAME: orcl12c \CDB:-YES \AUTH_ID: oracle \USER: SYS \CONTAINER:CDB$ROOT



set linesize 200
col name for a25
col VALUE for a20
col CON_NAME for a20
select s.name, s.con_id, p.name CON_NAME,s.value from v$system_parameter s,v$pdbs p 
where 1=1
and s.con_id=p.con_id
and s.name='open_cursors';


NAME CON_ID CON_NAME VALUE
------------------------- ---------- -------------------- --------------------
open_cursors 3 ORCL 50
open_cursors 5 ANUJ 301



col container_name for a10
col parameter for a20
col value$    for a30
select container.name container_name, par.name PARAMETER,par.value$ from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
-- and par.name = 'cursor_sharing';

CONTAINER_ PARAMETER            VALUE$
---------- -------------------- ------------------------------
ORCL       job_queue_processes  2
PDB$SEED   max_string_size      'EXTENDED'
ORCL       max_string_size      'EXTENDED'
ORCL       db_securefile        'PREFERRED'
ORDS       max_string_size      'EXTENDED'
ORCL       open_cursors         50
ANUJ       max_string_size      'EXTENDED'
ANUJ       open_cursors         301

8 rows selected.


No comments:

Oracle DBA

anuj blog Archive