【达梦8】sql语句学习笔记

达梦角色说明

角色名称	角色简单说明
DBA	DM 数据库系统中对象与数据操作的最高权限集合，拥有构建数据库的全部特权，只有 DBA 才可以创建数据库结构
RESOURCE	可以创建数据库对象，对有权限的数据库对象进行数据操纵，不可以创建数据库结构
PUBLIC	不可以创建数据库对象，只能对有权限的数据库对象进行数据操纵
VTI	具有系统动态视图的查询权限，VTI 默认授权给 DBA 且可转授
SOI	具有系统表的查询权限
SVI	具有基础 V 视图的查询权限
DB_AUDIT_ADMIN	数据库审计的最高权限集合，可以对数据库进行各种审计操作，并创建新的审计用户
DB_AUDIT_OPER	可以对数据库进行各种审计操作，但不能创建新的审计用户
DB_AUDIT_PUBLIC	不能进行审计设置，但可以查询审计相关字典表
DB_AUDIT_VTI	具有系统动态视图的查询权限，DB_AUDIT_VTI 默认授权给DB_AUDIT_ADMIN
DB_AUDIT_SOI	具有系统表的查询权限
DB_AUDIT_SVI	具有基础 V 视图和审计 V 视图的查询权
DB_POLICY_ADMIN	数据库强制访问控制的最高权限集合，可以对数据库进行强制访问控制管理，并创建新的安全管理用户
DB_POLICY_OPER	可以对数据库进行强制访问控制管理，但不能创建新的安全管理用户
DB_POLICY_PUBLIC	不能进行强制访问控制管理，但可以查询强制访问控制相关字典表
DB_POLICY_VTI	具 有 系 统 动 态 视 图 的 查 询 权 限 ， DB_POLICY_VTI 默 认 授 权 给DB_POLICY_ADMIN 且可转授
DB_POLICY_SOI	具有系统表的查询权限
DB_POLICY_SVI	具有基础 V 视图和安全 V 视图的查询权限
DB_OBJECT_ADMIN	可以在自己的模式下创建各种数据库对象并进行数据操纵，也可以创建和删除非模式对象
DB_OBJECT_OPER	可以在自己的模式下创建数据库对象并进行数据操纵
DB_OBJECT_PUBLIC	不可以创建数据库对象，只能对有权限的数据库对象进行数据操纵
DB_OBJECT_VTI	具 有 系 统 动 态 视 图 的 查 询 权 限 ， DB_OBJECT_VTI 默 认 授 权 给DB_OBJECT_ADMIN 且可转授
DB_OBJECT_SOI	具有系统表的查询权限
DB_OBJECT_SVI	和 SVI 权限一样

权限说明表

权限	说明
ALTER DATABASE	修改数据库
RESTORE DATABASE	恢复数据库
CREATE USER	创建用户
ALTER USER	修改用户
DROP USER	丢弃用户
CREATE ROLE	创建角色
CREATE SCHEMA	创建模式
CREATE TABLE	创建数据表
CREATE VIEW	创建视图
CREATE PROCEDURE	创建存储过程
CREATE SEQUENCE	创建序列
CREATE TRIGGER	创建触发器
CREATE INDEX	创建索引
CREATE CONTEXT INDEX	创建上下文索引
BACKUP DATABASE	备份数据库
CREATE LINK	创建链接
CREATE REPLICATE	创建副本
CREATE PACKAGE	创建包
CREATE SYNONYM	创建同义词
CREATE PUBLIC SYNONYM	创建公共同义词
ALTER REPLICATE	修改副本
DROP REPLICATE	删除副本
DROP ROLE	丢弃角色
ADMIN ANY ROLE	管理任何角色
ADMIN ANY DATABASE PRIVILEGE	管理任何数据库权限
GRANT ANY OBJECT PRIVILEGE	授予任何对象特权
CREATE ANY SCHEMA	创建任何模式
DROP ANY SCHEMA	丢弃任何模式
CREATE ANY TABLE	创建任何数据表
ALTER ANY TABLE	修改任何数据表
DROP ANY TABLE	丢弃任何数据表
INSERT TABLE	插入数据表
INSERT ANY TABLE	插入任何数据表
UPDATE TABLE	更新数据表
UPDATE ANY TABLE	更新任何数据表
DELETE TABLE	删除数据表
DELETE ANY TABLE	删除任何数据表
SELECT TABLE	查询数据表
SELECT ANY TABLE	查询任何数据表
REFERENCES TABLE	引用表
REFERENCES ANY TABLE	引用任何表
GRANT TABLE	授权数据表
GRANT ANY TABLE	授权任何数据表
CREATE ANY VIEW	创建任何视图
ALTER ANY VIEW	修改任何视图
DROP ANY VIEW	丢弃任何视图
INSERT VIEW	插入视图
INSERT ANY VIEW	插入任何视图
UPDATE VIEW	更新视图
UPDATE ANY VIEW	更新任何视图
DELETE VIEW	删除视图
DELETE ANY VIEW	删除任何视图
SELECT VIEW	查询视图
SELECT ANY VIEW	查询任何视图
GRANT VIEW	授权视图
GRANT ANY VIEW	授权任何视图
CREATE ANY PROCEDURE	创建任何存储过程
DROP ANY PROCEDURE	丢弃任何存储过程
EXECUTE PROCEDURE	执行存储过程
EXECUTE ANY PROCEDURE	执行任何存储过程
GRANT PROCEDURE	授权存储过程
GRANT ANY PROCEDURE	授权任何存储过程
CREATE ANY SEQUENCE	创建任何序列
DROP ANY SEQUENCE	丢弃任何序列
SELECT SEQUENCE	查询序列
SELECT ANY SEQUENCE	查询任何序列
GRANT SEQUENCE	授权序列
GRANT ANY SEQUENCE	授权任何序列
CREATE ANY TRIGGER	创建任何触发器
DROP ANY TRIGGER	丢弃任何触发器
CREATE ANY INDEX	创建任何索引
ALTER ANY INDEX	修改任何索引
DROP ANY INDEX	丢弃任何索引
CREATE ANY CONTEXT INDEX	创建任何上下文索引
ALTER ANY CONTEXT INDEX	修改任何上下文索引
DROP ANY CONTEXT INDEX	丢弃任何上下文索引
CREATE ANY PACKAGE	创建任何包
DROP ANY PACKAGE	丢弃任何包
EXECUTE PACKAGE	执行包
EXECUTE ANY PACKAGE	执行任何包
GRANT PACKAGE	授权包
GRANT ANY PACKAGE	授权任何包
CREATE ANY LINK	创建任何链接
DROP ANY LINK	丢弃任何链接
CREATE ANY SYNONYM	创建任何同义词
DROP ANY SYNONYM	丢弃任何同义词
DROP PUBLIC SYNONYM	丢弃公共同义词
SELECT ANY DICTIONARY	查询任何同义词
ADMIN REPLAY	管理重演
ADMIN BUFFER	管理缓冲区
CREATE TABLESPACE	创建表空间
ALTER TABLESPACE	修改表空间
DROP TABLESPACE	丢弃表空间
ALTER ANY TRIGGER	修改任何触发器
CREATE MATERIALIZED VIEW	创建物化视图
CREATE ANY MATERIALIZED VIEW	创建任何物化视图
DROP ANY MATERIALIZED VIEW	丢弃任何物化视图
ALTER ANY MATERIALIZED VIEW	修改任何物化视图
SELECT MATERIALIZED VIEW	查询物化视图
SELECT ANY MATERIALIZED VIEW	查询任何物化视图
CREATE ANY DOMAIN	创建任何域
DROP ANY DOMAIN	丢弃任何域
CREATE DOMAIN	创建域
GRANT ANY DOMAIN	授权任何域
GRANT DOMAIN	授权域
USAGE ANY DOMAIN	使用任何域
USAGE DOMAIN	使用域
CREATE ANY CONTEXT	创建任何上下文
DROP ANY CONTEXT	丢弃任何上下文
GRANT ANY CONTEXT	授权任何上下文
COMMENT ANY TABLE	注释任何数据表
DUMP ANY TABLE	转储任何数据表
DUMP TABLE	转储数据表
ADMIN JOB	管理任务
CREATE ANY DIRECTORY	创建任何目录
DROP ANY DIRECTORY	丢弃任何目录
ALTER ANY SEQUENCE	修改任何序列
CREATE PROFILE	创建资源限制
ALTER PROFILE	修改资源限制
DROP PROFILE	丢弃资源限制
CREATE PARTITION GROUP	创建分区组
CREATE ANY PARTITION GROUP	创建任何分区组
DROP ANY PARTITION GROUP	丢弃任何分区组
USAGE PARTITION GROUP	使用分区组
USAGE ANY PARTITION GROUP	使用任何分区组

