How to use SYS_CONTEXT to display user information

Oracle provides a built-in namespace called USERENV, which describes the current session.

The function SYS_CONTEXT can be used to return the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

You can use the SYS_CONTEXT to retreive userinformation from the namespace USERENV, some examples below:

SELECT SYS_CONTEXT ('userenv','OS_USER') "OS user", 
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','IDENTIFICATION_TYPE') "Identification type",
       SYS_CONTEXT('userenv','IP_ADDRESS') "IP",
       SYS_CONTEXT('userenv','HOST') "Host name",
       SYS_CONTEXT('userenv','SID') "SID",
       SYS_CONTEXT('userenv','SERVICE_NAME') "Service"
FROM DUAL;

Executed from a remote session, logged into the database as user SCOTT, the output from the query above will be:

OS user	Current schema	Identification type	IP	Host name	SID	Service
SCOTT	SCOTT	LOCAL	192.168.1.2	MYDOMAIN\MYCLIENT	170	myservice.mydomain.com

To find the serial# of your session, you will need to query the v$session dynamic performance view. you need to have SELECT privileges on the V$SESSION view to use this query, so first, a grant is needed.

SQL> show user
USER is "SYS"

GRANT SELECT ON V_$SESSION TO SCOTT;

Now, as user SCOTT, you can execute the query

SELECT SID, SERIAL#
FROM V$SESSION
WHERE SID=SYS_CONTEXT('USERENV','SID');


Source: Oracle Documentation The 12.2 SYS_CONTEXT documentation can be found here

How to retreive information about CPUs on a Solaris server

For analytic purposes when dealing with installations, system validations, database design and performance tuning, you will often need to know hard facts about the CPUs of a server hosting an oracle database. Here are some of my notes on the matter.

Use the psrinfo utility. The flag -p displays the number of physical processors, and the -v flag is for verbose output:

oracle@host1[PRODDB11] psrinfo -pv
The physical processor has 8 virtual processors (0-7)
SPARC64-VII (portid 1024 impl 0x7 ver 0x91 clock 2400 MHz)
The physical processor has 8 virtual processors (8-15)
SPARC64-VII (portid 1032 impl 0x7 ver 0x91 clock 2400 MHz)

Without any argument, psrinfo prints a tabular output of the CPUs(or cores), as follows:
oracle@host1[PRODDB11] psrinfo
0 on-line since 07/18/2011 18:18:57
1 on-line since 07/18/2011 18:19:58
2 on-line since 07/18/2011 18:19:58
3 on-line since 07/18/2011 18:19:58
4 on-line since 07/18/2011 18:19:58
5 on-line since 07/18/2011 18:19:58
6 on-line since 07/18/2011 18:19:58
7 on-line since 07/18/2011 18:19:58
8 on-line since 07/18/2011 18:19:58
9 on-line since 07/18/2011 18:19:58
10 on-line since 07/18/2011 18:19:58
11 on-line since 07/18/2011 18:19:58
12 on-line since 07/18/2011 18:19:58
13 on-line since 07/18/2011 18:19:58
14 on-line since 07/18/2011 18:19:58
15 on-line since 07/18/2011 18:19:58

The utility uname can also be helpful, when executed with the -X flag, which prints expanded system information:

oraoracle@host1[PRODDB11] uname -X
System = SunOS
Node = zus60h-0034
Release = 5.10
KernelID = Generic_137111-04
Machine = sun4u
BusType =
Serial =
Users =
OEM# = 0
Origin# = 1
NumCPU = 16

The prtdiag utility, likewise:

oraoracle@host1[PRODDB11] prtdiag |more
System Configuration: Sun Microsystems sun4u Sun SPARC Enterprise M4000 Server
System clock frequency: 1012 MHz
Memory size: 32768 Megabytes

==================================== CPUs ====================================

CPU CPU Run L2$ CPU CPU
LSB Chip ID MHz MB Impl. Mask
--- ---- ---------------------------------------- ---- --- ----- ----
00 0 0, 1, 2, 3, 4, 5, 6, 7 2530 5.5 7 160
00 1 8, 9, 10, 11, 12, 13, 14, 15 2530 5.5 7 160

