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

What is the difference between automatic segment space management and manual segment space management?

In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual.

Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps.

Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.