学习笔记

 SELECT b.sid oracleID,
b.username ,
b.serial#,
spid ,
paddr, sql_text , 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 b.sid oracleID,
b.username ,
b.serial#,
spid ,
paddr, sql_text , 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, STATUS, B.SQL_TEXT
FROM V$SESSION ALEFT
JOIN V$SQL B
ON A.SQL_ADDRESS = B.ADDRESS
AND A.SQL_HASH_VALUE = B.HASH_VALUEORDER BY b.cpu_time 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;





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;





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 ；



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;





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 tablespace_name,
sum(bytes)/1024/1024
FROM dba_free_spacegroup by tablespace_name;





SELECT
( SELECT SUM(BLOCKS*8192/1024/1024)
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME='表空间名') AS E ,
( SELECT SUM(A.BYTES/1024/1024)
FROM V$DATAFILE A, V$TABLESPACE B
WHERE A.TS#=B.TS#
AND B.NAME='表空间名') AS F ,
( SELECT SUM(BYTES)/1024/1024
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME='表空间名') AS G
FROM DUAL




SELECT D.TABLESPACE_NAME "数据库文件名称",
SPACE || 'M' "总空间(M)", BLOCKS "总BLOCKS", SPACE - NVL (FREE_SPACE, 0) || 'M' "已用空间(M)", ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "已用率(%)", FREE_SPACE || 'M' "空闲空间(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' "总空间(M)", BLOCKS SUM_BLOCKS, USED_SPACE || 'M' "已用空间(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "已用率(%)", NVL (FREE_SPACE, 0) || 'M' "空闲空间(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 sid,
serial#,
username , osuser
FROM v$session
WHERE (sql_hash_value, sql_address)=
( SELECT hash_value, address
FROM v$sqlarea
WHERE sql_id='3jm2d4xjkh2m4' );