#!表分析语句
exec dbms_stats.GATHER_TABLE_STATS('DBCUSTADM','WUSERFAVCHG',ESTIMATE_PERCENT=>100,cascade=>true);
#!通过PID找出执行的语句
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC
/
#!通过SID找出执行的语句
SELECT sql_text
FROM v$sqltext a
WHERE a.hash_value = (SELECT sql_hash_value
FROM v$session b
WHERE b.SID = '&sid')
ORDER BY piece ASC
/
#!查看当前等待
while [ 1 ]
do
echo "SESSION EVENT WAIT LIST "
sqlplus -smonitor/monitor@crm1<set pagesize 2000
set linesize 110
col event format a25
col username for a10
col osuser for a10
col p1 for 9999999
col sid for 9999
col p2 for 999999
col p3 for 9999999999999
col program format a20
select substr(a.event,1,25) event,substr(b.program,1,20) program ,b.sid,b.username,b.osuser
from v\$session_wait a,v\$session b
where a.sid=b.sid and a.event not like '%SQL%'
and a.event not like '%message%'
and a.event not like '%time%'
and a.event not like 'PX Deq:%'
and a.event not like 'jobq slave%'
/
exit
!
sleep 3
done
#!查看表空间使用率
COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
COLUMN name FORMAT a16 HEADING "Tablespace Name"
COLUMN Kbytes FORMAT 9,999,999,999 HEADING "KBytes"
COLUMN used FORMAT 9,999,999,999 HEADING "Used"
COLUMN free FORMAT 9,999,999,999 HEADING "Free"
COLUMN largest FORMAT 999,999,999 HEADING "Largest"
BREAK ON report
COMPUTE sum OF kbytes ON REPORT
COMPUTE sum OF free ON REPORT
COMPUTE sum OF used ON REPORT
set pagesize 2000
set linesize 120
--SPOOL tablespace_size.lst
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, kbytes_alloc kbytes
, kbytes_alloc-NVL(kbytes_free,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
, tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
, tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name
) b
WHERE a.tablespace_name (+) = b.tablespace_name
order by pct_used desc
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14835654/viewspace-543074/,如需转载,请注明出处,否则将追究法律责任。