When did a user change his/her password?

SELECT NAME, ptime AS "LAST TIME CHANGED", ctime "CREATION TIME", ltime "LOCKED" 
FROM USER$ 
WHERE ptime IS NOT NULL 
ORDER BY ptime DESC;

Explain plan and statistics with SQL*Plus

SQL> set timing on
SQL> set autotrace traceonly explain
SQL> select * from dual;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> set autotrace traceonly statistics
SQL> /

Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          2  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace traceonly explain

SQL> /

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

Environment settings for SQL*PLUS - SET

Summary 

SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:

    * SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
    * SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
    * SET HEADING OFF - Stops displaying the header line of the query output.
    * SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
    * SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
    * SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
    * SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
    * SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
    * SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
    * SET WRAP OFF - Turns off the wrapping feature when displaying query output.

Memory Notification: Library Cache Object loaded into SGA

Summary 
In the Oracle10g a new heap checking mechanism, together with a new messaging system is introduced. This new mechanism reports memory allocations above a threshold in the alert.log, together with a tracefile in the udump directory. If you notice in your alert.log you may get messages like this:

Memory Notification: Library Cache Object loaded into SGA
Heap size 58689K exceeds notification threshold (51200K)

This message means that the threshold set by hidden parameter _kgl_large_heap_warning_threshold has been exceeded! 

In certain situations this can be very helpful to inform you if large allocations have been done in the sga heap (shared pool). The notification mechanism allows you to troubleshoot memory allocation problems, which eventually will appear as the infamous ORA-4031. 

If you don't have ORA-04031: unable to allocate x bytes of shared memory problems and don't want to appear the message in the alert log then you can increase the hidden parameter _kgl_large_heap_warning_threshold. 

The default limit is set at 2048K. To find the value of this parameter execute

SELECT * FROM (
SELECT a.ksppinm AS parameter,
       a.ksppdesc AS description,
       b.ksppstvl AS session_value,
       c.ksppstvl AS instance_value
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' ESCAPE '/'
ORDER BY a.ksppinm) 
WHERE parameter IN ('_kgl_large_heap_warning_threshold')

In the description of the parameter indicates: maximum heap size before KGL writes warnings to the alert log 

Workaround 
If you want to disappear the message from the alert.log increase the hidden parameter to something bigger, for example

ALTER SYSTEM SET "_kgl_large_heap_warning_threshold" = 89428800 SCOPE=SPFILE ;

Oracle New Support Versions

12c Architecture Diagram

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt
because a resource was unavailable for a LOCK TABLE or SELECT FOR
UPDATE command.
Action: Try the command after a few minutes or enter the command without
the NOWAIT keyword.


Example:
SQL> alter table emp add (mobile varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


How to avoid the ORA-00054:
    - Execute DDL at off-peak hours, when database is idle.
    - Execute DDL in maintenance window.
    - Find and Kill the session that is preventing the exclusive lock.


Other Solutions:

Solution 1:
In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait for the object to 
become available, simply specify how long you would like it to wait:
 
SQL> alter session set ddl_lock_timeout = 600;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered. 


Solution 2:
Also In 11g, you can mark your table as read-only to prevent DML:
SQL> alter table emp read only;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered.


Solution 3 (for 10g):
DECLARE
 MYSQL VARCHAR2(250) := 'alter table emp add (mobile varchar2(15))';
 IN_USE_EXCEPTION EXCEPTION;
 PRAGMA EXCEPTION_INIT(IN_USE_EXCEPTION, -54);
BEGIN
 WHILE TRUE LOOP
  BEGIN
   EXECUTE IMMEDIATE MYSQL;
   EXIT;
  EXCEPTION
   WHEN IN_USE_EXCEPTION THEN 
    NULL;
  END;
  DBMS_LOCK.SLEEP(1);
 END LOOP;
END;


Solution 4: 

Step 1: Identify the session which is locking the object
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c 
where b.object_id = c.object_id 
and a.sid = b.session_id
and OBJECT_NAME='EMP';

Step 2: kill that session using
alter system kill session 'sid,serial#'; 

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e0.htm