#！表分析语句

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
/