数据库各个表空间增长情况的检查: SELECT SW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT SQL>SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUS FROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE#; 查看回滚段的使用情况,哪个用户正在使用回滚段的资源: 检查共享内存的剩余情况: 数据高速缓冲区性能检查: 数据库回滚段性能检查: 杀会话的脚本: 10、捕捉运行很久的SQL: SELECT A.USERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID=SPID AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE; |
根据SID找ORACLE的某个进程:
SQL> SELECT PRO.SPID FROM V$SESSION SES,V$PROCESS PRO WHERE SES.SID= 21 AND SES.PADDR=PRO.ADDR; 监控当前数据库谁在运行什么SQL语句: SQL>SELECT OSUSER, USERNAME, SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE; 如何查看数据库中某用户,正在运行什么SQL语句 SQL>SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_HASH_VALUE AND S.MACHINE= 'XXXXX' OR USERNAME='WACOS' ; 如何查出前台正在发出的sql语句: SQL> SELECT USER_NAME,SQL_TEXT FROM V$OPEN_CURSOR WHERE SID IN(SELECT SID FROM (SELECT SID,SERIAL# FROM V$SESSION WHERE STATUS= 'ACTIVE' )); 查询当前所执行的SQL语句: SQL> SELECT PROGRAM ,SQL_ADDRESS FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID= 3556 ); PROGRAM SQL_ADDRESS ------------------------------------------------ ---------------- SQLPLUS@CTC20 (TNS V1-V3) 000000038 FCB1A90 SQL> SELECT SQL_TEXT FROM V$SQLAREA WHERE ADDRESS='000000038FCB1A90' ; 找出消耗CPU最高的进程对应的SQL语句: SET LINE 240 SET VERIFY OFF COLUMN SID FORMAT 999 COLUMN PID FORMAT 999 COLUMN S_# FORMAT 999 COLUMN USERNAME FORMAT A9 HEADING "ORA USER" COLUMN PROGRAM FORMAT A29 COLUMN SQL FORMAT A60 COLUMN OSNAME FORMAT A9 HEADING "OS USER" SELECTP.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSEROSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%' ; ENTER VALUE FOR 1: PID¡(这里输入占用CPU最高的进程对应的PID) SET TERMOUT OFF SPOOL MAXCPU.TXT SELECT '++'||S.USERNAME USERNAME,RTRIM(REPLACE(A.SQL_TEXT,CHR(10),''))||';'FROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&&1%' ; Enter value for 1: PID(这里输入占用CPU最高的进程对应的PID) spool off(这句放在最后执行 ) CPU用率最高的2条SQL语句的获取
SELECT SQL_TEXT FROM V$SQL WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID IN (SELECT SID FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID=&PID))); |
SELECT A.TABLESPACE_NAME " 空间名称", 100-ROUND((NVL(B.BYTES_FREE,0)/A.BYTES_ALLOC)*100,2) "占用率(%)", ROUND(A.BYTES_ALLOC/1024/1024,2) "容量(M) ",
ROUND(NVL(B.BYTES_FREE,0)/1024/1024,2) 空闲(M) ",
ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024,2) "使用(M) ",
LARGEST "最大扩展段(M)",TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "采样时间" FROM (SELECT F.TABLESPACE_NAME,SUM(F.BYTES) BYTES_ALLOC,SUM(DECODE(F.AUTOEXTENSIBLE,'YES',F.MAXBYTES,'NO',F.BYTES)) MAXBYTES FROM DBA_DATA_FILES F GROUP BY TABLESPACE_NAME) A,(SELECT F.TABLESPACE_NAME,SUM(F.BYTES) BYTES_FREE FROM DBA_FREE_SPACE F GROUP BY TABLESPACE_NAME) B,(SELECT ROUND(MAX(FF.LENGTH)*16/1024,2 ) LARGEST,TS.NAME TABLESPACE_NAME FROM SYS.FET$ FF, SYS.FILE$ TF,SYS.TS$ TS WHERE TS.TS#=FF.TS# AND FF.FILE#=TF.RELFILE# AND TS.TS#=TF.TS# GROUP BY TS.NAME, TF.BLOCKS) C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
SELECT UPPER(F.TABLESPACE_NAME) "表空间名 ",
D.TOT_GROOTTE_MB "表空间大小(M) ",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M) ",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",F.TOTAL_BYTES "空闲空间(M)",F.MAX_BYTES "最大块(M) " FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2 ) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2 ) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2 ) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
24、查询表空间的碎片程度:
SQL>SELECT TABLESPACE_NAME,COUNT(TABLESPACE_NAME) FROM DBA_FREE_SPACE GROUP BY