当前位置：首页 > article >正文

达梦数据库常用指令都是工作中常用的

article 2024/12/2 7:59:05

达梦数据库连接配置文件名称

cd /etc/dm_svc.conf

查询 sql 日志记录是否开启：0 关闭，1/2/3开启);

select SF_GET_PARA_VALUE(1,'SVR_LOG')union ALL select SF_GET_PARA_VALUE(2,'SVR_LOG');

关闭 sql 日志记录功能

call SP_SET_PARA_VALUE(1,'SVR_LOG',0);

开启 sql 日志记录功能

call SP_SET_PARA_VALUE(1,'SVR_LOG',1);

切换达梦用户

su - dmdba

达梦DM8中，查看具体的版本号（准确说是发布日期）使用的是select id_code;命令

select id_code();

可以直接查询v$instance视图，更直接地查看数据库的当前状态：

select instance_name,status$ from v$instance;

版本号

select * from v$version;

查询表空间单位为byte

注意：考虑到备份恢复、并行读写、磁盘IO、坏块因素不建议maxsize 67108863，建议设置合理值，比如32G或50G等。
在某些情况下，可以把一个表空间内的多个数据文件放到不同的存储硬盘上，这样来可以起到分散 I/O 的目的，从而提高系统整体的运行效率。

select PATH,TOTAL_SIZE,FREE_SIZE,MAX_SIZE,page_size from v$datafile;

查询达梦的磁盘占用空间

SELECT T.OWNER,
       T.SEGMENT_NAME,
       T.SEGMENT_TYPE,
       T.TABLESPACE_NAME,
       T.BYTES,
       T.BYTES/1024 BYTE_KB,
       T.BYTES/1024/1024 BYTE_MB,
       T.BYTES/1024/1024/1024 BYTE_GB
FROM DBA_SEGMENTS T
ORDER BY T.BYTES DESC;

达梦增加数据文件

'--自动扩展  指定自动扩展参数  扩充尺寸和扩容上限参数
alter tablespace "fuwa" add datafile '/dmdb8/dmdata/fuwa/fuwa02024012601.dbf' size 200 autoextend on next 20 maxsize 1024;
'--自动扩展 如果不加autoextend on 默认开启自动扩展 扩充尺寸为0  扩容上限为67108863即不设限
alter tablespace "fuwa" add datafile '/dmdb8/dmdata/fuwa/fuwa02024012601.dbf' size 200;
'--不自动扩展
alter tablespace "fuwa" add datafile '/dmdb8/dmdata/fuwa/fuwa02024012601.dbf' size 200 autoextend off;

达梦查询表空间占用

SELECT
    t.tablespace_name AS "表空间名称",
    t.total_space AS "总空间(MB)",
    t.total_space - f.free_space AS "已使用空间(MB)",
    f.free_space AS "剩余空间(MB)",
    (t.total_space - f.free_space) / t.total_space * 100 AS "已使用百分比"
FROM
    (SELECT
         tablespace_name,
         SUM(bytes) / 1024 / 1024 AS total_space
     FROM
         dba_data_files where tablespace_name='MAIN'
     GROUP BY
         tablespace_name ) t
        JOIN
    (SELECT
         tablespace_name,
         SUM(bytes) / 1024 / 1024 AS free_space
     FROM
         dba_free_space where tablespace_name='MAIN'
     GROUP BY
         tablespace_name) f ON t.tablespace_name = f.tablespace_name;

达梦查询表空间占用

 SELECT a.tablespace_name "表空间名称",
       total / (1024 * 1024) "表空间大小(M)",
       free / (1024 * 1024) "表空间剩余大小(M)",
       (total - free) / (1024 * 1024 ) "表空间使用大小(M)",
       total / (1024 * 1024 * 1024) "表空间大小(G)",
       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
      FROM dba_free_space
      GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) total
      FROM dba_data_files
      GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

查询这个表空间下的索引

select 'SP_REORGANIZE_INDEX(''' || owner || ''',''' || index_name || ''');', table_name
from SYS.DBA_indexes
where tablespace_name = 'MAIN'
  and index_name
    in (select object_name
        from dba_objects
        where object_id in (select id
                            from SYS.SYSINDEXES
                            where id in (select a.object_id
                                         from dba_objects a,
                                              (select owner, index_name
                                               from SYS.DBA_indexes
                                               where tablespace_name = 'MAIN') b
                                         where a.owner = b.owner
                                           and a.OBJECT_NAME = B.INDEX_NAME)
                            order by ROOTPAGE desc));

存放所有表、视图、存储过程、约束、关键字、索引等的信息。

select * from sysobjects;

通过 V $TRX W A I T 视图查看进程，找到 I D ， I D 对应着 V$ SESSIONS视图的SESS_ID：

select * from v$trxwait;

设置ENABLE_STRICT_CHECK=1

SELECT * FROM V$PARAMETER WHERE NAME='ENABLE_STRICT_CHECK';
select SF_GET_PARA_VALUE(1,'ENABLE_STRICT_CHECK')
sp_set_para_value(2,'ENABLE_STRICT_CHECK',1)

查询用户相关数据

SELECT 
 T1.USERNAME AS 用户名,
 DECODE(T2.AUTHENT_TYPE,1,'数据库密码认证',2,'操作系统认证',3,'远程认证','未知认证方式') AS 用户认证方式,
 T2.SESS_PER_USER AS 用户最大会话数,
 T2.CONN_IDLE_TIME AS "用户空闲期（分钟1-1440）",
 T2.FAILED_NUM AS 用户登录失败次数限制,
 T2.LIFE_TIME AS "口令有效期（天0-365）",
 T2.REUSE_TIME AS "口令等待期（天0-365）",
 T2.REUSE_MAX AS 口令变更次数,
 T2.LOCK_TIME AS "用户锁定时间（分1-1440）",
 T2.GRACE_TIME AS "口令宽限期1-30",
 T2.PASSWORD AS 密码策略,
 T2.RN_FLAG AS 只读,
 T2.ALLOW_ADDR AS 允许访问的IP,
 T2.NOT_ALLOW_ADDR AS 不允许访问的IP,
 T2.ALLOW_DT AS 允许访问的时间,
 T2.NOT_ALLOW_DT AS 不允许访问的时间,
 T2.LAST_LOGIN_DTID AS 上次登录时间,
 T2.LAST_LOGIN_IP AS 上次登录IP,
 T2.FAILED_ATTEMPS AS 自上一次登录成功以来失败次数
FROM 
 DBA_USERS T1,
 SYSUSERS T2 
WHERE 
  T1.USER_ID = T2.ID

修改用户密码

ALTER USER username IDENTIFIED BY "password" ;

查询密码复杂度

SELECT b.username AS "达梦数据库用户名",
a.failed_num AS "失败次数限制",
a.failed_attemps AS "失败尝试次数",
a.lock_time AS "锁定时间（min）"
FROM sysusers a
RIGHT JOIN all_users b ON a.id = b.user_id

查看全文

http://www.kler.cn/a/418180.html