Somes useful new view in Oracle 11G

Record in here for avoid being forget
1. V$SQL_MONITOR
To find information about currently long-running queries

set lines 200 pages 200

col sql_text for a50
col username for a12
col sid for 9999
col key for 99999999999999

select key, sid, username, sql_id, sql_plan_hash_value plan_hash, elapsed_time, cpu_time, buffer_gets, disk_reads, substr(sql_text,1,50) sql_text
from v$sql_monitor
where status = 'EXECUTING';

2.V$SQL_PLAN_MONITOR
To find the execution plan for a key value

set lines 200 pages 200
col operation for a32
col plan_options for a20
col plan_object_name for a24
col id for 999

select plan_line_id id, lpad (' ', plan_depth) || plan_operation operation , plan_options , plan_object_name , plan_cardinality card, plan_cost
from v$sql_monitor q, v$sql_plan_monitor p
where q.key = p.key
and q.key = < key value >
order by id;

To find the execution plans of currently long-running queries

set lines 200 pages 200
col operation for a32
col plan_options for a20
col plan_object_name for a24
col id for 999
break on sql_id on plan_hash

select sql_id, sql_plan_hash_value plan_hash, plan_line_id id, lpad (' ', plan_depth) || plan_operation operation , plan_options , plan_object_name , plan_cardinality card, plan_cost
from v$sql_plan_monitor
where status = 'EXECUTING'
order by key, id;

3.DBA_TABLESPACE_USAGE_METRICS
Display tablespace use percent

SQL> SELECT * FROM DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX                              10360          252891   4.09662661
SYSTEM                              59064          265691   22.2303352
TEMP                                    0          178746            0
TEST                                  128            1280           10
UNDOTBS1                              432          201786   .214088192
USERS                                 168          176731   .095059724