创建角色

/*
ORDINARY: 自定义的角色名称 
*/
create role ORDINARY;

/*
将ORDINARY角色归属于 PUBLIC、RESOURCE、VTI角色下
*/
grant "PUBLIC","RESOURCE","VTI" to ORDINARY;

/*
授予ORDINARY角色权限,详细请查看权限说明表
*/
grant 
CREATE SCHEMA,
CREATE TABLE,
CREATE VIEW,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE INDEX,
CREATE CONTEXT INDEX,
BACKUP DATABASE,
CREATE LINK,
CREATE REPLICATE,
CREATE PACKAGE,
CREATE SYNONYM,
CREATE PUBLIC SYNONYM,
ALTER REPLICATE,
INSERT TABLE,
UPDATE TABLE,
DELETE TABLE,
SELECT TABLE,
GRANT TABLE,
INSERT VIEW,
UPDATE VIEW,
DELETE VIEW,
SELECT VIEW,
GRANT VIEW,
SELECT SEQUENCE,
EXECUTE PACKAGE,
GRANT PACKAGE,
CREATE PROFILE,
ALTER PROFILE 
to ORDINARY;

提示：

1. 安全严格管控的生产系统推荐授予以下角色

"PUBLIC","VTI","SOI"

2. 若需要放开create权限，则推荐授予以下角色

 "RESOURCE","PUBLIC","VTI","SOI"

创建用户

/*
说明：
MY：自定义的用户名
123456：密码
password_policy 0：密码策略
not_allow_ip：不允许访问的ip
allow_ip ：允许访问的ip
allow_datetime :允许访问的时间段
default tablespace MYTABLESPACE ：默认使用的表空间
default index tablespace MYTABLESPACE: 默认使用的索引表空间 
*/
create user MY identified by "123456" password_policy 0
-- allow_ip "192.168.0.188"
-- allow_datetime "2023-01-05" "16:12:51" to "2023-01-31" "16:12:51"
default tablespace MYTABLESPACE 
default index tablespace MYTABLESPACE ;

/*
授予用户角色
*/
grant "PUBLIC","SOI","ORDINARY","VTI" to MY;

/*授予用户权限，具体权限参考权限说明*/
grant UPDATE TABLE,SELECT TABLE,GRANT TABLE to MY;

创建模式

/*
说明：
MYSCHEMA  ：自定义的模式名称
MY：模式授权给指定的用户
*/
CREATE SCHEMA MYSCHEMA  AUTHORIZATION MY;

创建表空间

/*
说明：
MYTABLESPACE : 自定义的表空间名称
'G:\DM8\install_new\data\MYTABLESPACE\test.DBF' ：表空间数据文件地址
128：数据文件大小
autoextend ：默认自动扩充
67108863 ：数据文件扩充上线值
NORMAL: 普通缓冲区
*/
create tablespace MYTABLESPACE
datafile 'G:\DM8\install_new\data\TEST\test.DBF' 
size 128 
autoextend on 
maxsize 67108863 
CACHE = NORMAL;

创建序列

/*
说明：
MYTABLESPACE.SEQ_MYTABLESPACE: 模式名称.序列名称
INCREMENT BY 1 ：自增1
START WITH 1：从1开始
MAXVALUE：最大值
MINVALUE：最小值
NOCACHE：不缓存
NOORDER：非顺序
*/
CREATE SEQUENCE MYSCHEMA.SEQ_MYTABLESPACE
INCREMENT BY 1
START WITH 1
MAXVALUE 9223372036854775807
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

创建表

/*
说明：
MYSCHEMA.T_USER: 模式名称.表名
MYTABLESPACE：表空间名
*/
-- 若存在相同名称的表则先删除后再创建
DROP TABLE IF EXISTS MYSCHEMA.T_USER;
CREATE TABLE MYSCHEMA.T_USER
(
id INTEGER NOT NULL,
name VARCHAR2(255),
phone VARCHAR2(50),
idCard VARCHAR2(50),
NOT CLUSTER PRIMARY KEY(id ),
UNIQUE(phone),
UNIQUE(idCard)) STORAGE(ON MYTABLESPACE, CLUSTERBTR) ;

COMMENT ON TABLE MYSCHEMA.T_USER IS '用户表';
COMMENT ON COLUMN MYSCHEMA.T_USER.id IS 'id主键';
COMMENT ON COLUMN MYSCHEMA.T_USER.name IS '姓名';
COMMENT ON COLUMN MYSCHEMA.T_USER.phone IS '手机号';
COMMENT ON COLUMN MYSCHEMA.T_USER.idCard IS '身份证号';

向已有的表添加索引

创建唯一索引

/* 
 说明：
 	unique ：索引类型
	MYSCHEMA.uq_name_phone_idcard：模式名.索引名称
	MYSCHEMA.T_USER : 模式名.表名
	name,phone,idCard:分别为表中的字段名称
	initial 1： 初始分配簇大小
	next 1：下次分配簇大小
	minextents：最小保留簇大小
*/
create unique  index MYSCHEMA.uq_name_phone_idcard on MYSCHEMA.T_USER(name,phone,idCard) storage(initial 1,next 1,minextents 1);

例子

给多个字段创建组合索引，并将索引存到指定的索引表空间中

# 格式：create index 索引名称 on 模式名.表名（字段名,...） tablespace 索引表空间名称;
create index idx_code_name_rId_rTable on MYSCHEMA.T_USER (code,name,rid,rtable) tablespace TS_ATTACHMENT_IDX;

创建空间索引

