oracle数据库常用脚本1

1、查看表空间的名称及大小 
select   t.tablespace_name,   round(sum(bytes/(1024*1024)),0)   ts_size   
from   dba_tablespaces   t,   dba_data_files   d   
where   t.tablespace_name   =   d.tablespace_name   
group   by   t.tablespace_name;

2、查看表空间物理文件的名称及大小
select tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) total_space
  from dba_data_files
 order by tablespace_name;

3、查询实时sql
SELECT b.sid,
       b.username,
       a.sql_id,
       ' kill -9 ' || c.spid || '',
       b.event,
       round(b.LAST_CALL_ET / 3600) "ses_T(小时)",
       trunc(((a.ELAPSED_TIME / decode(executions, 0, 1, executions)) /
             1000000),
             2) "etime",
       round(a.buffer_gets / decode(a.executions, 0, 1, a.executions)) per_bu,
       a.sql_fulltext fullsql,
       a.sql_text,
       a.executions,
       a.disk_reads,
       a.buffer_gets,
       b.OSUSER,
       b.machine,
       b.program,
       a.MODULE,
       a.cpu_time,
       a.LAST_LOAD_TIME,
       a.LAST_ACTIVE_TIME /*,b.sql_exec_start*/
  FROM v$sqlarea a, v$session b, v$process c
 WHERE executions >= 0
   AND b.status = 'ACTIVE'
   AND a.hash_value = b.sql_hash_value
   AND a.sql_id = b.sql_id
   and b.paddr = c.addr
--and a.sql_text like  '%insert into t_xt_yyxx%'
 ORDER BY (cpu_time / decode(executions, 0, 1, executions)) DESC,
          a.buffer_gets DESC,
          a.executions DESC,
          a.sql_id;

4、实时监控sql
Select b.USERNAME,
       a.SQL_TEXT,
       a.SQL_ID,
       b.SID,
       b.SERIAL#,
       a.sql_fulltext,
       a.cpu_time,
       a.executions,
       ((a.cpu_time / a.executions) / 100000),
       b.sid,
       a.BUFFER_GETS,
       'alter system kill session ' || '''' || b.sid || ',' || b.serial# || '''' || ';',
       b.CLIENT_INFO
  From v$sql a, v$session b
 Where executions > 0
   And b.status = 'ACTIVE'
   And a.hash_value = b.sql_hash_value
   and a.SQL_ID = b.SQL_ID
--and b.USERNAME = 'DB_ZGXT'
--and ((cpu_time / executions) / 100000 >= 10)
 Order By (cpu_time / executions) desc,
          a.BUFFER_GETS Desc,
          a.EXECUTIONS desc,
          a.sql_id;

5、查找长事务
SELECT s.INST_ID,
       s.sid,
       s.serial#,
       t.start_time,
       s.username,
       s.status,
       ' kill -9 ' || p.spid || '',
       t.XIDUSN || '.' || t.XIDSLOT || '.' || t.XIDSQN as xidn,
       'ALTER SYSTEM KILL SESSION ' || '''' || s.sid || ',' || s.serial# || ',@' ||
       s.inst_id || '''' || ';' as kill
  FROM GV$session s, GV$transaction t, GV$rollstat r, gv$process p
 WHERE s.saddr = t.ses_addr
   and t.xidusn = r.usn
   and s.inst_id = t.inst_id
   and t.inst_id = r.inst_id
   and p.addr = s.paddr
 order by t.start_time;

6、锁表，根据用户名和表名查出锁表的相关sid号
select a.username,
       d.object_name,
       a.sid,
       a.serial#,
       c.sql_text,
       a.STATUS,
       a.SQL_ID
  from v$sql c, v$session a, v$locked_object b, dba_objects d
 where a.sql_id = c.sql_id(+)
   and a.sid = b.session_id
   and b.object_id = d.object_id
   and d.owner = upper('R_COMMON')
   and d.object_name = upper('T_F_XT_JHXT_');

7、查看锁
select decode(t.REQUEST, 0, 'Holder:', 'Waiter:') || ' inst_id:' ||
       t.INST_ID || ', sid: ' || t.sid sess,
       v.username,
       t.CTIME,
       v.STATUS,
       t.ID1,
       v.SQL_ID,
       t.id2,
       t.LMODE,
       t.REQUEST,
       t.TYPE,
       v.STATUS,
       v.EVENT,
       v.type,
       p.INST_ID,
       p.SPID,
       'ps -ef|grep ' || p.SPID,
       'kill -9 ' || p.SPID,
       v.PROGRAM
  from gv$lock t, gv$session v, gv$process p
 where (t.id1, t.id2, t.TYPE) in
       (select id1, id2, type from gv$lock where request > 0)
   and v.TYPE <> 'BACKGROUND'
   and v.INST_ID = t.INST_ID
   and t.SID = v.SID
   and p.addr = v.paddr
   and p.INST_ID = v.INST_ID
 order by id1, request;

SELECT b.sid,
       c.USERNAME,
       a.job,
       a.what,
       ROUND((sysdate - a.last_date) * 24, 2) "run(小时)",
       a.last_date,
       a.next_date,
       a.next_sec,
       a.interval,
       a.FAILURES,
       'ps -ef|grep ' || d.spid ps,
       'exec dbms_ijob.broken(' || b.job || ',' || 'true)' || ';' broken,
       'kill -9 ' || d.spid KILL,
       'orakill ' || (SELECT INSTANCE_NAME FROM v$instance) || ' ' ||
       d.spid orakill
  FROM dba_jobs a, dba_jobs_running b, v$session c, v$process d
 WHERE a.job = b.job
   AND c.sid = b.sid
   AND c.paddr = d.addr;

SELECT *
  FROM TABLE(dbms_xplan.display_cursor('bmtpyvf4pw0qm',
                                       NULL,
                                       'last typical'));

SELECT a.PARSING_SCHEMA_NAME || '.' pname,
       a.SQL_FULLTEXT,
       a.module,
       round(a.buffer_gets / decode(a.executions, 0, 1, a.executions)) per_buf,
       round(a.ROWS_PROCESSED / decode(a.executions, 0, 1, a.executions)) rows_per,
       round(buffer_gets / decode(a.ROWS_PROCESSED, 0, 1, a.ROWS_PROCESSED)) buf_row,
       ROUND(a.DISK_READS / decode(a.executions, 0, 1, a.executions)) per_read,
       TRUNC(a.elapsed_time / decode(a.executions, 0, 1, a.executions) /
             1000000) etime,
       a.executions,
       a.parse_calls parsec,
       a.sharable_mem sharem,
       a.last_active_time,
       a.CHILD_NUMBER
  FROM v$sql a /*查询一句sql的逻辑读、物理读 、单次执行时间--获取一行开销5个逻辑读是可接受范围*/
 WHERE a.sql_id = 'bmtpyvf4pw0qm';

8、TOPAS中有没有占用CPU大而且时间长的进程
  根据PID查看SID，SQL语句，执行计划。
 select sid, machine
   from v$session
  where paddr in (select addr from v$process where spid = '5501518');
 
 select b.sid, c.SQL_TEXT
   from V$process a, V$session b, V$sqltext c
  where a.ADDR = b.PADDR
    and b.SQL_HASH_VALUE = c.HASH_VALUE
    and b.SID = '****'
  order by c.HASH_VALUE, c.PIECE;

9、查看当前数据库会话连接数
  select count(*) from v$session;
  当前连接数和数据库参数中连接数比较
  show parameters processes;  --- 数据库连接数参数

10、是否有JOB在执行
  select * from dba_jobs_running；
  有的话，将JOB改为Borben，再将JOB进程杀掉。