How to install java in an Oracle 11gR2 database

Source: Oracle Support article "How to Reload the JVM in 11.2.0.x (Doc ID 1112983.1)"

Here are the steps I followed:

First, confirmed that java is indeed not installed (see my previous note "Is java installed in my database")

Step 1: Executed full export or another type of full backup (Oracle recommends a cold backup):
Create a parameter file as follows:
USERID='/ as sysdba'
DIRECTORY=DPUMP
DUMPFILE=full_exp_before_java.dmp
LOGFILE=full_exp_before_java.log
JOB_NAME=PREJAVA_EXP
FULL=Y

Execute the export from the command line:

$ [PRODDB11] expdp parfile=full_exp.par

Check that export finished successfully:
Dump file set for SYS.PREJAVA_EXP is:
/db_impexp/oracle/PRODDB1/full_exp_before_java.dmp
Job "SYS"."PREJAVA_EXP" successfully completed at 09:10:41

Step 2: shutdown of the database:
$ [PRODDB11] srvctl status database -d PRODDB10
Instance PRODDB11 is running on node node1
$ [PRODDB11] srvctl stop database -d PRODDB1
$ [PRODDB11] srvctl status database -d PRODDB1
Instance PRODDB1 is not running on node node1

Step 3: Create script full_rmjvm.sql:
-- Start of File full_rmjvm.sql
spool full_rmjvm.log
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter system enable restricted session;
alter database open;
@?/rdbms/admin/catnoexf.sql
@?/rdbms/admin/catnojav.sql
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
truncate table java$jvm$status;
select * from obj$ where obj#=0 and type#=0;
delete from obj$ where obj#=0 and type#=0;
commit;
select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
select o1.name from obj$ o1,obj$ o2
where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29;
shutdown immediate
set echo off
spool off
exit
-- End of File full_rmjvm.sql

Step 4: Remove existing java installations, if any. From a new sqlplus session, run the script full_rmjvm.sql.
(Note that the script will start up the database for you)
$ [PRODDB11] sqlplus / as sysdba @rmjvm.sql

Step 5: Review the log file FULL_RMJVM.LOG
In my case, java was not installed, so the removal scritp had nothing to do and exited with the following message:

ERROR at line 1:
ORA-20000: CATJAVA has not been loaded into the database.
ORA-06512: at line 3

Step 6: Create script full_jvminst.sql
-- Start of File full_jvminst.sql
spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catexf.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit
-- End of File full_jvminst.sql

Step 7: Install the JVM
a) Be sure the REMOVAL script, full_rmjvm.sql, completed successfully
b) Create the following INSTALL script, full_jvminst.sql
c) Run it from a new SQL*Plus session
$ [PRODDB11] sqlplus / as sysdba @full_jvminst.sql

Step 8: Review the log file FULL_JVMINST.LOG
In my case, there were no errors and the logfile ends with:

SQL> select count(*), object_type from all_objects
  2  where object_type like '%JAVA%' group by object_type;

  COUNT(*) OBJECT_TYPE                                                          
---------- -------------------                                                  
       317 JAVA DATA                                                            
       762 JAVA RESOURCE                                                        
     20332 JAVA CLASS                                                           
         2 JAVA SOURCE                                                          

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 9: Start the database:
$ [PRODDB11] srvctl start database -d PRODDB10
Step 10: Resolve Invalid Objects:
$ [PRODDB11] sqlplus / as sysdba @?/rdbms/admin/utlrp.sql
Step 11: Create the file validate_java_installation.sql

-- Start of File validate_java_installation.sql
set lines 200
col banner format a80
col comp_name format a30
col version format a20
col status format a20
-- Validation Query 1
select count(*), object_type
from all_objects
where object_type like '%JAVA%'
and owner = 'SYS'
group by object_type;

-- Validation Query 2
select owner, count(*)
from all_objects 
where object_type like '%JAVA%'
and owner = 'SYS'group by owner;

