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.