11.2.0.2 and onwards: v$parameter reflects the NLS session parameters, not the NLS instance parameters

After a migration of a database from single-byte to multi-byte character set, I wanted to compare the nls_parameters.

As a part of the migration, we had to change semantics on several of our tables from BYTE to CHAR to accommodate for expanding data.

One issue that came up during our initial discussions on how to complete the migration, was if it would be beneficial to set nls_length_semantics to CHAR in the init.ora file. But according to Oracle's documentation, the NLS_LENGTH_SEMANTICS should be kept to BYTE at database level.(Check Doc ID 144808.1 "Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)", section G: "Summary of best practices") so that idea was rejected.

After the import was finished, I wanted to compare the parameters to quality check the new instance.

I queried the v$parameter view, as many DBAs would normally do and found that nls_length_semantics seemed to have been set to CHAR at the instance level:

select name,value from v$parameter where name = 'nls_length_semantics';

NAME	VALUE
nls_length_semantics	CHAR

After a quick search on My Oracle Support I found Doc ID 1368966.1 "NLS settings like NLS_DATE_FORMAT in spfile or init.ora ignored, incorrect or not as expected on Oracle 11.2.0.2 and higher"

Turns out that this is not an error, but the result of a corrected bug (bug no 8722860).

In previous releases, a query of v$parameter or "show parameter" in sql*plus would reflect the instance parameters.
This is incorrect, as Oracle points out:

The documentation set states that :
* the SQL*Plus command show parameter displays the values of initialization parameters in effect for the current session.
* the V$PARAMETER view displays information about the initialization parameters that are currently in effect for the session


As Oracle puts it
"From 11.2.0.2 onwards V$PARAMETER and the SQL*Plus command show parameter reflect the session NLS settings as it should."

So make sure to use v$system_parameter instead, which is reflecting the correct instance setting:

select name,value from V$SYSTEM_PARAMETER where name = 'nls_length_semantics';

NAME	VALUE
nls_length_semantics	BYTE

Sources:
Doc ID 144808.1 "Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)"
Doc ID 1368966.1 "NLS settings like NLS_DATE_FORMAT in spfile or init.ora ignored, incorrect or not as expected on Oracle 11.2.0.2 and higher"
Doc ID 241047.1: "The Priority of NLS Parameters Explained (Where To Define NLS Parameters)

What is the package SYS.USER_LOCK?

Recenty I was migrating a database with a package that wouldn't compile because it couldn't find the package called upon in its code:

SQL> ALTER PACKAGE mypackage compile body; 

Warning: Package body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY MVA3.MYPACKAGE:

PLS-00201: identifier 'USER_LOCK.SLEEP' must be declared

According to PSOUG, there are "four separate ways to induce a sleep into PL/SQL."

•dbms_backup_restore
•dbms_drs
•dbms_lock
•user_lock


PSOUG continues: "Note that USER_LOCK package is not part of the default Oracle installation"

Install as follows:

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba @userlock.sql

How to work around error from opatch "LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo"

I recently had to investigate the patch level for an Oracle 10g database prior to an upgrade of an OAS application database.

This is the error I ran into:

myserver:asdb:ora10g>opatch lsinventory
Invoking OPatch 10.2.0.4.3

 
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Fix this by running the attachHome.sh script in $ORACLE_HOME/oui/bin.
The Oracle Universal Installer allows you to set up an Inventory from scratch, or to register an existing ORACLE_HOME in an already existing Inventory.

myserver:asdb:ora10g>cd $ORACLE_HOME/oui/bin
/u01/oracle/product/ora10g/oui/bin
myserver:asdb:ora10g>./attachHome.sh
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/oracle/oraInventory11g
'AttachHome' was successful.

Result:

