-
ps -ef|grep dms|grep -v grep|awk '{print $9}'
-
- ps -ef|grep dms|grep -v grep|awk '{print $9}'|awk -F"=" '{print $2}'
-
--表空间使用
-
set pagesize 100
-
-
SELECT F.TABLESPACE_NAME,round(T.TOTAL_SPACE / 1024,1) TOTAL_G,
-
round((T.TOTAL_SPACE - F.FREE_SPACE) / 1024,1) USED_G,round(F.FREE_SPACE / 1024,1) FREE_G,
-
ROUND(((T.TOTAL_SPACE - F.FREE_SPACE) / T.TOTAL_SPACE) * 100) "PER_USED%"
-
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BLOCKS *
-
(SELECT PARA_VALUE / 1024 FROM V$DM_INI
-
WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE
-
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
-
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
-
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T
-
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME ORDER BY 5 DESC;
-
-
--监控运行时错误历史
-
select * from V$RUNTIME_ERR_HISTORY;
-
-
--死锁历史信息记录
-
select * from V$DEADLOCK_HISTORY;
-
--查看数据库是否存在阻塞
-
WITH TRX_TAB AS
-
(SELECT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND O1.ID<>0),
-
TRX_SESS AS (
-
SELECT L.TRX_ID WT_TRXID, L.ROW_IDX BLK_TRXID,L.BLOCKED,(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID=L.TRX_ID) WT_TABLE,
-
S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS,
-
S1.USER_NAME WT_USER_NAME,S2.USER_NAME BLK_USER_NAME,S1.SQL_TEXT,S1.CLNT_IP,DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS
-
FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2
-
WHERE L.TRX_ID=S1.TRX_ID AND L.ROW_IDX=S2.TRX_ID)
-
SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1;
-
-
-
--按小时统计arch
-
select to_char(CREATE_TIME,'yy-mm-dd hh24') xiaoshi,round(sum(free)/1024/1024) mb,count(0) cnt from v$arch_file group by to_char(CREATE_TIME,'yy-mm-dd hh24') order by 1;
-
-
-
-
--最慢的 20 条 SQL
-
SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;
-
SELECT top 20 * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;
-
--高内存的 20 条 SQL 信息
-
select top 20 * from V$SYSTEM_LARGE_MEM_SQLS order by mem_used_by_k desc;
-
-
--查看所有作业信息
-
SELECT A.ID,A.NAME,A."ENABLE",A.USERNAME,A.CREATETIME,A.MODIFYTIME,A.DESCRIBE,
-
B.LAST_DATE||' '||B.LAST_SEC LAST_TIME,B.NEXT_DATE||' '||B.NEXT_SEC NEXT_TIME,B.WHAT
-
FROM SYSJOB.SYSJOBS A,SYSJOB.USER_JOBS B
-
WHERE A.ID=B.JOB;
-
-
-
--用户权限
-
SELECT * FROM (
-
SELECT GRANTEE,GRANTED_ROLE PRIVILEGE,'ROLE_PRIVS' PRIVILEGE_TYPE,CASE ADMIN_OPTION WHEN 'Y' THEN 'YES' ELSE 'NO' END ADMIN_OPTION FROM DBA_ROLE_PRIVS
-
UNION SELECT GRANTEE,PRIVILEGE,'SYS_PRIVS' PRIVILEGE_TYPE,ADMIN_OPTION FROM DBA_SYS_PRIVS
-
UNION SELECT GRANTEE,PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME PRIVILEGE,'TABLE_PRIVS' PRIVILEGE_TYPE,GRANTABLE FROM DBA_TAB_PRIVS
-
)
-
WHERE GRANTEE IN (SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSDBA','SYSSSO','SYSAUDITOR') )
- ORDER BY GRANTEE,PRIVILEGE_TYPE,PRIVILEGE;