-- Validation Query 3
select owner, object_type, count(*)
from all_objects 
where object_type like '%JAVA%'
and status <> 'VALID'
and owner = 'SYS'
group by owner, object_type;

SELECT * FROM ALL_REGISTRY_BANNERS
WHERE   BANNER LIKE INITCAP('%Java%') 
OR      BANNER LIKE UPPER('%JAVA%');

SELECT  COMP_NAME,VERSION,STATUS 
FROM    DBA_REGISTRY 
WHERE   COMP_NAME LIKE INITCAP('%Java%') 
OR      COMP_NAME LIKE UPPER('%JAVA%');

SELECT * FROM V$OPTION 
WHERE PARAMETER = 'Java';
exit
-- End of File validate_java_installation.sql

Step 12: Validate the Install. Expected output should be similar to the following:
$ [PRODDB11] sqlplus / as sysdba @validate_java_installation.sql

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 8 11:19:04 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

  COUNT(*) OBJECT_TYPE
---------- -------------------
       317 JAVA DATA
       761 JAVA RESOURCE
     20285 JAVA CLASS
         2 JAVA SOURCE

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                 21365

no rows selected

BANNER
--------------------------------------------------------------------------------
JServer JAVA Virtual Machine Release 11.2.0.2.0 - Development
Oracle Database Java Packages Release 11.2.0.2.0 - Development

COMP_NAME                      VERSION              STATUS
------------------------------ -------------------- --------------------
JServer JAVA Virtual Machine   11.2.0.2.0           VALID
Oracle Database Java Packages  11.2.0.2.0           VALID

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Java                                                             TRUE

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options´

-- All done! --

Is java installed in my database?

Check the following views to confirm whether or not java is a part of your Oracle installation:

ALL_REGISTRY_BANNERS displays the valid components loaded into the database.

SELECT * FROM ALL_REGISTRY_BANNERS
WHERE   BANNER LIKE INITCAP('%Java%') 
OR      BANNER LIKE UPPER('%JAVA%');

If java is installed, the query would typically return:

BANNER
--------------------------------------------------------------------------------
JServer JAVA Virtual Machine Release 11.1.0.7.0 - Production
Oracle Database Java Packages Release 11.1.0.7.0 - Production

DBA_REGISTRY displays information about the components loaded into the database.

SELECT  COMP_NAME,VERSION,STATUS 
FROM    DBA_REGISTRY 
WHERE   COMP_NAME LIKE INITCAP('%Java%') 
OR      COMP_NAME LIKE UPPER('%JAVA%');

If java is installed, the query would typically return:

COMP_NAME                                VERSION              STATUS
---------------------------------------- -------------------- ---------------------------------
JServer JAVA Virtual Machine             11.1.0.7.0           VALID
Oracle Database Java Packages            11.1.0.7.0           VALID

V$OPTION lists database options and features.
Some options must be separately purchased and installed, while other features come with the product and are enabled based on the product that is running (Standard Edition, Enterprise Edition, or Personal Oracle).

SELECT * FROM V$OPTION 
WHERE PARAMETER = 'Java';

Source: Oracle Documentation

SQL statement to list all database objects and their sizes

Use the WITH clause to create two different subqueries that you can reference later in the query:

WITH
  OBJECT_COUNT AS
   (
    SELECT OWNER, OBJECT_TYPE, COUNT(*) "NUM_OBJECTS"
    FROM DBA_OBJECTS
    GROUP BY OWNER,OBJECT_TYPE
    ),
    SPACE_USAGE AS
    (
    SELECT SEGMENT_TYPE, SUM(BYTES)  "BYTES"
    FROM DBA_SEGMENTS
    GROUP BY SEGMENT_TYPE
    )
    SELECT  O.OWNER,O.OBJECT_TYPE,O.NUM_OBJECTS, ROUND(U.BYTES/1024/1024) "MB"
    FROM    OBJECT_COUNT O LEFT OUTER JOIN SPACE_USAGE U
    ON      O.OBJECT_TYPE = U.SEGMENT_TYPE
    ORDER BY 1 ASC;

SQL statement to find all referential integrity constraints that relates to a specific table

