SELECT b.sid oracleID,

       b.username 登录Oracle用户名,

       b.serial#,

       spid 操作系统ID,

       paddr,

       sql_text 正在执行的SQL,

       b.machine 计算机名

FROM v$process a, v$session b, v$sqlarea c

WHERE a.addr = b.paddr

  AND b.sql_hash_value = c.hash_value

SELECT OSUSER 电脑登录身份,

       PROGRAM 发起请求的程序,

       USERNAME 登录系统的用户名,

       SCHEMANAME,

       B.Cpu_Time 花费cpu的时间,

       STATUS,

       B.SQL_TEXT 执行的sql

FROM V$SESSION A

LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS

                   AND A.SQL_HASH_VALUE = B.HASH_VALUE

ORDER BY b.cpu_time DESC 

SELECT l.session_id sid,

       s.serial#,

       l.locked_mode 锁模式,

       l.oracle_username 登录用户,

       l.os_user_name 登录机器用户名,

       s.machine 机器名,

       s.terminal 终端用户名,

       o.object_name 被锁对象名,

       s.logon_time 登录数据库时间

FROM v$locked_object l, all_objects o, v$session s

WHERE l.object_id = o.object_id

   AND l.session_id = s.sid

ORDER BY sid, s.serial#;

-- 找出哪个数据库用户用什么程序在最近三天执行过delete或truncate table的操作

SELECT c.username,

     a.program,

     b.sql_text,

     b.command_type,

     a.sample_time

FROM dba_hist_active_sess_history a

     JOIN dba_hist_sqltext b

        ON a.sql_id = b.sql_id

     JOIN dba_users c

        ON a.user_id = c.user_id

WHERE a.sample_time BETWEEN SYSDATE - 3 AND SYSDATE

  AND b.command_type IN (7, 85)

ORDER BY a.sample_time DESC;

http://blog.chinaunix.net/uid-7900301-id-2548168.html

http://blog.sina.com.cn/s/blog_4a407a16010009n7.html

with tmp as

(select rtime,

                       sum(tablespace_usedsize_kb) tablespace_usedsize_kb,

                       sum(tablespace_size_kb) tablespace_size_kb

                  from (select rtime,

                               e.tablespace_id,

                               (e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,

                               (e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb

                          from dba_hist_tbspc_space_usage e,

                               dba_tablespaces            f,

                               v$tablespace               g

                         where e.tablespace_id = g.TS#

                           and f.tablespace_name = g.NAME

                           and f.contents not in ('TEMPORARY','UNDO'))

                 group by rtime)

       select tmp.rtime,

              tablespace_usedsize_kb,

              tablespace_size_kb,

              (tablespace_usedsize_kb -

              LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB

         from tmp,

              (select max(rtime) rtime

                 from tmp

                group by substr(rtime, 1, 10)) t2

        where t2.rtime = tmp.rtime

        ORDER BY rtime

        /

with tmp as

(select rtime,

                       sum(tablespace_usedsize_kb) tablespace_usedsize_kb,

                       sum(tablespace_size_kb) tablespace_size_kb

                  from (select rtime,g.name,

                               e.tablespace_id,

                               (e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,

                               (e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb

                          from dba_hist_tbspc_space_usage e,

                               dba_tablespaces            f,

                               v$tablespace               g

                         where e.tablespace_id = g.TS#

                           and f.tablespace_name = g.NAME

                           and f.contents not in ('TEMPORARY','UNDO')

                           and g.name=upper('&tablespace_name'))

                 group by rtime)

       select tmp.rtime,

              tablespace_usedsize_kb,

              tablespace_size_kb,

              (tablespace_usedsize_kb -

              LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB

         from tmp,

              (select max(rtime) rtime

                 from tmp

                group by substr(rtime, 1, 10)) t2

        where t2.rtime = tmp.rtime

        ORDER BY rtime

        /

--Oracle九大性能视图之1.v$sort_usage 临时表空间的使用情况

select se.username,

       se.sid,

       se.serial#,

       se.sql_address,

       se.machine,

       se.program,

       su.extents,

       su.blocks * to_number(rtrim(p.value)) as Space,

       tablespace,

       segtype,

       sql_text,
       s.last_active_time

  from v$sort_usage su, v$parameter p, v$session se, v$sql s

 where p.name = 'db_block_size'

   and su.session_addr = se.saddr

   and s.hash_value = su.sqlhash

   and s.address = su.sqladdr

 order by se.username, se.sid;