/* 
 说明：
	spatial：索引类型
	MYSCHEMA.uq_name_phone_idcard：模式名.索引名称
	MYSCHEMA.T_USER : 模式名.表名
	name,phone,idCard:分别为表中的字段名称
	initial 1： 初始分配簇大小
	next 1：下次分配簇大小
	minextents：最小保留簇大小
*/
create spatial  index MYSCHEMA.uq_name_phone_idcard on MYSCHEMA.T_USER(name,phone,idCard) storage(initial 1,next 1,minextents 1);

创建数组索引

/* 
 说明：
	array：索引类型
	MYSCHEMA.uq_name_phone_idcard：模式名.索引名称
	MYSCHEMA.T_USER : 模式名.表名
	name,phone,idCard:分别为表中的字段名称
	initial 1： 初始分配簇大小
	next 1：下次分配簇大小
	minextents：最小保留簇大小
*/
create array index MYSCHEMA.uq_name_phone_idcard on MYSCHEMA.T_USER(name,phone,idCard) storage(initial 1,next 1,minextents 1);

向已有的表添加新列

/*
说明:
MYSCHEMA.T_USER : 模式名.表名
sex:新的列名
*/
-- 向 MYSCHEMA.T_USER 表中添加新列sex	
alter table MYSCHEMA.T_USER add column(sex number(1) default (1) not null );
-- 给新列添加备注
comment on column MYSCHEMA.T_USER.sex is '性别 1男 0女';

插入数据到表中

/*
说明:
MYSCHEMA.T_USER : 模式名.表名
MYSCHEMA.SEQ_MYTABLESPACE.NEXTVAL:序列。格式：模式名.序列名.NEXTVAL
*/
insert into MYSCHEMA.T_USER (id,name,phone,idCard) 
values(MYSCHEMA.SEQ_MYTABLESPACE.NEXTVAL,'张三','15578711016','4507211196612266390');

查询

查询当前数据库实例信息

select * from v$instance;

查询数据库集群信息

select * from v$dm_arch_ini;

查询表数据

/*
说明：
MYSCHEMA.T_USER： 模式名.表名
*/
select * from MYSCHEMA.T_USER

查询所有定时任务的信息

SELECT * FROM sysjob.sysjobs;

查询sql执行计划

方式1

以树形的方式展示执行计划

explain select * from MYSCHEMA.T_USER

方式2

以结果集的方式展示执行计划

explain for select * from MYSCHEMA.T_USER

执行计划说明：

1. CSCN ：基础全表扫描，从头到尾，全部扫描
2. SSCN ：二级索引扫描, 从头到尾，全部扫描
3. SSEK ：二级索引范围扫描 ，通过键值精准定位到范围或者单值
4. CSEK ：聚簇索引范围扫描 ，通过键值精准定位到范围或者单值
5. BLKUP ：根据二级索引的ROWID 回原表中取出全部数据(b + a)
6. SSEK2：二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID等信息去扫描表；
7. CSEK2：聚集索引扫描只需要扫描索引，不需要扫描表；
8. SSCN：索引全扫描，不需要扫描表。

如下图

执行顺序记住口诀：最右最上先执行。即缩进最多的最先执行，同级缩进的最上最先执行。

查询达梦数据库内存总量

 select
