DM数据库

--锁查询
select w.* ,t.sess_id ,s.* from v$trxwait w join v$trx t on w.id=t.id join v$sessions s on t.sess_id = s.sess_id;

select t.id,s.* from v$trxwait w join v$trx t on w.wait_for_id =t.id join v$sessions s on t.sess_id =s.sess_id;
--阻塞信息
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;
--查询未提交的事务
select t1.user_name,t1.sess_id,t1.sql_text,t1.state,t1.trx_id,T2.INS_CNT,T2.DEL_CNT,T2.UPD_CNT from v$sessions t1,v$trx t2 where t1.trx_id=t2.id and t1.state='IDLE' AND T2.STATUS='ACTIVE'

--结束会话
select 'SP_CLOSE_SESSION('||SESS_ID||');',datediff(ss, last_recv_time, sysdate) ss,
sf_get_session_sql(sess_id),*
from v$sessions
where trx_id in
(select wait_for_id
from v$trxwait
where wait_for_id not in (select id from v$trxwait));

--查询当前最大会话 实际
select * from v$dm_ini where PARA_NAME='MAX_SESSIONS';

select 索引名称,索引类型,表名,是否为唯一,cast(wm_concat(索引列) as varchar(5000)) from (

 select a.index_name 索引名称, 
           index_type  索引类型, 
           a.table_name  表名, 
           b.column_name 索引列,
           a.uniqueness 是否为唯一
      from user_indexes a 
inner join user_IND_COLUMNS b 
        on a.index_name = b.index_name
        where a.uniqueness='UNIQUE'
        )
        where 索引名称=索引名称
        group by 索引名称,索引类型,表名,是否为唯一;


SELECT '实例名称' 数据库选项,INSTANCE_NAME  数据库集群相关参数值 FROM V$INSTANCE UNION ALL
SELECT '数据库名',CUR_DATABASE()FROM DUAL UNION ALL
SELECT '授权客户',(SELECT AUTHORIZED_CUSTOMER FROM V$LICENSE) UNION ALL
SELECT '数据库授权码',(SELECT SERIES_NO FROM V$LICENSE) UNION ALL
SELECT '数据库有效期',CAST((SELECT EXPIRED_DATE FROM V$LICENSE)AS VARCHAR) UNION ALL
SELECT '数据库版本',SUBSTR(SVR_VERSION,INSTR(SVR_VERSION,'('))  FROM V$INSTANCE UNION ALL
SELECT '数据库版本小号',(SELECT BUILD_VERSION  FROM V$INSTANCE) UNION ALL
SELECT '数据库实例路径',(SELECT PARA_VALUE FROM V$DM_INI WHERE PARA_NAME LIKE'%SYSTEM_PATH%')  FROM V$INSTANCE UNION ALL
SELECT '数据库模式',MODE$ FROM V$INSTANCE  UNION ALL
SELECT '数据库状态',STATUS$ FROM V$INSTANCE UNION ALL
SELECT 'OGUID',CAST(OGUID AS VARCHAR) FROM V$INSTANCE UNION ALL
SELECT '归档状态_开 Y/关 N',ARCH_MODE FROM V$DATABASE UNION ALL
SELECT '长度是否以字符为单位',CASE (SELECT PARA_VALUE FROM V$DM_INI WHERE "V$DM_INI".PARA_NAME ='LENGTH_IN_CHAR') WHEN '0' THEN '否' WHEN '1' THEN '是' END UNION ALL
SELECT '大小写是否敏感_是 Y,1/否N,0',CAST(SF_GET_CASE_SENSITIVE_FLAG() AS VARCHAR) UNION ALL
SELECT '字符集',CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' THEN 'UTF-8' WHEN '2' THEN 'EUC-KR' END UNION ALL
SELECT '页大小',CAST(PAGE()/1024 AS VARCHAR) UNION ALL
SELECT '簇大小',CAST(SF_GET_EXTENT_SIZE() AS VARCHAR) UNION ALL
SELECT '唯一魔数',CAST(PERMANENT_MAGIC AS VARCHAR) UNION ALL
SELECT 'LSN',CAST(CUR_LSN AS VARCHAR) FROM V$RLOG UNION ALL
SELECT '当前登录用户',USER;
 

查询未释放连接的连接来源

SELECT
STATE "会话状态" ,
CLNT_IP "客户端IP",
CLNT_TYPE "连接类型",
CURR_SCH "当前模式",USER_NAME "当前用户",
COUNT(*) "会话数"
FROM
V$SESSIONS
GROUP BY
STATE ,
CLNT_IP ,
CLNT_TYPE,
CURR_SCH ,
USER_NAME
ORDER BY
STATE;

慢sql

SELECT * FROM V$LONG_EXEC_SQLS WHERE exec_time>1000 ORDER BY exec_time desc;