Oracle的sql记录 以后遇到陆续更新

1.查看Oracle被锁表,以及删除该被锁对象

SELECT OBJECT_NAME,     
       SESSION_ID SID,     
       MACHINE,  
       VS.MODULE,
       'ALTER   SYSTEM   KILL   SESSION   '''
       || SESSION_ID
       || ', '
       || SERIAL#
       || '''; '
       KILL_SESSION,
       VS.STATUS,
       VS.ACTION,
       SERIAL#,
       ORACLE_USERNAME,
       OS_USER_NAME
  FROM V$LOCKED_OBJECT VO, V$SESSION VS, ALL_OBJECTS AO
 WHERE VO.SESSION_ID = VS.SID   
   AND AO.OBJECT_ID = VO.OBJECT_ID   
   AND NVL(VS.ACTION, '   ') <> 'Service   Management '
 ORDER BY OBJECT_NAME, MACHINE, VS.MODULE;

2.查看表空间利用率
SELECT D.TABLESPACE_NAME,
       SPACE || 'M' "SUM_SPACE(M)",
       BLOCKS "SUM_BLOCKS",
       SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
       FREE_SPACE || 'M' "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间  
SELECT D.TABLESPACE_NAME,
       SPACE || 'M' "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       USED_SPACE || 'M' "USED_SPACE(M)",
       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
       NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
          FROM V$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 ORDER BY 1;

3.查看数据库正在执行的sql

select a.sql_text,e.event,e.sid,e.SERIAL#  from v$sqlarea a,
(select b.sql_id,c.event,b.sid,b.SERIAL# from v$session b, v$session_wait c
where b.sid=c.sid and c.EVENT not like 'SQL%' and c.event not like 'rdbms%'
) e
where a.sql_id=e.sql_id
4.查看一个表占用大多空间

SELECT TABLESPACE_NAME,
       TO_CHAR(SUM(BYTES) / (1024 * 1024), '999G999D999') CNT_MB
  FROM DBA_EXTENTS
 WHERE OWNER = 'CRNOP' --所属用户
   AND SEGMENT_NAME = 'CDL_PERF_CITYPH_1X_C_3' --表名称
   AND SEGMENT_TYPE LIKE 'TABLE%' --类型
 GROUP BY TABLESPACE_NAME;

 

5.正在执行的

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  from v$session a, v$sqlarea b 
where a.sql_address = b.address 
6.执行过的
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
  from v$sqlarea b
where b.FIRST_LOAD_TIME between '2009-10-15/09:24:47' and
       '2009-10-15/09:24:47' order by b.FIRST_LOAD_TIME 
（此方法好处可以查看某一时间段执行过的sql，并且 SQL_FULLTEXT 包含了完整的 sql 语句）

其他
select OSUSER,PROGRAM,USERNAME,SCHEMANAME,B.Cpu_Time,STATUS,B.SQL_TEXT 
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 address, sql_text, piece 
  from v$session, v$sqltext 
where address = sql_address 
  -- and machine = < you machine name > 
order by address, piece 

7查找前十条性能差的sql. 

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, 
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea 
order BY disk_reads DESC )where ROWNUM<10 ; 

8.查看占io较大的正在运行的session 

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status, 
se.terminal,se.program,se.MODULE,、se.sql_address,st.event,st. 
p1text,si.physical_reads, 
si.block_changes FROM v$session se,v$session_wait st, 
v$sess_io si,v$process pr WHERE st.sid=se.sid AND st. 
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st. 
wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC