Daniel Westermann's Blog

saturday, early in the morning morning, everybody else is still sleeping. a perfect time to do one of the oracle on demand sessions I wanted to complete:

grrr….

some time ago I blogged on how to list the installed patches for an oracle database. if you are on 12c there is another possibility:

SQL> select dbms_qopatch.get_opatch_lsinventory from dual;
 
    OracleHome-94047000-c49c-4d73-b13c-d252248d3c09
    oracle_home
    /opt/oracle/oraInventory
    false
    oneoff
    /opt/oracle/product/base/12.1.0.1
    oracle_home
  
  
....

this returns a xml of the opatch inventory right out of the database. another nice little feature introduced with 12c.

if you check the documentation you’ll notice that there are some other functions you might use to query the optach inventory.

oracle 12c introduced pdb level parameters:

select name from v$parameter where ISPDB_MODIFIABLE='TRUE';
NAME
--------------------------------------------------------------------------------
sessions
timed_statistics
timed_os_statistics
resource_limit
....

all of these parameters can be set per pdb, so lets have a look at one of the parameters. I’ll choose optimizer_dynamic_sampling for this little demonstration.

at the cdb level my value of optimizer_dynamic_sampling is:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter optimizer_dynamic_sampling

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling	     integer	 2

this is reflected in the spfile, too:

[oracle@localhost dbs]$ strings spfileorcl.ora | grep optimizer_dynamic_sampling
*.optimizer_dynamic_sampling=2

lets connect to the pdb and see what is the value there:

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> show parameter optimizer_dynamic_sampling

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling	     integer	 2

same as in the cdb. lets change it:

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> alter system set optimizer_dynamic_sampling=4 scope=both;

System altered.

SQL> show parameter optimizer_dynamic_sampling

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling	     integer	 4

going back to the cdb we can see that the cdb level parameter was not touched:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter optimizer_dynamic_sampling

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling	     integer	 2
SQL> 

but where does oracle store the pdb level parameters? in the spfile?

[oracle@localhost dbs]$ strings spfileorcl.ora | grep optimizer_dynamic_sampling
*.optimizer_dynamic_sampling=2

obviously not. so, lets trace:

SQL> alter session set tracefile_identifier=PARAMETER_CHECK;

Session altered.

alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> show parameter optimizer_dynamic_sampling

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling	     integer	 4

looking at the trace file we can see that the following happens:

PARSING IN CURSOR #139664056367904 len=289 dep=0 uid=0 oct=3 lid=0 tim=14602003422 hv=2462394820 ad='75206100' sqlid='7cfz5wy9caaf4'
     SELECT NAME NAME_COL_PLUS_SHOW_PARAM
      , DECODE(TYPE,1,'boolean',2,'string',3,'integer'
      , 4,'file',5,'number', 6,'big integer', 'unknown') TYPE
      , DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM 
   FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) 
  ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
END OF STMT

substituting the bind variable gives exactly the results we expect:

SELECT NAME NAME_COL_PLUS_SHOW_PARAM
     , DECODE(TYPE,1,'boolean',2,'string',3,'integer'
     , 4,'file',5,'number', 6,'big integer', 'unknown') TYPE
     , DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM 
  FROM V$PARAMETER 
 WHERE UPPER(NAME) LIKE UPPER('OPTIMIZER_DYNAMIC_SAMPLING') 
 ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM;
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling	     integer	 4

