达梦数据库业务场景处理

1. 锁表场景

关闭死锁状态下的sessions

SP_CLOSE_SESSION( 
	SELECT distinct sess_id 
	FROM V$sessions c left join v$lock l on l.trx_id = c.trx_id 
	WHERE 
	c.state = 'ACTIVE' 
     -- and c.name = 'table_name'  -- 某个表名称
	LIMIT 0,1
 );

SELECT *
FROM V$sessions c 
WHERE trx_id IN (SELECT trx_id FROM v$lock ) 
AND c.state = 'ACTIVE'

2.MERGE INTO 批量插入

能够解决主键冲突问题

MERGE INTO TABLE_NAME1 T1  
USING (
   SELECT field1,field2 FROM TABLE_NAME2 LIMIT 0,100
) T2 ON (T1.id = T2.id)  -- 要插入的数据集
WHEN NOT MATCHED THEN    -- 不重复时插入操作
INSERT('field','field') VALUES (T2.field1,field2)
WHEN MATCHED THEN UPDATE SET T1.createtime = '';  -- 重复时随便给一个字段设置一个值，相当于更新
COMMIT;

3.达梦数据库linux磁盘分区挂载

# 查看磁盘分区情况
fdisk -l

一、创建挂载点

mkdir /opt/dmdbms
mkdir /dmdata
mkdir /dmarch
mkdir /dmbak
mkdir /dmbak/dmcore

二、创建分区

pvcreate /dev/vdb 
vgcreate vg_dm /dev/vdb 
lvcreate   -n   vg_dmdbms   -L  200G   vg_dm
lvcreate   -n   vg_dmdata   -L  900G   vg_dm
lvcreate   -n   vg_dmarch   -L  300G   vg_dm
lvcreate   -n   vg_dmbak   -L   500G   vg_dm
lvcreate   -n   vg_dmcore   -l   100%FREE   vg_dm

三、格式化分区

mkfs.ext4 /dev/mapper/vg_dm-vg_dmdbms
mkfs.ext4 /dev/mapper/vg_dm-vg_dmdata 
mkfs.ext4 /dev/mapper/vg_dm-vg_dmarch
mkfs.ext4 /dev/mapper/vg_dm-vg_dmbak
mkfs.ext4 /dev/mapper/vg_dm-vg_dmcore

四、挂载

mount /dev/mapper/vg_dm-vg_dmdbms /opt/dmdbms
mount /dev/mapper/vg_dm-vg_dmdata /dmdata
mount /dev/mapper/vg_dm-vg_dmarch /dmarch
mount /dev/mapper/vg_dm-vg_dmbak /dmbak
mount /dev/mapper/vg_dm-vg_dmcore  /dmbak/dmcore

五、配置启动自动挂载

#  1.查看对应 id
blkid 
# 2. 修改重启自动挂载配置
vim /etc/fstab

UUID=b65a7a30-21b1-437f-b35d-ecb83f523f27 /dmdata         ext4     defaults        0 0
UUID=a8312bca-961d-4dbb-b8f4-279b078dd41a /dmarch         ext4     defaults        0 0
UUID=42bf3bd2-f4fe-4b2d-bf17-859d254dcade /dmbak          ext4     defaults        0 0
UUID=044b17e1-d172-4918-acea-9e526a80ae64 /dmbak/dmcore   ext4     defaults        0 0
UUID=57ba491a-d4a0-482c-83bf-dd10c28c5649 /opt/dmdbms     ext4     defaults        0 0

六、磁盘扩容、删减

 lvreduce -L 500G -f -r /dev/mapper/vg_dm-vg_dmbak   #删减
 lvreduce -L 900G -f -r /dev/mapper/vg_dm-vg_dmdata  #删减 

 lvextend -L -500G /dev/mapper/vg_dm-vg_dmbak        #扩容
 lvextend -L +400G /dev/mapper/vg_dm-vg_dmdata       #扩容