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.

How to create a sequence and a trigger that will emulate "autonumber" in other RDBMS systems

If you have been working with other RDBMS systems, such as SQL Server from Microsoft, you may be used to a datatype called AUTONUMBER, which will automatically insert a freshly generated number and create a unique value in your table. Perfect for generation of primary key values.
In oracle, no such data type exist. To achieve the same functionality, you need to create two additional objects: a sequence and a trigger. The trigger will fire on certain events. The sequence will generate a new number. Here is a simple example on how to set it up.

CREATE TABLE ADDRESSES
(
 ADDRESS_ID NUMBER,
 EMLOYEE_ID NUMBER,
 STREET_ADDRESS VARCHAR2(40),
 STREET_ADDRESS2 VARCHAR2(40),
 CITY VARCHAR2 (30),
 STATE VARCHAR2(2),
 ZIP VARCHAR2(5),
 ZIP_PLUS VARCHAR2(4),
 COUNTRY VARCHAR2(20),
 CONSTRAINT PK_ADDRESSES PRIMARY KEY (ADDRESS_ID)-- "OUT-OF-LINE" CONSTRAINT CREATION
);

CREATE SEQUENCE ADDRESSES_SEQ
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 1000000
 NOCACHE
 NOCYCLE
 NOORDER;

CREATE OR REPLACE TRIGGER ADDRESSES_TRG
BEFORE INSERT
ON ADDRESSES
FOR EACH ROW
WHEN (NEW.ADDRESS_ID IS NULL)
BEGIN
 SELECT ADDRESSES_SEQ.NEXTVAL
 INTO   :NEW.ADDRESS_ID
 FROM   DUAL;
END;
/

How does the 11gR2 feature "deferred segment creation" work?

Starting from 11gR2 onwards, Oracle introduced a feature called deferred segment creation. This means that Oracle will not need to create any segments for a table until you actually insert rows into it.

Oracle creates all needed metadata about the new table in data dictionary, but doesn’t actually allocate any space from the tablespace. This applies to other segment types like index and table/index partitions as well.

Syntax:

-- Without deffered segment creation
CREATE TABLE TEST(tab_id number)
SEGMENT CREATION IMMEDIATE
TABLESPACE USERS;

-- With deffered segment creation
CREATE TABLE TEST (tab_id number)
SEGMENT CREATION DEFERRED
TABLESPACE USERS;


The parameter deferred_segment_creation controls the default behavior:

SQL> show parameter deferred

NAME_COL_PLUS_SHOW_PARAM     TYPE        VALUE
---------------------------- ----------- --------
deferred_segment_creation    boolean     TRUE