玖叶教程网

前端编程开发入门

2024年04月26日

达梦数据库-常用SQl整理笔记（达梦数据库视频教程）

查询版本号

SELECT
	BANNER
FROM
	V$VERSION
WHERE
	BANNER LIKE 'DM Database%'

查询实例信息

select '版本' as 名称,SVR_VERSION as 信息 from v$instance union all
select '数据库名' as 名称,CUR_DATABASE() union all
select '实例名',INSTANCE_NAME from v$instance union all
select '永久魔术值',to_char(PERMANENT_MAGIC()) union all
select '簇大小',SF_GET_EXTENT_SIZE ()||'页' union all
select '页大小',PAGE()/1024||'K' union all
select '字符集',DECODE(UNICODE,'0','GB18030','1','UTF-8','2','EUC-KR') union all
select '大小写',DECODE(SF_GET_CASE_SENSITIVE_FLAG,'0','不敏感','1','敏感') union all
select '空格填充',DECODE(BLANK_PAD_MODE,'0','否','1','是') union all
select '日志大小',MAX(RLOG_SIZE/1024/1024)||'MB' from v$rlogfile union all
select '归档状态',DECODE(ARCH_MODE,'Y','启用','N','未启用') from v$database union all
select '创建时间', cast(CREATE_TIME as varchar(50)) from v$database;

查询当前会话 id

SELECT SESSID ();

查询当前登录用户 id

SELECT UID();

查询会话信息

SELECT
	SESS_ID AS "会话ID",
	DECODE(STATE,
	'CREATE',
	'创建',
	'STARTUP',
	'启动',
	'IDLE',
	'空闲',
	'ACTIVE',
	'活动',
	'WAIT',
	'等待',
	'UNKNOWN',
	'未知') AS "会话状态",
	CREATE_TIME AS "会话创建时间",
	TRX_ID AS "事务ID",
	SQL_TEXT AS "SQL",
	USER_NAME AS "当前用户",
	CURR_SCH AS "当前模式",
	CLNT_TYPE AS "连接类型",
	DECODE(AUTO_CMT,
	'Y',
	'是',
	'N',
	'否') AS "是否自动提交",
	DECODE(DDL_AUTOCMT,
	'Y',
	'是',
	'N',
	'否') AS "DDL是否自动提交",
	APPNAME AS "连接程序名",
	CLNT_IP AS "客户机IP",
	CLNT_HOST AS "客户机名",
	OSNAME AS "客户机系统"
FROM
	V$SESSIONS;

杀掉会话

--根据SESS_ID杀掉会话
SP_CLOSE_SESSION(157792223);

统计会话信息

SELECT
	STATE AS 状态,
	CLNT_IP AS 连接IP,
	COUNT(*)AS 数量
FROM
	V$SESSIONS
GROUP BY
	STATE,
	CLNT_IP

查询锁信息

SELECT
	ADDR AS "锁地址",
	TRX_ID AS "所属事务ID",
	LTYPE AS "锁类型",
	LMODE AS "锁模式",
	DECODE(BLOCKED,
	'1',
	'是',
	'0',
	'否') AS "是否阻塞",
	TABLE_ID AS "对应表锁ID",
	ROW_IDX AS "TID锁事务ID"
FROM
	V$LOCK;

查询发生死锁的历史记录

SELECT
	SESS_ID AS "会话ID",
	TRX_ID AS "事务ID",
	SQL_TEXT AS "产生死锁的SQL",
	HAPPEN_TIME AS "死锁发生时间"
FROM
	V$DEADLOCK_HISTORY;

查询用户列表

SELECT
	TABLESPACE_NAME,
	TABLE_NAME
FROM
	USER_TABLES

查询系统所有表

SELECT
	OWNER,
	TABLE_NAME
FROM
	ALL_TABLES;

查询是否开启归档日志

SELECT
	ARCH_MODE
FROM
	V$DATABASE;

查询有效归档日志中最小的更改编号