(select sum(n_pages) * page()/1024/1024 from v$bufferpool)||'MB' as 系统缓冲区大小BUFFER_SIZE,
(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as 共享内存池大小mem_pool,
(select sum(n_pages) * page()/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as 内存总量TOTAL_SIZE
from  dual;

查询sql语句占用内存情况

SELECT "SESSID", MAX_MEM_USED||'KB',SQL_TXT FROM V$SQL_STAT order by MAX_MEM_USED DESC;

查询内存池使用信息

方式一

select  
name as 内存池名称,                      --内存池名称
is_shared as 是否是共享,                   --是否是共享的
is_overflow as 是否用到了备份池,                  --是否用到了备份池
org_size/1024.0/1024.0 || 'M' as 内存池初始大小,        --内存池初始大小
TOTAL_size/1024.0/1024.0 || 'M' as 内存池总大小_包括扩展的,     --内存池总大小(包括扩展的)
RESERVED_SIZE/1024.0/1024.0 || 'M' as 内存池总大_小包括扩展的,  --当前已分配大小(包括扩展的)
DATA_SIZE/1024.0/1024.0 || 'M' as 实际有效字节,      --实际有效字节
EXTEND_SIZE || 'M' as 实际有效字节,                --每次扩展多少
TARGET_SIZE || 'M' as 目标大小,                --目标大小
N_EXTEND_NORMAL as  TARGET范围内累计扩展次数,         --TARGET范围内累计扩展次数
N_EXTEND_EXCLUSIVE  as 超过TARGET累计扩展次数        --超过TARGET累计扩展次数
from  v$mem_pool
order by  TOTAL_size desc;

方式二

select  (
	CASE name 
	WHEN 'memory pool size in bytes' THEN '内存池总的大小' 
	WHEN 'memory used bytes' THEN '内存池使用的内存大小'
	WHEN 'memory used bytes from os' THEN '内存池从操作系统分配的大小'
	END CASE 
	) ,stat_val/1024.0/1024.0 from  v$sysstat where CLASSID=11 ; 

查询会话的内存使用量

SELECT
        A.CREATOR  ,
        B.SQL_TEXT ,
        SUM(A.TOTAL_SIZE)/1024.0/1024.0 || 'M' 当前总量_包括扩展,    --当前总量(包括扩展)
        SUM(A.DATA_SIZE) /1024.0/1024.0 || 'M' 实际使用量  --实际使用量
FROM
        V$MEM_POOL A,
        V$SESSIONS B
WHERE
        A.CREATOR = B.THRD_ID
GROUP BY
        A.CREATOR,
        B.SQL_TEXT
ORDER BY
        当前总量_包括扩展 DESC;

查看表空间使用情况

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
        2 desc;

查询指定表中的字段数

语法

select count(*) as 字段总数 from all_tab_columns where table_name='表名称' AND OWNER='模式名称';

例子

select count(*) as 字段总数 from all_tab_columns where table_name='T_USER AND OWNER='MYSCHEMA';

扩展

oracle 的查询表字段数

SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '表名称';

达梦DES加密解密

加密

DECLARE
BEGIN
--加密
-- 450111111111016019 明文
-- 1122334455667788 密钥key
select RAWTOHEX(CFALGORITHMSENCRYPT(
utl_i18n.string_to_raw('450111111111016019', 'UTF8'),
129,
utl_i18n.string_to_raw('1122334455667788', 'UTF8')));
END;
/

解密

DECLARE
BEGIN
--解密
-- 463845343638333934463231433533424434463332373839324645 密文
-- 1122334455667788 密钥key
select UTL_I18N.RAW_TO_CHAR(CFALGORITHMSDECRYPT(
utl_i18n.raw_to_char(hextoraw('463845343638333934463231433533424434463332373839324645'),'UTF8'),
129,
utl_i18n.string_to_raw('1122334455667788', 'UTF8')),'UTF8');
END;
/

案例

declare
	encrypted_string varchar2(100);
	key_string varchar2(100);
	input_string varchar2(500);
	res_string varchar2(100);
	encrypted_data varbinary(100);
	keys varbinary(100);
	input varbinary(100);
	res_data varchar2(500);
begin
	input_string :='450111111111016019';-- 被加密的字符
	key_string := '1122334455667788'; -- 加密的key
	
	input_string := hextoraw(rpad(rawtohex(utl_raw.cast_to_raw(input_string)), 160,'F'));
	-- input_string := rawtohex(utl_raw.cast_to_raw(input_string));
	
	-- 加密  encrypted_string 明文加密后得到密文
	DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT(input_string =>input_string,key_string =>key_string,encrypted_string =>encrypted_string);
	
	-- 解密     res_data解密得到的明文
	DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT(encrypted_string,key_string,res_data);
	
	-- 查询出明文和密文
	select 
		lower(rawtohex(encrypted_string)) 密文 ,
		utl_raw.CAST_TO_VARCHAR2(hextoraw(trim('F' from hextoraw(res_data))))  as 明文 
	from dual; 
	
end;

达梦SM4国密加解密

语法

-- 加密语法：
-- 参数：
-- 1. 明文
-- 2. 加密方法id，select * from V$CIPHERS 查询加密方法id； 5202：OPENSSL_SM4_CBC
-- 3. 密钥
CFALGORITHMSENCRYPT(明文, 加密方法id, 密钥)

-- 解密语法：
-- 参数：
-- 1. 密文
-- 2. 加密方法id，select * from V$CIPHERS  5202：OPENSSL_SM4_CBC
-- 3. 密钥
CFALGORITHMSDECRYPT(密文, 加密方法id, 密钥)

例子

-- 加密
select CFALGORITHMSENCRYPT('1232234512', 5202, 'aabbccddeeffgg12') from dual;

-- 解密
select 
	CFALGORITHMSENCRYPT(
		CFALGORITHMSENCRYPT('1232234512', 5202, 'aabbccddeeffgg12'), 5202, 'aabbccddeeffgg12') 
from dual;

锁超时处理

查询被锁对象、会话信息、数据库对象

select 
 v_lock.*, -- 锁对象
 v_obj.NAME, -- 
 v_session.SESS_ID, -- 会话id
 v_session.SQL_TEXT, -- sql语句
 v_session.STATE, -- 状态
 v_session.USER_NAME,--  用户名
 v_session.CURR_SCH, 
 v_session.CLNT_TYPE, -- 连接类型
 v_session.CLNT_HOST, -- 客户端名称
 v_session.CLNT_IP, -- 客户端ip
 v_session.OSNAME  -- 操作系统名称
from v$lock v_lock
left join sysobjects v_obj on v_obj.ID = v_lock.TABLE_ID
left join v$sessions v_session on v_lock.TRX_ID = v_session.TRX_ID;

查询被锁对象

select * from v$lock;

或者

SELECT
        DS.SESS_ID "被阻塞的会话ID",
        DS.SQL_TEXT "被阻塞的SQL",
        DS.TRX_ID "被阻塞的事务ID",
        (CASE L.LTYPE WHEN 'OBJECT' THEN '对象锁' WHEN 'TID' THEN '事务锁' END CASE ) "被阻塞的锁类型",
        DS.CREATE_TIME "开始阻塞时间",
        SS.SESS_ID "占用锁的会话ID",
        SS.SQL_TEXT "占用锁的SQL",
        SS.CLNT_IP "占用锁的IP",
        L.TID "占用锁的事务ID"
FROM
        V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
        DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
        SS.TRX_ID = L.TID
WHERE
        L.BLOCKED = 1

查询系统对象

select * from sysobjects;

查询所有会话进程信息

select * from v$sessions ;

关闭被锁的进程

-- 140235649561712 为查询所有会话进程信息得到的SESS_ID值
sp_close_session('140235649561712');

查询执行时间过长的sql

使用以下sql语句可以查询执行时间过长的记录

select * from V$SYSTEM_LONG_EXEC_SQLS;

或者

select * from V$LONG_EXEC_SQLS;

使用disql命令行窗口执行sql文件

当我们有一个sql文件名称为：test.sql，路径为：/home/test.sql
使用命令行执行sql文件。注意，语句之后不需要带分号结束。

-- 语法
-- start 文件路径
start /home/test.sql

函数

创建函数

格式

CREATE OR REPLACE FUNCTION 模式名称.函数名称 (参数1 数据类型, 参数2 数据类型, ....) return 返回值类型 is
	-- 变量定于区
	resultValue VARCHAR2(4000); -- 返回结果变量，变量名和类型自定义。必须声明。
begin
	-- 判定条件抛出异常
	IF 判定条件 THEN
      RAISE_APPLICATION_ERROR(返回的错误编码数值, 错误信息字符);
    END IF;
    
	begin
		-- 函数逻辑区
		-- todo
		
	-- 异常捕获区
	EXCEPTION
	WHEN OTHERS THEN
		RAISE; -- 异常抛出
	end;
	
	RETURN resultValue;  -- 返回结果
end;

例子

创建一个加法运算的函数，两个整数相加返回结果为字符串，返回格式为：参数1+参数2=结果

CREATE OR REPLACE FUNCTION MYSCHEMA.f_sum (param1 int, param2 int) return varchar2(4000) is
	-- 变量定于区
	resultValue VARCHAR2(4000); -- 返回结果变量，变量名和类型自定义。必须声明。
begin
	-- 判定条件抛出异常
	IF param1 is null THEN
      RAISE_APPLICATION_ERROR(-20001, '参数1不能为空');
    END IF;
    IF param2 is null THEN
      RAISE_APPLICATION_ERROR(-20001, '参数2不能为空');
    END IF;
    
	begin
		-- 函数逻辑区
		resultValue := param1 || '+' || param2 || '=' || (param1+param2);
		
	-- 异常捕获区
	EXCEPTION
	WHEN OTHERS THEN
		RAISE; -- 异常抛出
	end;
	
	RETURN resultValue;  -- 返回结果
end;

调用

	select MYSCHEMA.f_sum (1,2) 结果 from dual; 

存储过程

创建存储过程

格式

使用以下格式的来创建存储过程

CREATE OR REPLACE PROCEDURE  模式名称.存储过程名称
AUTHID DEFINER
is
-- 定义变量区域
begin
	-- 存储过程逻辑体
	
	-- 捕获存储过程逻辑体中出现的异常
	exception when others THEN
		rollback; --把前面的全部回滚
end;

例子

CREATE OR REPLACE PROCEDURE  MYSCHEMA.p_mytest
AUTHID DEFINER
is
-- 定义变量区域
begin
	-- 存储过程逻辑体
	
	-- 捕获存储过程逻辑体中出现的异常
	exception when others THEN
		rollback; --把前面的全部回滚
end;

记录存储过程异常日志

主要思路是创建一个存放存储过程异常日志的数据表，在存储过程捕获异常体中将异常存储到该表中。

创建存储异常信息的数据表

DROP TABLE IF EXISTS MYSCHEMA.T_PROCEDURE_ERROR_LOG;
CREATE TABLE MYSCHEMA.T_PROCEDURE_ERROR_LOG
(
id INTEGER NOT NULL,
name VARCHAR2(255),
error_time timestamp,
msg VARCHAR2(500),
NOT CLUSTER PRIMARY KEY(id ))) STORAGE(ON MYTABLESPACE, CLUSTERBTR) ;

COMMENT ON TABLE MYSCHEMA.T_PROCEDURE_ERROR_LOG IS '存储异常信息记录表';
COMMENT ON COLUMN MYSCHEMA.T_USER.id IS 'id主键';
COMMENT ON COLUMN MYSCHEMA.T_USER.name IS '存储过程名称';
COMMENT ON COLUMN MYSCHEMA.T_USER.error_time  IS '出现异常的时间';
COMMENT ON COLUMN MYSCHEMA.T_USER.msg IS '异常提示';

格式

CREATE OR REPLACE PROCEDURE  MYSCHEMA.p_mytest
AUTHID DEFINER
is
-- 定义变量区域
v_error_message varchar2(4000); -- 声明存放异常消息的变量
begin
	-- 存储过程逻辑体
	
	-- 捕获存储过程逻辑体中出现的异常
	exception when others THEN
		rollback; --把前面的全部回滚
		-- 将异常数据存入表中
		v_errmessage := sqlerrm; -- 获取异常提示消息
		insert into 异常日志表 values(id,存储过程名称,sysdate,v_errmessage );
		commit;
end;

例子

CREATE OR REPLACE PROCEDURE  MYSCHEMA.p_mytest
AUTHID DEFINER
is
-- 定义变量区域
v_error_message varchar2(4000); -- 声明存放异常消息的变量
begin
	-- 存储过程逻辑体
	
	-- 捕获存储过程逻辑体中出现的异常
	exception when others THEN
		rollback; --把前面的全部回滚
		-- 将异常数据存入表中
		v_errmessage := sqlerrm; -- 获取异常提示消息
		insert into MYSCHEMA.T_PROCEDURE_ERROR_LOG values(1,'MYSCHEMA.p_mytest',sysdate,v_errmessage );
		commit;
end;

时间处理

时间加减

语法

# 时间往前加
# 时间单位：MINUTE、YEAR、DAY 等
时间字段 + INTERVAL '数值' 时间单位

# 时间往前减
# 时间单位：MINUTE、YEAR、DAY 等
时间字段 - INTERVAL '数值' 时间单位

例子

将查询到的时间值往前加2分钟

SELECT CREATE_TIME + INTERVAL '2' MINUTE AS new_time,CREATE_TIME from zhang.t_user;

字符处理

分割字符

将字符串按照指定分隔符号分割

语法

-- 格式： 
-- 		str：待分割的字符
--      reg: 正则表达式。  '[^ ]+' : 按空格分隔  ;   '[^,]+' :按逗号分隔  '[^?]+'：按问号分隔
-- 		startIndex： 开始截取的字符位置,从1开始
--		resultIndex： 分隔之后取的结果位置，从1开始。（可以理解成java中split分隔后数组的第几个元素，只是位置是从1开始）
REGEXP_SUBSTR(str , reg , startIndex, resultIndex)

例子

将时间 2023-06-01 12:55:59 按照空格分隔成两部分，分别为年月日、时分秒

select REGEXP_SUBSTR('2023-06-01 12:55:59' ,'[^ ]+',1,1) ymd, REGEXP_SUBSTR('2023-06-01 12:55:59' ,'[^ ]+',1,2) hms

结果

使用正则表达式替换字符

使用正则匹配规则，将字符串中指定的内容替换成新内容

语法

-- 格式：
-- str：原字符串
-- reg：正则表达式
-- newStr： 新字符
REGEXP_REPLACE(str,reg,newStr) 

例子

将字符串中的日期去除（替换成空白内容）

select REGEXP_REPLACE('abc/2023-11-29/def','([0-9]{4}-[0-9]{2}-[0-9]{2})','') 

结果

字符拆分成多行记录

语法

SELECT SUBSTR(要拆分的字符, (拆分的字符长度 * LEVEL) - 0, 拆分的字符长度) AS sub_string FROM DUAL CONNECT BY LEVEL <= LENGTH(要拆分的字符) / 拆分的字符长度;

例子

将字符串1222拆分成记录，每个数字为一条记录。

SELECT SUBSTR('12345', (1 * LEVEL) - 0, 1) AS sub_string FROM DUAL CONNECT BY LEVEL <= LENGTH('12345') / 1;

结果

随机数

生成随机数

语法

 -- 随机生成指定范围的数字。[起始数，终点数]
 -- fromNumber： 起始数
 -- toNumber：终点数
 round(dbms_random.value(fromNumber,toNumber))

例子

-- 随机生成0-10之间的整数
select round(dbms_random.value(0,10))

结果

从查询的结果中随机选取数据

语法

-- tableName: 查询的数据表名
-- number: 随机选取的记录条数
select * from tableName order by rand() limit number;

例子

-- 从结果中随机选取5条记录
select * from t_test order by rand() limit 5;

结果

随机生成指定范围内的时间

语法

-- startNumber: 随机范围起始
-- endNumber: 随机范围截止
TRUNC(sysdate-round(dbms_random.value(startNumber,endNumber))) + INTERVAL '1' SECOND * ROUND(DBMS_RANDOM.VALUE(0, 86399))

例子

-- 随机生成10天前到今天的时间，年月日时分秒都随机，生成五个记录
select TRUNC(sysdate-round(dbms_random.value(0,10))) + INTERVAL '1' SECOND * ROUND(DBMS_RANDOM.VALUE(0, 86399))

结果

连接限制

IP也可以使用通配符的方式，例如192.168.0.*

设置ip白名单

语法：

alter user "用户名" allow_ip "第一个IP","第二个IP";

例子

alter user "SYSDBA" allow_ip "127.0.0.1","192.168.0.1";

设置ip黑名单

语法：

alter user "用户名" not_allow_ip "第一个IP","第二个IP";

例子

alter user "SYSDBA" not_allow_ip "127.0.0.1","192.168.0.1";

清除白名单IP限制

语法：

alter user  "用户名"  allow_ip null;

例子

alter user  "SYSDBA"  allow_ip null;

清除黑名单IP限制

语法：

alter user  "用户名"  not_allow_ip null;

例子

alter user  "SYSDBA"  not_allow_ip null;

修改序列值为指定表的最大值

创建以下存储过程，用于执行查询指定表中最大id值，并将此id值赋给序列。

create or replace procedure update_seq(v_table_name in varchar2, v_seq_name in varchar2,v_dmName in varchar2,v_idFileName in varchar2) is
  -- id最大值
  v_max int;
  -- 序列当前值
  v_seq_val int;
  -- 查询id最大值的sql字符串
  selectIdMaxStr VARCHAR2 (500);
  -- 查询seq下一个值的sql字符串
  selectSeqValStr VARCHAR2 (500) ;
begin
  selectIdMaxStr := 'select max('||v_idFileName||') from ' || v_dmName ||'.' ||v_table_name;
  selectSeqValStr :='select ' ||v_dmName ||'.' ||v_seq_name || '.NEXTVAL from dual' ;
  
  EXECUTE IMMEDIATE selectIdMaxStr into v_max;
  -- 未修改前的序列下一个值
  EXECUTE IMMEDIATE selectSeqValStr into v_seq_val;
  EXECUTE IMMEDIATE 'alter sequence '||v_dmName ||'.'|| v_seq_name ||' increment BY '|| (v_max - v_seq_val);
  -- 修改后的的序列取下一个值
  EXECUTE IMMEDIATE selectSeqValStr into v_seq_val;
  -- 修改为原来的1
  EXECUTE IMMEDIATE 'alter sequence '||v_dmName ||'.'|| v_seq_name ||' increment BY 1';
end update_seq;

使用方法

-- 说明：
-- tableName： 表名称
-- seqName：序列名称
-- dmName：模式名称
-- v_idFileName：id字段名称
call update_seq('tableName','seqName','dmName','v_idFileName');

例子

call QWGL.update_seq('T_USER','SEQ_T_USER_ID','USERDB','ID' )

修改用户登录密码

-- alter user 账号 identified by "密码"
alter user zhang identified by "zhang@12456";

登录会话设置

用户会话登录设置

修改用户zhang的会话空闲时间为30分钟，尝试登陆次数为5次，密码有效期为90天，连续登陆失败后锁定10分钟，会话持续时间10分钟。

alter user zhang limit  connect_idle_time 30, failed_login_attemps 5, password_life_time 90, password_lock_time 10, connect_time 10;

取消对用户zhang的所有会话限制。

alter user zhang limit CONNECT_IDLE_TIME UNLIMITED CONNECT_TIME UNLIMITED FAILED_LOGIN_ATTEMPS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED;

逻辑备份

对数据库逻辑组件（如表、视图和存储过程等数据库对象）的备份还原。
逻辑导入导出使用的脚本为dimp 和dexp，再安装目录的bin下。
所以，先进入安装目录的bin目录下再进行操作。

导出

数据库级备份（FULL）

导出或导入整个数据库中的所有对象。

# 格式：
# ./dexp USERID=登录的账号/密码 FILE=导出的逻辑结构文件名.dmp LOG=导出过程的日志文件.log  FULL=Y  DIRECTORY=导出文件存放的文件夹路径
# 注意：密码中有特殊字符的需要进行转义
./dexp USERID=SYSDBA/'"root@123"' FILE=db20231223.dmp LOG=db20231223.log FULL=Y DIRECTORY=/home

用户级备份 （OWNER）

导出或导入一个或多个用户所拥有的所有对象。

模式级备份（SCHEMAS）

导出或导入一个或多个模式下的所有对象。

表级备份（TABLES）

导出或导入一个或多个指定的表或表分区。

导入

数据库级备份导入（FULL）

# 格式：
# ./dimp 账号/密码 file=逻辑备份文件路径 log=逻辑备份日志路径 full=是否全库 TABLE_EXISTS_ACTION=表存在时操作
./dimp USERID=SYSDBA/SYSDBA FILE=/home/db_str.dmp DIRECTORY=/home LOG=/home/im_db_str.log FULL=Y

用户级备份导入 （OWNER）

模式级备份（SCHEMAS）

表级备份（TABLES）

联机备份与恢复

联机备份是指在不关闭数据库的情况下进行备份的方式，一般生产环境下使用此方式对数据库进行备份

备份

库级别

在库级别联机备份中，只允许备份，不允许恢复。若要还原恢复库级别的联机备份记录，则只能在脱机的情况下进行还原，即关闭停止数据库后再进行库级别的备份恢复。

方式1：手动备份

使用sql命令行窗口或者使用disql执行以下的sql语句。

联机全量备份

语法

-- 说明：
-- full: 表示全量备份，可是省略不写
-- path: 全量备份存放的文件夹路径。文件夹不存在时会自动创建，已存在则报错无法备份。
backup database full backupset 'path';

例子

联机全库备份，备份存放路径为：/extend1/dmBackUp/onlineBackUp/onlineFullBackUp20231221

backup database full backupset '/extend1/dmBackUp/onlineBackUp/onlineFullBackUp20231221';

联机增量备份

语法

-- 说明：
-- increment: 表示增量备份，不可省略
-- basePath: 基准备份所在的路径，一般为全量备份所在路径
-- path: 增量备份存放文件夹路径，文件夹不存在时会自动创建，已存在则报错无法备份。
backup database increment with backupdir 'basePath' backupset 'path';

例子

联机增量备份
基准备份所在的路径为：/extend1/dmBackUp/onlineBackUp/
备份存放路径为：/extend1/dmBackUp/onlineBackUp/onlineIncrementBackUp20231221

backup database increment with backupdir '/extend1/dmBackUp/onlineBackUp/' backupset '/extend1/dmBackUp/onlineBackUp/onlineIncrementBackUp20231221';

方式2：数据库定时任务

使用sql命令行窗口或者使用disql执行以下的sql语句。
推荐使用达梦可视化界面进行定时任务的设计。

联机全量备份

语法

-- 说明
-- 推荐使用达梦可视化界面进行定时任务的设计。此处涉及到任务调度，使用可视化界面更加直观。
-- 
call SP_CREATE_JOB('自定义定时任务名称',1,0,'',0,0,'',0,'全库联机备份每个星期六23:00:00秒执行一次全库联机备份，备份存放到 /extend1/dmBackUp/onlineBackUp/onlineFullBackUp 下,定时任务启动一分钟后自动进行备份一次');
call SP_JOB_CONFIG_START('自定义定时任务名称');
call SP_ADD_JOB_STEP('自定义定时任务名称', '自定义定时任务的步骤名称', 6, '00000000/extend1/dmBackUp/onlineBackUp/onlineFullBackUp', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('自定义定时任务名称', '自定义定时任务的步骤名称', 1, 2, 1, 64, 0, '23:00:00', NULL, '第一次执行的时间', NULL, '定时任务启动一分钟后自动进行备份一次');
call SP_ADD_JOB_SCHEDULE('自定义定时任务名称', '自定义定时任务的步骤名称', 1, 0, 0, 0, 0, NULL, NULL, sysdate+1/1440, NULL, '');
call SP_JOB_CONFIG_COMMIT('自定义定时任务名称');

例子

定时任务每个星期六23:00:00秒执行一次全库联机备份，备份存放到 /extend1/dmBackUp/onlineBackUp/onlineFullBackUp 下。

call SP_CREATE_JOB('job_db_bakfull',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('job_db_bakfull');
call SP_ADD_JOB_STEP('job_db_bakfull', 'bak01', 6, '00000000/extend1/dmBackUp/onlineBackUp/onlineFullBackUp', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('job_db_bakfull', 'std1', 1, 2, 1, 64, 0, '23:00:00', NULL, '2022-12-29 10:48:00', NULL, '');
call SP_ADD_JOB_SCHEDULE('job_db_bakfull', 'once1', 1, 0, 0, 0, 0, NULL, NULL, sysdate+1/1440, NULL, '');
call SP_JOB_CONFIG_COMMIT('job_db_bakfull');

联机增量备份

语法

-- 说明
-- 推荐使用达梦可视化界面进行定时任务的设计。此处涉及到任务调度，使用可视化界面更加直观。
call SP_CREATE_JOB('自定义定时任务名称',1,0,'',0,0,'',0,'增量联机备份');
call SP_JOB_CONFIG_START('自定义定时任务名称');
call SP_ADD_JOB_STEP('自定义定时任务名称', '自定义定时任务的步骤名称', 0, '定时任务执行逻辑', 3, 3, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('自定义定时任务名称', '自定义定时任务的步骤名称', 1, 1, 1, 0, 0, '23:59:59', NULL, '2023-12-21 15:21:34', NULL, '每天23:59:59执行一次全库增量备份');
call SP_JOB_CONFIG_COMMIT('自定义定时任务名称');

例子

定时任务每天23:00:00秒执行一次增量联机备份，基准备份所在路径为： /extend1/dmBackUp/onlineBackUp/jobBakup 下。
备份存放在**/extend1/dmBackUp/onlineBackUp/jobBakup** 下

call SP_CREATE_JOB('job_db_bakincr',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('job_db_bakincr');
call SP_ADD_JOB_STEP('job_db_bakincr', 'bak2', 6, '40000000/extend1/dmBackUp/onlineBackUp/jobBakup|/extend1/dmBackUp/onlineBackUp/jobBakup', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('job_db_bakincr', 'std2', 1, 2, 1, 63, 0, '23:00:00', NULL, '2022-12-29 10:48:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('job_db_bakincr');

备份定时删除

语法

call SP_CREATE_JOB('自定义定时任务名称',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('自定义定时任务名称');
call SP_ADD_JOB_STEP('自定义定时任务名称','自定义定时任务的步骤名称',0, 'SF_BAKSET_BACKUP_DIR_ADD(''DISK'',''备份存放路径'');
call sp_db_bakset_remove_batch(''DISK'',now()-14);', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('自定义定时任务名称', '自定义定时任务的步骤名称', 1, 1, 1, 0, 0, '23:30:00', NULL, '2022-12-29 10:48:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('自定义定时任务名称');

例子

每天 23：30 删除 14 天前存放在 /extend1/dmBackUp/onlineBackUp/jobBakup 的备份

call SP_CREATE_JOB('job_db_delbak',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('job_db_delbak');
call SP_ADD_JOB_STEP('job_db_delbak','bak1',0, 'SF_BAKSET_BACKUP_DIR_ADD(''DISK'',''/extend1/dmBackUp/onlineBackUp/jobBakup'');
call sp_db_bakset_remove_batch(''DISK'',now()-14);', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('job_db_delbak', 'del01', 1, 1, 1, 0, 0, '23:30:00', NULL, '2022-12-29 10:48:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('job_db_delbak');

方式3：linux系统shell脚本定时任务

联机全量备份

创建一个名为jobDbBackupOnlineFull.sh 的shell脚本文件，用于执行全量备份

#!/bin/bash
date_time=`date +"%Y%m%d%H%M"`
# /home/dmdba/install/bin/disql 为disql的所在路径
# SYSDBA/'"SYSDBA@123"'  表示登录的账户密码，因为密码有特殊字符所以使用 '""' 来包裹。如果没有特殊字符的可以按照一般情况写，例如：SYSDBA/SYSDBA
/home/dmdba/install/bin/disql SYSDBA/'"SYSDBA@123"' -E "backup database full backupset '/extend1/dmBackUp/onlineFullBackUp${date_time}';";

联机增量备份

创建一个名为jobDbBackupOnlineIncrement.sh 的shell脚本文件，用于执行增量备份

#!/bin/bash
date_time=`date +"%Y%m%d%H%M"`
# /home/dmdba/install/bin/disql 为disql的所在路径
# SYSDBA/'"SYSDBA@123"'  表示登录的账户密码，因为密码有特殊字符所以使用 '""' 来包裹。如果没有特殊字符的可以按照一般情况写，例如：SYSDBA/SYSDBA
/home/dmdba/install/bin/disql SYSDBA/'"SYSDBA@123"' -E "backup database increment with backupdir '/extend1/dmBackUp/onlineBackUp/' backupset '/extend1/dmBackUp/onlineBackUp/onlineIncrementBackUp${date_time}';"

将以上脚本配置再linux的定时任务中。
使用下命令编辑定时任务

crontab -e

以下是定时任务配置

# 命令时间格式 :
#	*     * 　  *　  *　  *　　command
#	分　  时　  日　  月　 周　  命令
#	第1列表示分钟1～59 每分钟用*或者 */1表示
#	第2列表示小时1～23（0表示0点）
#	第3列表示日期1～31
#	第4列表示月份1～12
#	第5列标识号星期0～6（0表示星期天）
#	第6列要运行的命令

# 格式：
# 时间表达式 命令脚本所在路径
# 每个星期天的23:59:00 秒执行全量备份脚本
59 23 * * 0 /jobDbBackupOnlineFull.sh

# 每天的23:59:00 秒执行增量备份脚本
59 23 * * * /jobDbBackupOnlineIncrement.sh

恢复

综合案例

案例1：

按照以下sql顺序创建相应的内容

1. 创建角色
2. 给角色授权
3. 创建表空间
4. 创建用户
5. 给用户分配角色
6. 创建模式
7. 创建序列
8. 创建表

/* 创建 角色 */
create role ORDINARY;

/*授予角色指定权限*/
grant 
CREATE TABLE,
CREATE VIEW,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE INDEX,
CREATE LINK,
BACKUP DATABASE,
CREATE CONTEXT INDEX,
CREATE PACKAGE,
CREATE REPLICATE,
CREATE SYNONYM,
CREATE PUBLIC SYNONYM,
INSERT TABLE,
UPDATE TABLE,
DELETE TABLE,
SELECT TABLE,
INSERT VIEW,
UPDATE VIEW,
DELETE VIEW,
SELECT VIEW,
EXECUTE PROCEDURE,
EXECUTE PACKAGE,
SELECT SEQUENCE,
CREATE MATERIALIZED VIEW,
SELECT MATERIALIZED VIEW,
CREATE DOMAIN,
USAGE DOMAIN,
DUMP TABLE,
CREATE PARTITION GROUP,
USAGE PARTITION GROUP
to ORDINARY;

/*创建表空间*/
create tablespace MYTABLESPACE
datafile 'G:\DM8\install_new\data\MYTABLESPACE\MYTABLESPACE.DBF' 
size 128 
autoextend on 
maxsize 67108863 
CACHE = NORMAL;


/*创建用户*/
create user MY identified by "123456" password_policy 0
default tablespace MYTABLESPACE 
default index tablespace MYTABLESPACE ;

/*给用户分配角色*/
grant "ORDINARY","VTI" to MY;

/*创建模式*/
CREATE SCHEMA MYSCHEMA  AUTHORIZATION MY;

/*创建序列*/
CREATE SEQUENCE MYSCHEMA.SEQ_MYTABLESPACE
INCREMENT BY 1
START WITH 1
MAXVALUE 9223372036854775807
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

/*创建表
若存在相同名称的表则先删除后创建
*/
DROP TABLE IF EXISTS MYSCHEMA.T_USER;
CREATE TABLE MYSCHEMA.T_USER
(
id INTEGER NOT NULL,
name VARCHAR2(255),
phone VARCHAR2(50),
idCard VARCHAR2(50),
NOT CLUSTER PRIMARY KEY(id ),
UNIQUE(phone),
UNIQUE(idCard)) STORAGE(ON MYTABLESPACE, CLUSTERBTR) ;

COMMENT ON TABLE MYSCHEMA.T_USER IS '用户表';
COMMENT ON COLUMN MYSCHEMA.T_USER.id IS 'id主键';
COMMENT ON COLUMN MYSCHEMA.T_USER.name IS '姓名';
COMMENT ON COLUMN MYSCHEMA.T_USER.phone IS '手机号';
COMMENT ON COLUMN MYSCHEMA.T_USER.idCard IS '身份证号';

案例2：

使用AES加解密的方式对数据进行加密、解密。

1. 数据库加密得到的密文要求在数据库、java代码中可以解密回明文
2. java代码加密得到的密文要求在数据库、java代码中可以解密回明文

判断当前数据库是否存在加解密包

使用以下sql语句查询

-- 查询系统是否存在加解密包、数据的版本号 
SELECT a.PROCEDURE_NAME 加解密包,t.id_code 版本号 FROM ALL_PROCEDURES a,(select id_code) t
WHERE a.OWNER = 'SYS'  AND a.OBJECT_NAME = 'DBMS_CRYPTO'

查询结果如下：
版本号大于20230417之后的一般情况下都有这个加解密包。

数据库加解密

使用以上的加密解密包进行加解密

加密 语法

select
	bintochar (
	utl_encode.base64_encode (
		dbms_crypto.encrypt(
			utl_i18n.string_to_raw ( '这里是需要加密的明文数据', 'UTF8' ),
			dbms_crypto.encrypt_aes128 + dbms_crypto.chain_ecb + dbms_crypto.pad_pkcs5,
			utl_i18n.string_to_raw ( '密匙', 'UTF8' ) 
		) 
	) 
) from dual;

解密 语法

select
	utl_i18n.raw_to_char (
		dbms_crypto.decrypt (
			utl_encode.base64_decode (
			chartobin ( '加密得到的密文')),
			dbms_crypto.encrypt_aes128 + dbms_crypto.chain_ecb + dbms_crypto.pad_pkcs5,
			utl_i18n.string_to_raw ( '密匙', 'UTF8' ) 
		),
	'UTF8' 
	) from dual ;

java代码工具类

import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import java.util.Base64;
import java.util.Objects;

/**
 * 说明：
 *	达梦数据库 AES 加解密工具类
 * @author 张小三
 * @create 2024-03-04 18:31
 * @verson 1.0.0
 */
public class DmAesUtil {
	/**
     * AES 加解密密钥，此处要改成自己自定义的密钥
     */
    private static final String key = "10Abc56780asfAC@";

	/**
     * 算法名称
     */
    private static final String methods = "AES";

	/**
     * 算法/模式/补码方式
     */
    private static final String padding = "AES/ECB/PKCS5Padding";

	/**
	* 字符类型
	*/
    private static final String charsetName = "utf-8";
	
	/**
     * 校验,
     */
    private static void check(String sSrc, String sKey) {
        if (Objects.isNull(sKey)) {
            throw new RuntimeException("密钥不能为空");
        }
        if (sKey.length() != 16) {
            throw new RuntimeException("密钥长度必须为16位");
        }
        if (Objects.isNull(sSrc) || sSrc.length() <= 0) {
            throw new RuntimeException("数据不能为空，加解密失败");
        }
    }
	
	/**
	* 获取密钥
	* @param key 密钥
	*/
	private  static SecretKeySpec createKey(String key){
		byte[] raw = key.getBytes(charsetName);
		return new SecretKeySpec(raw, methods);
	}
	
	/**
	* 加密
	* @param data 明文数据
	* @param key 密钥
	*/
	private static String encrypt(String data, String key) {
        try {
            check(data, key);
            SecretKeySpec skeySpec = createKey(key);
            Cipher cipher = Cipher.getInstance(padding);
            cipher.init(Cipher.ENCRYPT_MODE, skeySpec);
            byte[] encrypted = cipher.doFinal(data.getBytes(charsetName));
            return Base64.getEncoder().encodeToString(encrypted);
            // return new Base64().encodeToString(); 
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    
	/**
	* 解密
	* @param data 加密得到的密文
	* @param key 密钥
	*/
	private static String decrypt(String data, String key) {
        try {
            check(data, key);
            SecretKeySpec skeySpec = createKey(key);
            Cipher cipher = Cipher.getInstance(padding);
            cipher.init(Cipher.DECRYPT_MODE, skeySpec);
            /** 先用base64解密 */
            byte[] encrypted1 = Base64.getDecoder().decode(data);
            // byte[] encrypted1 = new Base64().decode(sSrc);
            byte[] original = cipher.doFinal(encrypted1);
            return  new String(original, charsetName);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
	
	/**
	* 加密
	* @param data 要加密的明文数据
	*/
	public static String encrypt(String data ) {
        return encrypt(data , key);
    }
	
	/**
	* 解密
	* @param data 密文
	*/
	public static String decrypt(String data ) {
        return decrypt(data, key);
    }
}

使用 以上的java代码和sql语法可以使数据库和java代码之间的加密结果一致，从而在java代码和数据库之中都能对密文进行解密。

数据库端封装成方法

当然，为了在数据库中方便使用这个加解密，减少sql语句的长度，可以将加解密封装成方法进行使用。如下：

CREATE OR REPLACE FUNCTION MYSCHEMA.f_AESUtil(data VARCHAR2, method INT) RETURN VARCHAR2 IS
  -- 参数：
  -- data：明文/密文数据
  -- method：1 为加密，其他为解密
  
  -- AES 密钥，要与java代码中的key一致
  keyValue VARCHAR2(100) := '10Abc56780asfAC@';
  
  -- 字符编码
  chartsetCode VARCHAR2(10) := 'UTF8';
  
  -- 结果
  resultValue VARCHAR2(4000);
BEGIN
  -- 根据特定条件抛出异常
    IF data IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001, decode(method,'1','加密','解密') || '时,参数data不能为空');
    END IF;
    IF method IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001, '参数method不能为空,可选值：1为加密,0为解密');
    END IF;
    
    
  -- AES 加解密逻辑
  IF method = 1 THEN
    -- 加密操作
    SELECT
      bintochar(
        utl_encode.base64_encode(
          dbms_crypto.encrypt(
            utl_i18n.string_to_raw(data, chartsetCode),
            dbms_crypto.encrypt_aes128 + dbms_crypto.chain_ecb + dbms_crypto.pad_pkcs5,
            utl_i18n.string_to_raw(keyValue, chartsetCode)
          )
        )
      ) INTO resultValue
    FROM dual;
  ELSE
   	-- 解密操作
    SELECT 
      utl_i18n.raw_to_char(
        dbms_crypto.decrypt(
          utl_encode.base64_decode(chartobin(data)),
          dbms_crypto.encrypt_aes128 + dbms_crypto.chain_ecb + dbms_crypto.pad_pkcs5,
          utl_i18n.string_to_raw(keyValue, chartsetCode)
        ),
        chartsetCode
      ) INTO resultValue
    FROM dual;
  END IF;

  RETURN resultValue;  -- 返回结果字符串
END;
/

调用方法：

-- 加密
select MYSCHEMA.f_AESUtil('123125452',1) 加密 from dual;
-- 解密 
select MYSCHEMA.f_AESUtil( MYSCHEMA.f_AESUtil('123125452',1) ,0) 解密 from dual;