Here is how to use a self-join of the view USER_CONSTRAINTS to find constraints that is referring to a specific table. Note that you can also use the DBA_CONSTRAINTS if logged in as a privileged user.

First, set up a couple of extra tables in the SCOTT schema to illustrate the point (the existing SCOTT objects will contain only one - 1 - Foreign Key by default):

CONNECT SCOTT/TIGER
CREATE TABLE PORTS
 (
    PORT_ID     NUMBER,
    PORT_NAME   VARCHAR2 (20),
    COUNTRY     VARCHAR2 (40),
    CAPACITY    NUMBER
 );


 CREATE TABLE SHIPS
 (
    SHIP_ID        NUMBER,
    SHIP_NAME      VARCHAR2 (20),
    CAPACITY       NUMBER,
    LENGTH         NUMBER,
    HOME_PORT_ID   NUMBER
 );

 ALTER TABLE SHIPS
 ADD CONSTRAINT PK_SHIPS PRIMARY KEY (SHIP_ID);

 ALTER TABLE PORTS
 ADD CONSTRAINT PK_PORTS PRIMARY KEY (PORT_ID);

 ALTER TABLE SHIPS
 ADD CONSTRAINT SHIPS_PORTS_FK FOREIGN KEY (HOME_PORT_ID)
 REFERENCES PORTS (PORT_ID);

Now, let's find the relationships between the Foreign key constraints and their corresponding unique key constraint:

CONNECT SCOTT/TIGER
SET LINES 200
SET HEADING OFF
SET FEEDBACK OFF
SET PAGES 0
SELECT    B.CONSTRAINT_NAME
        || ' on table '
        || B.TABLE_NAME
        || ' points to table '
        || A.TABLE_NAME
           "Relationship"
   FROM USER_CONSTRAINTS A
        FULL OUTER JOIN USER_CONSTRAINTS B
           ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  WHERE A.OWNER = 'SCOTT' AND B.R_CONSTRAINT_NAME IS NOT NULL;

Here you can see the relationships between the constraints and their tables:

Relationship
SHIPS_PORTS_FK on table SHIPS points to table PORTS
FK_DEPTNO on table EMP points to table DEPT

In my case I wanted to drop the constraints, so I concatenated the columns with strings to generate a "drop constraint" script:

CONNECT SCOTT/TIGER

 SELECT    'alter table '
        || B.OWNER
        || '.'
        || B.TABLE_NAME
        || ' drop constraint '
        || B.CONSTRAINT_NAME
        || ';'
  FROM USER_CONSTRAINTS A
        FULL OUTER JOIN USER_CONSTRAINTS B
           ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  WHERE A.OWNER = 'SCOTT' AND B.R_CONSTRAINT_NAME IS NOT NULL;

Returned rows:

alter table SCOTT.SHIPS drop constraint SHIPS_PORTS_FK;
 alter table SCOTT.EMP drop constraint FK_DEPTNO;

Note that you can achieve the same goal by using a subquery instead of a FULL OUTER JOIN:

CONNECT SCOTT/TIGER
 SELECT OWNER,TABLE_NAME,CONSTRAINT_NAME
 FROM  USER_CONSTRAINTS
WHERE  R_CONSTRAINT_NAME IN (
             SELECT CONSTRAINT_NAME
            FROM USER_CONSTRAINTS
             WHERE CONSTRAINT_TYPE='P'
            AND OWNER = 'SCOTT'
 );

Output:

OWNER	TABLE_NAME	CONSTRAINT_NAME
SCOTT	EMP	FK_DEPTNO
SCOTT	SHIPS	SHIPS_PORTS_FK

Two different ways to retreive the current System Change Number

Use either of the following methods:

SQL> col current_scn format 9999999999999
SQL> select current_scn from v$database;

   CURRENT_SCN
--------------
 6525378448555

SQL> col GET_SYSTEM_CHANGE_NUMBER format 9999999999999
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
           6525378448442

Oracle officially recommends tht if your goal is to obtain an SCN from within an application, then you should use the dbms_flashback.get_system_change_number function.