SELECT
	MIN(FIRST_CHANGE#) 
FROM
	V$ARCHIVED_LOG
WHERE
	ARCHIVED = 'YES'
	AND DELETED = 'NO'
	AND STATUS = 'A'

查询有效归档日志中最大的更改编号

SELECT
	MAX(NEXT_CHANGE#)
FROM
	V$ARCHIVED_LOG
WHERE
	NAME IS NOT NULL
	AND ARCHIVED = 'YES'
	AND STATUS = 'A'

查询所有可用的归档日志文件

SELECT
	A.NAME AS FILE_NAME,
	A.FIRST_CHANGE# FIRST_CHANGE,
	A.NEXT_CHANGE# NEXT_CHANGE,
	'YES',
	NULL,
	'ARCHIVED',
	A.SEQUENCE# AS SEQ,
	A.DICTIONARY_BEGIN,
	A.DICTIONARY_END
FROM
	V$ARCHIVED_LOG A
WHERE
	A.NAME IS NOT NULL
	AND A.ARCHIVED = 'YES'
	AND A.STATUS = 'A'
	AND A.NEXT_CHANGE# > 618083584
ORDER BY
	7

这边sql 中过滤掉了 SCN 小于的 618083584 的记录

删除归档日志

--保留30天归档
SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 30);

查询归档日志详情

SELECT
	*
FROM
	SYS."V$ARCHIVED_LOG";

查询下一个redo日志序列号

SELECT
	NEXT_SEQ
FROM
	V$RLOG；

查询当前redo 日志序列号

SELECT
	NEXT_SEQ -1
FROM
	V$RLOG；

如何判断 redo 日志发生切换

两次查询当前的 redo日志序列号,如果两次结果不一样，则说明redo日志发生了切换。

查询redo文件信息

SELECT
	CLIENT_PATH AS "日志名",
	PATH AS "路径",
	(RLOG_SIZE / 1024 / 1024) AS "文件大小M",
	CREATE_TIME AS "创建时间"
FROM
	V$RLOGFILE;

查询redo日志详情

SELECT
	CKPT_LSN,
	FILE_LSN,
	FLUSH_LSN,
	CUR_LSN,
	NEXT_SEQ,
	N_MAGIC,
	DB_MAGIC,
	FLUSH_PAGES,
	FLUSHING_PAGES,
	CUR_FILE,
	CUR_OFFSET,
	CKPT_FILE,
	CKPT_OFFSET,
	FREE_SPACE,
	TOTAL_SPACE,
	SUSPEND_TIME,
	UPD_CTL_LSN,
	N_RESERVE_WAIT,
	TOTAL_FLUSH_PAGES,
	TOTAL_FLUSH_TIMES,
	TOTAL_ECPR_FLUSH_PAGES,
	GLOBAL_NEXT_SEQ,
	N_PRIMAY_EP,
	PRIMARY_DB_MAGIC,
	CKPT_N_PRIMAY_EP,
	CKPT_PRIMARY_DB_MAGIC,
	MIN_EXEC_VER,
	MIN_DCT_VER
FROM
	V$RLOG

查询所有表行数

SELECT
	TABLE_NAME AS 表名,
	OWNER AS 所属用户,
	TABLESPACE_NAME AS 所属表空间,
	NUM_ROWS AS 行数
FROM
	DBA_TABLES
WHERE
	OWNER NOT IN ('SYS', 'SYSTEM', 'SYSAUDITOR', 'SYSJOB', 'SYSSSO', 'CTISYS')
	AND TABLESPACE_NAME != 'TEMP';

查询模式下所有表行数

SELECT
	OWNER,
	TABLE_NAME,
	NUM_ROWS,
	TABLESPACE_NAME
FROM
	DBA_TABLES T
WHERE
	T.OWNER = UPPER('TEST');

统计各个模式下表数据量

SELECT
	OWNER,
	SUM(NUM_ROWS)
FROM
	DBA_TABLES
GROUP BY
	OWNER;

查询模式下各个表索引

SELECT
	TABLE_OWNER AS "表归属",
	TABLE_NAME AS "表名",
	OWNER AS "索引归属",
	INDEX_NAME AS "索引名",
	INDEX_TYPE AS "索引类型",
	TABLESPACE_NAME AS "索引所在表空间",
	JOIN_INDEX AS "是否组合索引",
	DECODE(VISIBILITY,
	'VISIBLE',
	'可见',
	'INVISIBLE',
	'不可见') AS "索引是否可见"
FROM
	DBA_INDEXES
WHERE
	OWNER = 'TEST'

其中 TEST是具体的模式名称

查询模式下有哪些表

SELECT
	NAME AS "表名"
FROM
	SYSOBJECTS
WHERE
	SUBTYPE$ = 'UTAB'
	AND SCHID IN (
	SELECT
		A.ID
	FROM
		SYSOBJECTS A,
		ALL_USERS B
	WHERE
		A.TYPE$ = 'SCH'
		AND A.PID = B.USER_ID
		AND A.NAME = 'TEST');

其中 TEST是具体的模式名称

查询模式下表的总数量

SELECT
	count(*)
FROM
	all_all_tables
WHERE
	owner = 'TEST';

其中 TEST是具体的模式名称

查询当前用户下有哪些模式

SELECT
	A.NAME AS "模式名",
	A.ID AS "模式ID"
FROM
	SYSOBJECTS A,
	ALL_USERS B
WHERE
	A.TYPE$ = 'SCH'
	AND A.PID = B.USER_ID;

查询所有用户信息

SELECT
	USER_ID AS "用户ID",
	USERNAME AS "用户名",
	ACCOUNT_STATUS AS "状态",
	DEFAULT_TABLESPACE AS "默认表空间",
	CREATED AS "创建时间",
	LOCK_DATE AS "锁定开始时间",
	EXPIRY_DATE AS "密码有效期截止"
FROM
	DBA_USERS;

查询表结构信息

-- 查看TEST模式下TEST_TABLE表的结构
SELECT
	TABLEDEF('TEST', 'TEST_TABLE')
FROM
	DUAL;

查询数据库实例信息

SELECT
	B.SVR_VERSION AS "数据库版本",
	C.DB_MAGIC AS "数据库MAGIC",
	A.NAME AS "数据库名",
	B.INSTANCE_NAME AS "实例名",
	B.STATUS$ AS "系统状态",
	B.MODE$ AS "模式",
	DECODE(A.ARCH_MODE,
	'Y',
	'是',
	'N',
	'否') AS "是否归档",
	B.START_TIME AS "服务启动时间"
FROM
	V$DATABASE A,
	V$INSTANCE B,
	V$RLOG C;

查询表空间信息

SELECT
	NAME AS 名称,
	DECODE(TYPE$,
	'1',
	'DB类型',
	'2',
	'临时表空间')AS 类型,
	DECODE(STATUS$,
	'0',
	'联机',
	'1',
	'脱机',
	'2',
	'RES_OFFLINE',
	'3',
	'CORRUPT')AS 状态,
	TOTAL_SIZE * PAGE / 1024 / 1024 AS 总大小MB,
	FILE_NUM AS 包含文件数
FROM
	V$TABLESPACE;

查询数据文件信息

SELECT
	PATH AS 文件路径,
	(TOTAL_SIZE * PAGE / 1024 / 1024)AS 文件大小MB,
	(FREE_SIZE * PAGE / 1024 / 1024)AS 剩余大小MB,
	(CAST((TOTAL_SIZE-FREE_SIZE)* 100 / TOTAL_SIZE AS NUMERIC(2,
	0))|| '%') AS 使用比例,
	DECODE(AUTO_EXTEND,
	'0',
	'关闭',
	'1',
	'打开') AS 自动扩展,
	NEXT_SIZE AS 扩充尺寸MB,
	MAX_SIZE AS 扩充上限MB,
	CREATE_TIME AS 创建时间,
	MODIFY_TIME AS 修改时间
FROM
	V$DATAFILE;

查询阻塞信息

  • dm8
WITH TRX_TAB AS(
SELECT
	DISTINCT 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.TID 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.TID = S2.TRX_ID
)SELECT
	SYSDATE STATTIME,
	*
FROM
	TRX_SESS
WHERE
	BLOCKED = 1;
  • dm7
WITH LOCKS AS(
SELECT
	O.NAME,
	L.*,
	S.SESS_ID,
	S.SQL_TEXT,
	S.CLNT_IP,
	S.LAST_SEND_TIME
FROM
	V$LOCK L,
	SYSOBJECTS O,
	V$SESSIONS S
WHERE
	L.TABLE_ID = O.ID
	AND L.TRX_ID = S.TRX_ID),
LOCK_TR AS(
SELECT
	TRX_ID WT_TRXID,
	ROW_IDX BLK_TRXID
FROM
	LOCKS
WHERE
	BLOCKED = 1),
RES AS(
SELECT
	SYSDATE STATTIME,
	T1.NAME,
	T1.SESS_ID WT_SESSID,
	S.WT_TRXID,
	T2.SESS_ID BLK_SESSID,
	S.BLK_TRXID,
	T2.CLNT_IP,
	SF_GET_SESSION_SQL(T1.SESS_ID)FULSQL,
	DATEDIFF(SS, T1.LAST_SEND_TIME, SYSDATE)SS,
	T1.SQL_TEXT WT_SQL
FROM
	LOCK_TR S,
	LOCKS T1,
	LOCKS T2
WHERE
	T1.LTYPE = 'OBJECT'
	AND T1.TABLE_ID <> 0
	AND T2.LTYPE = 'OBJECT'
	AND T2.TABLE_ID <> 0
	AND S.WT_TRXID = T1.TRX_ID
	AND S.BLK_TRXID = T2.TRX_ID)
SELECT
	DISTINCT WT_SQL,
	CLNT_IP,
	SS,
	WT_TRXID,
	BLK_TRXID
FROM
	RES;

查询等待事件

SELECT
	THREAD_ID AS "线程ID",
	TRX_ID AS "事务ID",
	WAIT_CLASS AS "等待类型号",
	WAIT_OBJECT AS "等待对象",
	WAIT_START AS "等待开始时间",
	TRUNC(WAIT_TIME, 6)/ 1000000 AS "等待时间"
FROM
	V$WAIT_HISTORY;

查询历史错误信息

SELECT
	SESS_ID AS "会话ID",
	TRX_ID AS "事务ID",
	DECODE(SU_FLAG,
	'U',
	'用户异常',
	'S',
	'系统异常',
	'P',
	'语法异常') AS "错误类型",
	SQL_TEXT AS "错误的SQL",
	ECPT_CODE AS "错误提示",
	ERR_TIME AS "产生时间"
FROM
	V$RUNTIME_ERR_HISTORY;

清除当日之前的空闲会话

DECLARE
  vsessid varchar(50);

venddate varchar(8);

isessioncnt int;

CURSOR c1;
BEGIN
	vsessid := '';

SELECT
	to_char(sysdate , 'yyyymmdd')
INTO
	venddate
FROM
	dual;
--获取指定日期
  SELECT
	count(1)
INTO
	isessioncnt
FROM
	v$sessions
WHERE
	state = 'IDLE'
	AND to_char(last_recv_time, 'yyyymmdd') < venddate;
--查看会话数
  OPEN c1 FOR
SELECT
	sess_id
FROM
	v$sessions
WHERE
	state = 'IDLE'
	AND to_char(create_time, 'yyyymmdd') < venddate;
--打开游标
loop
--循环获取会话ID
      IF c1%NOTFOUND THEN EXIT;
END IF;

FETCH c1
INTO
	vsessid;

sp_close_session(vsessid);
--删除会话
END loop;
--关闭游标
  CLOSE c1;
END;

清除大于1800的空闲会话

DECLARE
  vsessid varchar(50);

venddate varchar(8);

isessioncnt int;

CURSOR c1;
BEGIN
	vsessid := '';

SELECT
	to_char(sysdate + 1 , 'yyyymmdd')
INTO
	venddate
FROM
	dual;
--获取指定日期
  SELECT
	count(1)
INTO
	isessioncnt
FROM
	v$sessions
WHERE
	state = 'IDLE'
	AND to_char(last_recv_time, 'yyyymmdd') < venddate;
--查看会话数
  IF isessioncnt > 1800 THEN 	  
    OPEN c1 FOR
SELECT
	sess_id
FROM
	v$sessions
WHERE
	state = 'IDLE'
	AND to_char(create_time, 'yyyymmdd') < venddate;
--打开游标
loop
--循环获取会话ID
        IF c1%NOTFOUND THEN EXIT;
END IF;

FETCH c1
INTO
	vsessid;

sp_close_session(vsessid);
--删除会话
END loop;

CLOSE c1;
--关闭游标
END IF;
END;

缩小TEMP表空间

--缩小TEMP表空间大小为32M
SP_TRUNC_TS_FILE(3,0,32);

清理执行计划缓存

SP_CLEAR_PLAN_CACHE();

查询备份文件类型

SELECT
	DECODE(SF_BAK_GET_TYPE('/opt/bak/all.bak'),
	'0',
	'全量',
	'1',
	'增量',
	'2',
	'B树');

查询备份文件方式

SELECT
	DECODE(SF_BAK_GET_LEVEL('/opt/bak/all.bak'),
	'0',
	'联机备份',
	'1',
	'脱机备份');

查询备份文件时间

SELECT
	SF_BAK_GET_TIME('/opt/bak/all.bak');

查询备份文件簇大小

SELECT
	SF_BAK_GET_EXTENT_SIZE('/opt/bak/all.bak')|| '页';

查询备份文件页大小

SELECT
	SF_BAK_GET_PAGE_SIZE('/opt/bak/all.bak')/ 1024 || 'K';

查询备份文件大小写

SELECT
	DECODE(SF_BAK_GET_CASE_SENSITIVE('/opt/bak/all.bak'),
	'0',
	'不敏感',
	'1',
	'敏感');

查询备份文件数据库版本

SELECT
	SF_BAK_GET_GLOBAL_VERSION('/opt/bak/all.bak');

查询备份文件是否有归档

SELECT
	DECODE(SF_BAK_GET_ARCH_FLAG('/opt/bak/all.bak',
	'0',
	'未归档',
	'1',
	'归档');

查询备份文件是否加密

SELECT
	DECODE(SF_BAK_GET_ENCRYPT_TYPE ('/opt/bak/all.bak'),
	'0',
	'未加密',
	'1',
	'加密');

查询备份文件是压缩

SELECT
	DECODE(SF_BAK_GET_COMPRESSED('/opt/bak/all.bak'),
	'0',
	'未压缩',
	'1',
	'压缩');

监控数据库内存

--创建基础表
CREATE TABLE MEM_POOL("CONTTIME" TIMESTAMP(0),
"SUM(ORG)" VARCHAR2(20),
"SUM(TOTAL)" VARCHAR2(20));

--插入当前内存信息
INSERT
	INTO
	MEM_POOL
VALUES (SYSDATE(),
(
SELECT
	sum(ORG_SIZE)/ 1024 / 1024
FROM
	v$mem_pool),
(
SELECT
	sum(TOTAL_SIZE)/ 1024 / 1024
FROM
	v$mem_pool));

COMMIT;

监控指定索引是否使用

CREATE TABLE T1("C1" CHAR(10)); --创建测试表
CREATE INDEX IDX_T1_C1 ON T1("C1" ASC); --创建IDX_T1_C1索引
ALTER INDEX SYSDBA."IDX_T1_C1" MONITORING USAGE; --监控IDX_T1_C1索引
SELECT * FROM T1 WHERE T1.C1 = '1'; --使用IDX_T1_C1索引
ALTER INDEX SYSDBA."IDX_T1_C1" NOMONITORING USAGE; --取消监控INDEX_T1_C1索引
SELECT SCH_NAME AS "所属模式",TABLE_NAME AS "所属表",INDEX_NAME AS "索引名称", MONITORING AS "是否监控",USED AS "是否使用",START_MONITORING AS "开始监控时间",END_MONITORING AS "停止监控时间" FROM V$OBJECT_USAGE; --查看监控结果

监控指定模式下索引是否使用

  • 监控SYSDBA模式下的索引
SELECT
	'ALTER INDEX ' || OWNER || '."' || OBJECT_NAME || '" ' || 'MONITORING USAGE;'
FROM
	DBA_OBJECTS
WHERE
	OBJECT_TYPE = 'INDEX'
	AND GENERATED = 'N'
	AND OWNER = 'SYSDBA';
  • 取消监控SYSDBA模式下的索引
SELECT
	'ALTER INDEX ' || OWNER || '."' || OBJECT_NAME || '" ' || 'NOMONITORING USAGE;'
FROM
	DBA_OBJECTS
WHERE
	OBJECT_TYPE = 'INDEX'
	AND GENERATED = 'N'
	AND OWNER = 'SYSDBA';
  • 查看监控结果
SELECT
	SCH_NAME AS "所属模式",
	TABLE_NAME AS "所属表",
	INDEX_NAME AS "索引名称",
	MONITORING AS "是否监控",
	USED AS "是否使用",
	START_MONITORING AS "开始监控时间",
	END_MONITORING AS "停止监控时间"
FROM
	V$OBJECT_USAGE;

参考文章：https://blog.csdn.net/yuDazzle/article/details/118671006

标签：sqldatediff 

作者:gojiuye , 分类:技术精选 , 浏览:13 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言