but this does not really help, does it? the magic must be hidden in the v$parameter view, so lets see what is being done there (v$parameter is based on gv$parameter):

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl
   , ksppstdf, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE')
   , decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED'
   , 3,'IMMEDIATE','FALSE')
   , decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE')
   , decode(bitand(ksppiflg,4),4,'FALSE'
   , decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE'))
   , decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')
   , decode(bitand(ksppstvf,2),2,'TRUE','FALSE')
   , decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE')
   , decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE')
   , ksppdesc
   , ksppstcmnt
   , ksppihash
   , x.con_id  
from x$ksppi x, x$ksppcv y 
where (x.indx = y.indx) and  bitand(ksppiflg,268435456) = 0 
  and ((translate(ksppinm,'_','#') not like '##%') 
  and ((translate(ksppinm,'_','#') not like '#%')
   or (ksppstdf = 'FALSE') 
   or (bitand(ksppstvf,5) > 0)))

ah, there is a con_id column in x$ksppi. but no where clause on this column, so:

SQL> select distinct con_id from x$ksppi;

    CON_ID
----------
	 3

… only container 3 is reflected in the x$ table, which is my pdb:

SQL> select con_id, name from v$pdbs;

    CON_ID NAME
---------- ------------------------------
	 2 PDB$SEED
	 3 PDB1

doing the same in the cdb lists all containers:

SQL> select distinct con_id from x$ksppi;

    CON_ID
----------
	 1
	 2
	 3

the answer is: the pdb level parameters are stored in the data dictionary of the pdb and each pdb just sees the paramters which are valid for just the pdb and nothing else. makes sense, as you may unplug and plug pdbs and for sure are happy to have the same parameters at the pdb level once you plug into a different instance.

another little, but fine improvement in oracle 12c: Now you may issue sql statements directly in rman without the need to use the “SQL ‘…'” syntax:

rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jun 29 08:40:34 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBS300 (DBID=3276277814)

RMAN> select instance_name from v$instance;

using target database control file instead of recovery catalog
INSTANCE_NAME   
----------------
dbs300          

RMAN> alter system switch logfile;

Statement processed

RMAN> shutdown;

database closed
database dismounted
Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area     334036992 bytes

Fixed Size                     2288104 bytes
Variable Size                268437016 bytes
Database Buffers              58720256 bytes
Redo Buffers                   4591616 bytes

RMAN> select status from v$instance;

STATUS      
------------
OPEN        

RMAN> 

a lot of people define varchar2 columns this way:

create table t1 ( a varchar2(1) );

… and expect that they will be able to insert one character into this column. well, this is not always true. the default for a varchar2 column is byte, so the above statement is equal to:

create table t1 ( a varchar2(1 byte) );

The length semantics of character data types are measurable in bytes or characters. The treatment of strings as a sequence of bytes is called byte semantics. This is the default for character data types. The treatment of strings as a sequence of characters is called character semantics. A character is a code point of the database character set.

the data dictionary does reflect this if you know what the default is:

SQL> create table t1 ( a varchar2(1 char), b varchar2(1));

Table created.

SQL> desc t1;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 A						    VARCHAR2(1 CHAR)
 B						    VARCHAR2(1)

SQL> 

so, if you work with multibyte character sets and do not take care of this, this may happen:

create table t1 ( a varchar2(1 char), b varchar2(1));
insert into t1 (a) values (chr(1000));

1 row created.

SQL> insert into t1 (b) values (chr(1000));
insert into t1 (b) values (chr(1000))
                           *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."T1"."B" (actual: 2, maximum: 1)

postgresql provides it, msssql (I think) provides it, too. and finally, with release 12c, you may have auto incrementing columns in oracle:

SQL> create table t1 ( a number generated always as identity, b number );
SQL> insert into t1 (b) values (1);

1 row created.

SQL> insert into t1 (b) values (2);

1 row created.

SQL> select * from t1;

	 A	    B
---------- ----------
	 1	    1
	 2	    2

what happens in the background is quite simple: oracle creates a sequence:

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
--------------------------------------------------------------------------------
ISEQ$$_19540

user/dba/all_tables has a new column to refect this:

SQL> select HAS_IDENTITY from user_tables where table_name = 'T1';

HAS
---
YES

but it is not possible to have more than one of these:

SQL> create table t2 ( a number generated always as identity, b number generated always as identity );
create table t2 ( a number generated always as identity, b number generated always as identity )
                                                                                               *
ERROR at line 1:
ORA-30669: table can have only one identity column

once the table is dropped, the sequence is gone, too:

SQL> drop table t1;

Table dropped.

SQL> select sequence_name from user_sequences;

no rows selected

SQL> 

oracle 12c introduced a nice feature: move data files online:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 16:48:29 2013

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


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

SQL> create tablespace tbs1 datafile '/oradata/dbs300/dbf/tbs1_01.dbf' size 10m;

Tablespace created.

SQL> alter database move datafile '/oradata/dbs300/dbf/tbs1_01.dbf' to '/oradata/dbs300/dbf/tbs1_02.dbf';

Database altered.

SQL> 

the trace file generated gives some more details about the move operation:

Moving datafile /oradata/dbs300/dbf/tbs1_01.dbf (16) to /oradata/dbs300/dbf/tbs1_02.dbf
The secondary file /oradata/dbs300/dbf/tbs1_02.dbf is created
Blocks copied for file /oradata/dbs300/dbf/tbs1_02.dbf
Move operation committed for file /oradata/dbs300/dbf/tbs1_02.dbf
Move operation completed for file /oradata/dbs300/dbf/tbs1_02.dbf
Moving datafile /oradata/dbs300/dbf/tbs1_01.dbf (16) to /oradata/dbs300/dbf/tbs1_02.dbf

*** 2013-06-28 17:28:26.437
The secondary file /oradata/dbs300/dbf/tbs1_02.dbf is created

The number in brackets (16) is the file id:

SQL> select FILE_ID from dba_data_files where file_name = '/oradata/dbs300/dbf/tbs1_02.dbf';

   FILE_ID
----------
	16

SQL> 

wow: three releases, three different file namings:

em12 donwload page

in note 1169017.1 oracle announced that the setting of “similar” for the cursor_sharing parameter will be deprecated as of 12c: “The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g.”

the current documentation is up to date and “similar” is not mentioned anymore, but:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 15:33:34 2013

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


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

SQL> show parameter sharing

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 EXACT
SQL> alter system set cursor_sharing='similar';

System altered.

SQL> show parameter sharing

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 similar
SQL> 

so, the ability to set this is still there …