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.

How to create Pluggable database from SEED database ?

How to Create   Pluggable database ??

To check current environment of CDB and PDB


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


set linesize 200 pagesize 200 
col FILE_NAME for a70
SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ;

 CON_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ----------------------------------------------------------------------
 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf
 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf
 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf
 5 SYSTEM /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf
 5 SYSAUX /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf
 5 USERS /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf
 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf

19 rows selected.


How to check seed database path ?


SQL> alter session set exclude_seed_cdb_view=false;

Session altered.

show parameter EXCLUDE_SEED_CDB_VIEW

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
exclude_seed_cdb_view boolean FALSE


Now seed database file ..

set linesize 200 pagesize 200 
col FILE_NAME for a70
SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ;

 CON_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ----------------------------------------------------------------------
 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf
 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf
 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf
 2 SYSTEM /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
 2 SYSAUX /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
 5 SYSTEM /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf
 5 SYSAUX /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf
 5 USERS /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf
 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf

21 rows selected.




 alter system set "EXCLUDE_SEED_CDB_VIEW"=FALSE ;

System altered.


set linesize 200 pagesize 200 
col FILE_NAME for a70
SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ;

 CON_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ----------------------------------------------------------------------
 2 SYSTEM /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
 2 SYSAUX /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf
 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf
 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf
 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf

18 rows selected.



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

=========================================

To check seed database file only 

set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME  from cdb_data_files   where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' ) 
union 
select TABLESPACE_NAME, FILE_NAME  from cdb_temp_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' );



set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME  from cdb_data_files   where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' )
union
select TABLESPACE_NAME, FILE_NAME  from cdb_temp_files where CON_ID=( select CON_ID   from v$pdbs where NAME='PDB$SEED' );

no rows selected


Check this parameter ...

SQL> show parameter EXCLUDE_SEED_CDB_VIEW

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
exclude_seed_cdb_view                boolean     TRUE


SQL> alter session set EXCLUDE_SEED_CDB_VIEW=false ;

Session altered.


set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME  from cdb_data_files   where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' )
union
select TABLESPACE_NAME, FILE_NAME  from cdb_temp_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' );


TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEMP                           /u01/app/oracle/oradata/orcl12c/pdbseed/pdbseed_temp012016-06-02_07-10-28-AM.dbf
SYSAUX                         /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
SYSTEM                         /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

==========================================================

 As seed database path 

from 
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
to 
/u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf

So convert parameter .. 
FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/')

Create Pluggable databse ..


SQL> SQL>
CREATE PLUGGABLE DATABASE anuj ADMIN USER vihaan IDENTIFIED BY vihaan123
storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/');

Pluggable database created.


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 MOUNTED n/a NEW

SQL> alter pluggable database ANUJ open ;

Pluggable database altered.


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

==============================

 pwd
/u01/app/oracle/oradata/vihcdb1

 ls -ltr
total 2439372
drwxr-xr-x 2 oracle oinstall      4096 Sep 23 13:53 pdbseed
-rw-r----- 1 oracle oinstall  20979712 Sep 23 15:15 temp01.dbf
-rw-r----- 1 oracle oinstall  52429312 Sep 23 21:16 redo02a.log
-rw-r----- 1 oracle oinstall 524296192 Sep 23 21:21 users01.dbf
-rw-r----- 1 oracle oinstall 734011392 Sep 24 03:52 system01.dbf
-rw-r----- 1 oracle oinstall  52429312 Sep 24 04:01 redo03a.log
-rw-r----- 1 oracle oinstall 466624512 Sep 24 04:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 576724992 Sep 24 04:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall  52429312 Sep 24 04:05 redo01a.log
-rw-r----- 1 oracle oinstall  18038784 Sep 24 04:05 ora_control1.ctl
-rw-r----- 1 oracle oinstall  18038784 Sep 24 04:05 ora_control2.ctl

[oracle@cl-ora vihcdb1]$ mkdir ANUJ
[oracle@cl-ora vihcdb1]$ cd ANUJ/
[oracle@cl-ora ANUJ]$ pwd
/u01/app/oracle/oradata/vihcdb1/ANUJ
[oracle@cl-ora ANUJ]$ !sql

CREATE PLUGGABLE DATABASE anuj ADMIN USER vihaan IDENTIFIED BY vihaan123
SQL>   2  storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
  3  DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/vihcdb1/ANUJ/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
  4  FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/');


Pluggable database created.


CREATE PLUGGABLE DATABASE anuj ADMIN USER vihaan IDENTIFIED BY vihaan123
SQL>   2  storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
  3  DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/vihcdb1/ANUJ/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
  4  FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/');


Pluggable database created.

SQL> SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ANUJ                           MOUNTED
SQL> alter session set container=anuj ;
 
Session altered.

SQL> startup ;
Pluggable Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ANUJ                           READ WRITE NO