查看数据库初始化参数等信息
--DM8较新版本直接查询V$OPTION视图:
SELECT * FROM V$OPTION;
---通用
SELECT '实例名称' AS 数据库参数名称,INSTANCE_NAME 数据库参数值 FROM V$INSTANCE UNION ALL
SELECT '数据库版本', BANNER||'.'||ID_CODE FROM V$VERSION WHERE ROWNUM=1 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(SF_GET_CASE_SENSITIVE_FLAG() AS VARCHAR) UNION ALL
SELECT 'VARCHAR类型是否以字符为单位',VALUE FROM V$PARAMETER WHERE NAME='LENGTH_IN_CHAR' UNION ALL
SELECT '空格填充模式',VALUE FROM V$PARAMETER WHERE NAME='BLANK_PAD_MODE' UNION ALL
SELECT '数据库模式',MODE$ FROM V$INSTANCE UNION ALL
SELECT '数据库端口',VALUE FROM V$PARAMETER WHERE NAME='PORT_NUM' UNION ALL
SELECT '归档模式',ARCH_MODE FROM V$DATABASE UNION ALL
SELECT '唯一魔数',CAST(PERMANENT_MAGIC AS VARCHAR) UNION ALL
SELECT 'LSN',CAST(CUR_LSN AS VARCHAR) FROM V$RLOG ;
查看活动SQL语句执行时间
select datediff(s, last_recv_time, sysdate) exectime,
dbms_lob.SUBSTR(sf_get_session_sql(sess_id)) sql_txt,
CUR_SQLSTR,
sess_id,
state,
user_name,
clnt_ip,
clnt_ver
from v$sessions
where state = 'ACTIVE'
order by exectime desc
查询历史耗时SQL
---最近1000条执行时间较长的SQL(默认记录SQL执行时间大于1s的SQL语句,记录总条数和记录执行时间阈值可以手动调整)
select * from v$long_exec_sqls order by 4 desc;
---显示服务器启动以来执行时间最长的20条SQL语句(默认记录SQL执行时间大于1s的SQL语句,记录总条数和记录执行时间阈值可以手动调整)
select * from V$SYSTEM_LONG_EXEC_SQLS order by 5 desc,4 desc;
---查询历史执行SQL语句
select * from v$sql_history order by time_used desc;
主外键对应关系
select
a.owner,
a.table_name PK_TABLE,
a.constraint_name PK_CONSTRAINT,
b.owner,
b.table_name FK_TABLE,
b.CONSTRAINT_NAME FK_CONSTRAINT
from dba_constraints a,
dba_constraints b
where a.CONSTRAINT_TYPE='P'
and b.R_CONSTRAINT_NAME=a.constraint_name
and a.owner=b.owner;
外键列创建索引
--查找数据库中外键列未创建索引的表并生成创建索引语句
with cons as
(select owner, table_name, constraint_name
from dba_constraints
where constraint_type = 'R'),
idxs as
(select a.table_owner, a.table_name, a.column_name
from dba_ind_columns a, dba_indexes b
where a.table_owner = b.owner
and a.index_name = b.index_name
and b.index_type <> 'VIRTUAL'
),
fk_cols as
(select owner,
table_name,
constraint_name,
listagg('"' || column_name || '"', ',') WITHIN GROUP(ORDER BY TABLE_NAME) AS FK_COLUMNS
from dba_cons_columns
where (owner, table_name, constraint_name) in (select * from cons)
and (owner, table_name, column_name) not in
(select table_owner, table_name, column_name
from idxs)
group by owner, table_name, constraint_name)
select *,
'CREATE INDEX IDX_' || constraint_name || ' ON "' || owner || '"."' ||
table_name || '"(' || FK_COLUMNS || ');' as CREATE_FK_INDEX_SQL
from fk_cols
where owner = 'AAAA' /*指定模式名*/
--and table_name='TD_GWCL_RETREAT_ORG_REGISTER' /*指定表名*/
;
--或者
SELECT
'alter table "'
||t1.table_NAME
||'" modify constraint "'
||t1.CONSTRAINT_NAME
||'" to foreign key("'
||t3.COLUMN_NAME
||'") references "'
||t2.table_name
||'"("'
||t4.column_name
||'") with index;'
/*t1.owner AS FK_OWNER,
t1.table_name AS FK_TABLE,
t1.constraint_name AS FK ,
t3.column_name AS FK_COL ,
T2.OWNER AS PK_OWNER,
t2.table_name AS PK_TABLE,
t1.r_constraint_name AS PK ,
t4.column_name AS PK_COL,
T1.DELETE_RULE*/
from
dba_constraints t1 ,
dba_constraints t2 ,
DBA_CONS_COLUMNS t3,
DBA_CONS_COLUMNS T4
where
t1.constraint_type='R'
and t2.constraint_type='P'
and t2.constraint_name=t1.r_constraint_name
and t3.constraint_name=t1.constraint_name
AND T2.constraint_name=T4.constraint_name
and t2.table_name ='表名'
查看内存中的执行计划,输出到文本
---查询语句执行计划缓存地址
select cache_item from v$cachepln where sqlstr like 'select * from t1 where id%';
---打印内存中对应的执行计划
alter session set events 'immediate trace name plndump level 131111152555,dump_file ''/home/dmdba/sql111.log''';
清理内存中执行计划缓存
---清理指定的执行计划
CALL SP_CLEAR_PLAN_CACHE(473546872);
---清理内存中所有执行计划缓存
CALL SP_CLEAR_PLAN_CACHE();
查询会话阻塞
---DM8 DSC集群
select * from V$DSC_TRXWAIT;
select s1.instance_name,s1.user_name,s1.curr_sch,s1.sess_id "被阻塞sess_id",s1.sql_text,
s2.instance_name,s2.user_name,s2.curr_sch,s2.sess_id "产生阻塞sess_id",s2.sql_text,w1.wait_time
from gv$sessions s1,gv$sessions s2,v$dsc_trxwait w1
where
s1.trx_id=w1.trx_id
and s2.trx_id=w1.WAIT_TRX_ID
;
---DM8单机锁阻塞查询:
select *from v$trxwait;
select s1.user_name,s1.curr_sch,s1.sess_id "被阻塞sess_id",s1.sql_text,
s2.user_name,s2.curr_sch,s2.sess_id "产生阻塞sess_id",s2.sql_text,w1.wait_time
from v$sessions s1,v$sessions s2,v$trxwait w1
where
s1.trx_id=w1.id
and s2.trx_id=w1.WAIT_FOR_ID ;
WITH LOCK_TAB as(SELECT L1.TRX_ID,L1.TID,L1.LMODE, O1.NAME,L1.BLOCKED from V$LOCK L1,SYSOBJECTS O1 where
O1.ID=L1.TABLE_ID AND L1.BLOCKED<>0)
SELECT
L2.NAME WT_TABLE ,
L2.TRX_ID WT_TRXID ,
L2.TID BLK_TRXID ,
S1.SESS_ID WT_SESS ,
S2.SESS_ID BLK_SESS ,
S1.USER_NAME WT_USER_NAME ,
S2.USER_NAME BLK_USER_NAME,
L2.BLOCKED ,
L2.LMODE,
S1.SQL_TEXT ,
S1.CLNT_IP ,
DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) WAIT_TIME
FROM
V$SESSIONS S1,
V$SESSIONS S2,
LOCK_TAB L2
WHERE
L2.TRX_ID=S1.TRX_ID
AND L2.TID =S2.TRX_ID ;
---DM7锁阻塞查询:
WITH LOCK_INFO as
(SELECT L1.TRX_ID,L1.ROW_IDX,L1.LMODE,L2.TABLE_ID,L1.BLOCKED
FROM V$LOCK L1,V$LOCK L2
WHERE L1.TRX_ID=L2.TRX_ID AND L1.BLOCKED<>0 AND L2.TABLE_ID<>0)
SELECT
O.NAME WT_TABLE ,
L.TRX_ID WT_TRXID ,
L.ROW_IDX BLK_TRXID ,
S1.SESS_ID WT_SESS ,
S1.SESS_ID BLK_SESS ,
S1.USER_NAME WT_USER_NAME ,
S1.USER_NAME BLK_USER_NAME,
L.BLOCKED ,
L.LMODE,
S1.SQL_TEXT ,
S1.CLNT_IP ,
DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) WAIT_TIME
FROM
V$SESSIONS S1,
V$SESSIONS S2,
LOCK_INFO L,
SYSOBJECTS O
WHERE
L.TRX_ID=S1.TRX_ID
AND L.ROW_IDX =S2.TRX_ID
AND L.TABLE_ID=O.ID ;
查询用户拥有的模式:
SELECT B.USERNAME,A.NAME SCHEMA_NAME
FROM SYSOBJECTS A,DBA_USERS B
WHERE A.PID=B.USER_ID
AND A.TYPE$='SCH'
ORDER BY B.USERNAME;
---或者
SELECT B.USERNAME,listagg(A.NAME,',') within group (order by B.USERNAME) as SCHEMAS
FROM SYSOBJECTS A,DBA_USERS B
WHERE A.PID=B.USER_ID
AND A.TYPE$='SCH'
GROUP BY B.USERNAME;
DM 武汉达梦数据库股份有限公司
24小时免费服务热线:400 991 6599
达梦在线服务平台:https://eco.dameng.com