myserver:asdb:ora10g>opatch lsinventory
Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/ora10g
Central Inventory : /u01/oracle/oraInventory11g
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.3
OUI version       : 10.2.0.4.0
OUI location      : /u01/oracle/product/ora10g/oui
Log file location : /u01/oracle/product/ora10g/cfgtoollogs/opatch/opatch2016-01-18_16-01-17PM.log

Lsinventory Output file location : /u01/oracle/product/ora10g/cfgtoollogs/opatch/lsinv/lsinventory2016-01-18_16-01-17PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (4):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Products                                         10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2                            10.2.0.3.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 4 products installed in this Oracle Home.


Interim patches (3) :

Patch  6979801      : applied on Fri Mar 06 10:47:02 CET 2009
   Created on 5 Feb 2009, 15:35:25 hrs PST8PDT
   Bugs fixed:
     6979801

Patch  6972843      : applied on Tue Oct 14 17:35:12 CEST 2008
   Created on 25 Jun 2008, 08:31:04 hrs UTC
   Bugs fixed:
     6972843

Patch  6823287      : applied on Tue Oct 14 17:26:32 CEST 2008
   Created on 30 Jun 2008, 10:27:42 hrs UTC
   Bugs fixed:
     6823287



--------------------------------------------------------------------------------

OPatch succeeded.

How to use dbms_metadata to generate DDL for constraints

set long 10000
set heading off
set trimspool on
set lines 300
set longchunksize 300
spool all_mycons.sql
exec dbms_metadata.set_Transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl('CONSTRAINT','SYS_C006257121','SCOTT')  FROM DUAL;

-- For Foreign key constraints, use the REF_CONSTRAINT constant:
select dbms_metadata.get_ddl('REF_CONSTRAINT','MYFOREIGNKEY_FK','SCOTT')  FROM DUAL;

How to use dbms_metadata to generate DDL for a trigger

set long 10000
set heading off
set trimspool on
set lines 300
set longchunksize 300
spool mytrigger.sql
exec dbms_metadata.set_Transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl('TRIGGER','MYTRIGGER','SCOTT')  FROM DUAL;

On a Linux system, what do the parameters shmall, shmmax and shmmni define?

shmall
* indicates the total amount of shared memory that the system can use at one time (measured in pages)

shmmax
* defines the maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space, in bytes.
Oracle recommends that more than half of the physical memory is assigned to shmmax

shmmni
* defines the system wide maximum number of shared memory segments

How to join v$database with v$instance

Here is a useful query which displays basic information about the database, using v$database and v$instance.
Take the opportunity to calculate the uptime for the database by subtracting the value of startup_time from the current date.

col platform_name format a30
col open_mode format a20
col host_name format a20
col version format a10
col status format a20
col uptime format a30
col name format a10
set lines 300

SELECT D.NAME,D.PLATFORM_NAME,D.CREATED, D.OPEN_MODE,I.HOST_NAME,I.VERSION, I.ARCHIVER,I.STATUS,
   TO_DSINTERVAL( TO_CHAR(
                                    TO_TIMESTAMP(SYSDATE)-I.STARTUP_TIME
                                   )
                      ) "UPTIME", 
                      (SELECT ROUND(SUM(BYTES)/1024/1024/1024) FROM DBA_DATA_FILES) "DB size GB",  
                      ( SELECT ROUND(BYTES/1024/1024/1024) "mem GB" FROM V$SGAINFO WHERE NAME = 'Maximum SGA Size' ) "SGA max size GB"
  FROM V$DATABASE D INNER JOIN V$INSTANCE I 
  ON UPPER(D.NAME) = UPPER(I.INSTANCE_NAME);

Result may look like the following:

NAME	PLATFORM_NAME	CREATED	OPEN_MODE	HOST_NAME	VERSION	ARCHIVER	STATUS	UPTIME	DB size GB	SGA max size GB
proddb01	Linux x86 64-bit	10.12.2015 14:16:46	READ WRITE	prodserver01.mycompany.com	11.2.0.4.0	STARTED	OPEN	+00 13:59:55.000000	929	12