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;
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;
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 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;
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
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