How to list all schemas and their sizes

If you want a list of schema sizes, join dba_segments with dba_users to limit the extract to users that are non-oracle maintained:

set lines 200
col owner  format a30
col "GB" format 999999
SELECT s.owner,sum(s.bytes/1024/1024/1024) "GB"
FROM dba_segments s join dba_users u
on (s.owner = u.username)
where u.oracle_maintained = 'N'
group by owner
ORDER BY 2 desc;

Example output

OWNER                               GB
------------------------------ -------
USER1                            19577
USER2                             6144
USER3                             2306

How to list all users and their number of owned objects

Join dba_users with dba_objects using a correlated subquery, and you'll get all the schemas, both those which own objects and those who don't:

set lines 200
col username format a20
col created format a30
col "Num_obj" format 999999
SELECT u.username,u.created,(SELECT COUNT(*) FROM dba_objects o WHERE o.owner = u.username)"Num_obj"
FROM dba_users u
-- to leave out all oracle maintained schemas, comment out the following
-- WHERE  u.oracle_maintained = 'N'
ORDER BY 3 asc;

Example output:

USERNAME             CREATED                        Num_obj
-------------------- ------------------------------ -------
ANONYMOUS            14.06.2016                           0
ADDM_USER            14.06.2016                           0
GSMUSER              14.06.2016                           0
SYSDG                14.06.2016                           0
DIP                  14.06.2016                           0
SYSBACKUP            14.06.2016                           0
SCOTT                14.06.2016                           0
USER1                20.06.2016                           0
USER2                20.06.2016                           0
SYSKM                14.06.2016                           0
XS$NULL              14.06.2016                           0
GSMCATUSER           14.06.2016                           0
APPQOSSYS            14.06.2016                           5
ORACLE_OCM           14.06.2016                           6
OUTLN                14.06.2016                          10
AUDSYS               14.06.2016                          12
OJVMSYS              14.06.2016                          16
USER3                20.06.2016                          34
DBSNMP               14.06.2016                          55
USER4                20.06.2016                          95
WMSYS                14.06.2016                         389
CTXSYS               14.06.2016                         409
SYSTEM               14.06.2016                         641
XDB                  14.06.2016                         961
SYS                  14.06.2016                       42173

If you have a large number of accounts that own no objects at all, you may want to exclude them. Do so by checking for the existence of the particular account in dba_objects.
If there are any rows at all in there, the account owns at least 1 object. Make sure to select a literal, not a value from the database. This is good practice; performance is not affected:

SELECT u.username,u.created,(SELECT COUNT(*) FROM dba_objects o WHERE o.owner = u.username )"Num_obj"
FROM dba_users u
WHERE EXISTS (SELECT 1 FROM dba_objects o WHERE o.owner = u.username)
ORDER BY 3 asc;

How to check if the database is in restricted mode

To check what kind of logins that are allowed in your instance, query the LOGINS field of the v$instance view:

select logins from v$instance;

Output here will be either ALLOWED or RESTRICTED

Join with v$database for more information:

select a.INSTANCE_NAME, a.ACTIVE_STATE,a.logins, b.open_mode 
from v$instance a inner join v$database b 
on UPPER(a.instance_name) = b.name;

Which will show the following output if your database is in restricted mode, and in this example, mounted:

INSTANCE_NAME    ACTIVE_ST LOGINS     OPEN_MODE
---------------- --------- ---------- --------------------
mydb01           NORMAL    RESTRICTED MOUNTED

and for normal mode:

INSTANCE_NAME    ACTIVE_ST LOGINS     OPEN_MODE
---------------- --------- ---------- --------------------
mydb01           NORMAL    ALLOWED    READ WRITE

Query the registry!

To view the different options installed in the database, you should use DBA_REGISTRY, as follows:

set lines 200 pages 100
col comp_name format a40
SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;

Example output:

SQL> SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;

COMP_NAME                                COMP_ID                        VERSION                        STATUS
---------------------------------------- ------------------------------ ------------------------------ --------------------------------------------
Oracle Text                              CONTEXT                        11.2.0.4.0                     VALID
Oracle Application Express               APEX                           3.2.1.00.12                    VALID
Oracle Multimedia                        ORDIM                          11.2.0.4.0                     VALID
Oracle XML Database                      XDB                            11.2.0.4.0                     VALID
Oracle Expression Filter                 EXF                            11.2.0.4.0                     VALID
Oracle Rules Manager                     RUL                            11.2.0.4.0                     VALID
Oracle Workspace Manager                 OWM                            11.2.0.4.0                     VALID
Oracle Database Catalog Views            CATALOG                        11.2.0.4.0                     VALID
Oracle Database Packages and Types       CATPROC                        11.2.0.4.0                     VALID
JServer JAVA Virtual Machine             JAVAVM                         11.2.0.4.0                     VALID
Oracle XDK                               XML                            11.2.0.4.0                     VALID
Oracle Database Java Packages            CATJAVA                        11.2.0.4.0                     VALID

12 rows selected.

How to display the currently used ORACLE_HOME from sqlplus

Thanks to my former colleague Laurent Schneider for pointing out a way to display the currently used ORACLE_HOME from within sqlplus:

set lines 200
col oracle_home format a40
set autoprint on
var oracle_home varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME);

PL/SQL procedure successfully completed.

ORACLE_HOME
------------------------------

/u01/oracle/product/11204

Note: To automatically display the value of a bind variable created with VARIABLE, use the SET AUTOPRINT command.
ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).

Source: Oracle Documentation

Login storm against database caused exhausted library cache

One of our databases experienced massive contention in the shared pool, in form of wait events alerted as "library cache locks".

The database was very small indeed, so my natural instinct was to throw some more memory at the virtual host, and rearrange the memory parameters.

This turned out to be a misconception; the resources were sufficient for the instance to work properly.

The problem was caused by an incorrect password configuration on the application server.

What we could observe was:

A totally exhausted shared pool, caused by "library cache lock"

The SQL that seemed to be repeatedly executed was

SELECT /*+ connect_by_filtering */
          privilege#, LEVEL
      FROM sysauth$
CONNECT BY grantee# = PRIOR privilege# AND privilege# > 0
START WITH grantee# = :1 AND privilege# > 0;


SELECT privilege#
  FROM sysauth$
WHERE (grantee# = :1 OR grantee# = 1) AND privilege# > 0;

The V$EVENT_NAME view showed that the wait event was accompanied by the additional information found in the columns parameter1 through parameter3, which turned out to be helpful further on:

select  name, wait_class,parameter1,parameter2,parameter3
from v$event_name
where wait_class = 'Concurrency'
and name = 'library cache lock';

NAME	WAIT_CLASS	PARAMETER1	PARAMETER2	PARAMETER3
library cache lock	Concurrency	handle address	lock address	100*mode+namespace

Further research showed that the problem was due to a built-in delay between failed login attempts in Oracle 11g:

"The 'library cache lock' wait is seen due to the fact that the account status gets updated due to incorrect login.
To prevent password guessing attack, there's a sleep() in the code when incorrect login attempts exceed count of 3.
And because of this sleep() you see a wait on library cache, as the process is yet to release the lock."

In release 11.1.0.7, patch 7715339 was released to remove this delay.

In release 11.2.X, the DBA must set an event to remove the delay, as follows:

alter system set events '28401 trace name context forever, level 1'; 

According to Oracle, the purpose of the built-sleep is to make it harder to succeed in a "password guessing attack", particularly in cases where FAILED_LOGIN_ATTEMPTS is set to UNLIMITED. Oracle Development is pointing out that disabling the sleep-function is not recommended. A better solution is to set the FAILED_LOGIN_ATTEMPTS to a reasonable value.
When the number of failed login attempts for a session hits the limit, the account will be locked. Subsequent logon attempts with incorrect password will then be rejected immediately without any contention in the library cache.

See Bug 15882590 : 'LIBRARY CACHE LOCK' DURING WRONG PASSWORD LOGON ATTEMPTS on My Oracle Support (MOS) for further information.

What is the difference between the columns PID and SPID in v$process?

The PID column is an internal counter that oracle uses for its own processes. It is incremented by one for every new process. Consequently, since every session creates its own server process (also called "shadow process") you will see the number increment by one for each session that connects.

The documentation for V$PROCESSES states that PID is the "Oracle Process Identifier" which is confusing.
In most circumstances, the column that you would need is the SPID, which shows the "Operating system process identifier".

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;