达梦常用SQL及脚本工具

目录

一、达梦常用SQL

（一）常用SQL

1.创建角色与赋权

2.创建用户指定默认表空间

3.将角色赋予用户

4.创建表

5.创建作业(全备)

6.创建作业(增备)

7.查询所有的模式名

8.根据模式名查询表名

9.模式和表名查询表字段

10.查询单个字段值

11.查看当前用户的表

12.查看所有用户的表

13.查看所有表(包含系统表)

14.获取表字段

15.查看表索引字段

（二）常用表和视图

1.查询定义被修改的时间

2.查询索引信息

3.查询temp表空间占用率

4.锁查询

5.查询用户下表行数和数据量大小

6.查询表占用空间大小

7.查询用户空闲断开时间

8.内存使用情况

9.阻塞信息查询

10.查询普通二级索引

11.查看表结构

12.全库更新统计信息

13.查询表空间使用率

14.查看定时作业

15.查看管理用户

16.查看用户表空间

17.查看拥有DBA角色的用户

18.查看当前数据库会话信息

二、达梦常用脚本工具

（一）服务脚本

（二）注册服务

（三）可视化工具

一、达梦常用SQL

（一）常用SQL

1.创建角色与赋权

CREATE ROLE "TEST";

GRANT CREATE SCHEMA,

CREATE TABLE,

CREATE VIEW TO "TEST";

2.创建用户指定默认表空间

CREATE USER "TEST" IDENTIFIED BY "1QAZ2WSX#EDC" DEFAULT TABLESPACE "TEST" DEFAULT INDEX TABLESPACE "TEST";

3.将角色赋予用户

GRANT "DBA","PUBLIC","SOI" TO "TEST";

4.创建表

CREATE TABLE "TEST"."TABLE_1"

(

"COLUMN_1" CHAR(10) NOT NULL ,

"COLUMN_2" CHAR(10),

"COLUMN_3" CHAR(10),

PRIMARY KEY("COLUMN_1")

)

STORAGE(INITIAL 1, NEXT 1, MINEXTENTS 1, FILLFACTOR 0)

5.创建作业(全备)

call SP_CREATE_JOB('全量备份',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('全量备份');

call SP_ADD_JOB_STEP('全量备份', '全备', 6, '00000000/dm8/backup', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('全量备份', '一周全备', 1, 2, 1, 1, 0, '23:00:00', NULL, '2024-05-18 15:05:33', NULL, '');

call SP_JOB_CONFIG_COMMIT('全量备份');

6.创建作业(增备)

周一到周六增备 call SP_CREATE_JOB('增量备份',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('增量备份');

call SP_ADD_JOB_STEP('增量备份', '增备', 6, '10000000/dm8/backup|/dm8/backup', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('增量备份', '每周增备', 1, 2, 1, 126, 0, '23:00:00', NULL, '2024-05-18 15:06:53', NULL, '');

call SP_JOB_CONFIG_COMMIT('增量备份');

7.查询所有的模式名

SELECT DISTINCT object_name FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'SCH'

8.根据模式名查询表名

SELECT table_name FROM dba_tables WHERE owner = '模式名'

9.模式和表名查询表字段

SELECT COLUMN_NAME FROM all_tab_columns WHERE onwer = '模式名' AND Table_Name = '表名'

10.查询单个字段值

SELECT '字段名' From '表名'

11.查看当前用户的表

SELECT * FROM USER_TABLES;

SELECT TABLE_NAME FROM USER_TABLES;

12.查看所有用户的表

SELECT * FROM ALL_TABLES;

SELECT TABLE_NAME FROM ALL_TABLES;

13.查看所有表(包含系统表)

SELECT * FROM DBA_TABLES ;

14.获取表字段

SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'PRODUCT_INVENTORY';

select * from all_tab_columns where Table_Name='PRODUCT_INVENTORY';

select * from dba_tab_columns where Table_Name='PRODUCT_INVENTORY';

15.查看表索引字段

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'SYSJOBHISTORIES2';

（二）常用表和视图

1.查询定义被修改的时间

SELECT OBJECT_NAME,

       OBJECT_TYPE,

       LAST_DDL_TIMEFROM DBA_OBJECTSWHERE OWNER = 'SYSDBA'

   AND OBJECT_NAME = 'T_NULL';

2.查询索引信息

  select 索引名称,

         索引类型,

         表名,

         wm_concat(索引列)

    from ( select a.index_name 索引名称,

                    index_type 索引类型,

                    a.table_name 表名,

                    b.column_name 索引列

               from user_indexes a

         inner join user_IND_COLUMNS b

                 on a.index_name = b.index_name

                    --   where a.table_name ='SYS_NOTIFY_TODO_DONE_INFO'

         )

   where 索引名称=索引名称

group by 索引名称,

         索引类型,

         表名

3.查询temp表空间占用率

select a.tablespace_name,

       a.total_mb,

       round(b.free_mb, 2) free_mb,

       TO_CHAR(ROUND((a.total_mb - b.free_mb) / a.total_mb * 100, 2), '990.99') || '%' "usage"

  from (select tablespace_name,

                sum(bytes) / 1024 / 1024 total_mb

           from dba_data_files

       group by tablespace_name) a, (select tablespace_name,

                sum(bytes) / 1024 / 1024 free_mb

           from dba_free_space

       group by tablespace_name) b,

       dba_tablespaces d

 where a.tablespace_name = b.tablespace_name(+)

   and a.tablespace_name = d.tablespace_name(+)

   and a.tablespace_name = 'TEMP';

4.锁查询

select o.name,

       l.*

  from v$lock l,

       sysobjects o

 where l.table_id=o.id

   and blocked=1

5.查询用户下表行数和数据量大小

with a as

     ( select table_rowcount(owner,table_name) 行数,

              table_used_space(owner,table_name)/1024.0/1024*page 大小mb,

              *

         from dba_tables

        WHERE OWNER ='PERSON'

     order by 大小mb desc

     )

select sum(行数),sum(大小mb) from a;

6.查询表占用空间大小

  SELECT A.OWNER AS "模式",

         A.SEGMENT_NAME AS "表名",

         A.BYTES/1024/1024 AS "大小(M)",

         A.TABLESPACE_NAME AS "所属表空间",

         B.COMMENTS AS "表注释"

    FROM DBA_SEGMENTS A,

         DBA_TAB_COMMENTS B

   WHERE A.OWNER=B.OWNER

     AND A.SEGMENT_NAME=B.TABLE_NAME

     AND A.OWNER='用户'

ORDER BY SEGMENT_NAME;

select TABLE_USED_SPACE('SYSDBA','LOG_COMMIT')*page()/1024/1024.0 as TABLE_MB

  from dual;

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='SYSDBA';

7.查询用户空闲断开时间

SELECT A.USERNAME,

       b.CONN_IDLE_TIME

  FROM ALL_USERS A ,

       SYSUSERS B

 WHERE A.USER_ID =B.ID;

8.内存使用情况

  SELECT A.CREATOR ,

         B.SQL_TEXT ,

         SUM(A.TOTAL_SIZE)/1024.0/1024.0 TOTAL_M,    --当前总量

         SUM(A.DATA_SIZE) /1024.0/1024.0 DATA_SIZE_M --实际使用量

    FROM V$MEM_POOL A,

         V$SESSIONS B

   WHERE A.CREATOR = B.THRD_ID

GROUP BY A.CREATOR,

         B.SQL_TEXT

ORDER BY TOTAL_M DESC;

9.阻塞信息查询

SELECT SYSDATE STATTIME,

       DATEDIFF(SS,S1.LAST_SEND_TIME,SYSDATE) SS,

       '被阻塞的信息' WT,

       S1.SESS_ID WT_SESS_ID,

       S1.SQL_TEXT WT_SQL_TEXT,

       S1.STATE WT_STATE,

       S1.TRX_ID WTTRX_ID,

       S1.USER_NAME WT_USER_NAME,

       S1.CLNT_IP WT_CLNT_IP,

       S1.APPNAME WT_APPNAME,

       S1.LAST_SEND_TIME WT_LAST_SENDTIME,

       '引起阻的信息' FM,

       S2.SESS_ID FM_SESS_ID,

       S2.SQL_TEXT FM_SQL_TEXT,

       S2.STATE FM_STATE,

       S2.TRX_ID FM_TRX_ID,

       S2.USER_NAME FM_USER_NAME,

       S2.CLNT_IP FM_CLNT_IP,

       S2.APPNAME FM_APPNAME,

       S2.LAST_SEND_TIME FM_LAST_SEND_TIME

  from v$sessions s1,

       v$sessions s2,

       v$trxwait w

 where s1.trx_id=w.id

   and s2.trx_id=w.wait_for_id;

10.查询普通二级索引

select indexdef(id, 1)

  from sysobjects

 where pid = ( SELECT ID

          FROM sysobjects

         WHERE NAME='BFBANK'

           AND SCHID = ( SELECT ID

                  FROM SYSOBJECTS

                 WHERE NAME='CLOUD'

                   AND TYPE$='SCH' ) )

   and subtype$ = 'INDEX'

   and name != 'INDEX' || id ;

11.查看表结构

select TABLEDEF('CLOUD', 'BFBANK');

12.全库更新统计信息

CALL SP_DB_STAT_INIT ();

13.查询表空间使用率

select t.name 表空间,

       d.total_size*SF_GET_PAGE_SIZE()/1024/1024 ||'M' 总空间,

   (d.total_size*SF_GET_PAGE_SIZE()/1024/1024)-(d.free_size*SF_GET_PAGE_SIZE()/1024/1024) ||'M' 已使用空间,

       d.free_size*100/d.total_size||'%' "空闲百分比"

  from v$tablespace t,

       v$datafile d

 where t.id=d.group_id;

14.查看定时作业

select * from sysjob.SYSJOBSTEPS;

15.查看管理用户

select username from dba_users where account_status='OPEN';

16.查看用户表空间

select username,default_tablespace from dba_users where account_status='OPEN';

17.查看拥有DBA角色的用户

select grantee,granted_role from dba_role_privs where granted_role='DBA';

18.查看当前数据库会话信息

总会话数

select count(*) from v$sessions;

按状态分组会话数

select count(*),state from v$sessions group by state;

按客户端分组会话数

select count(*),clnt_ip from v$sessions group by clnt_ip;

二、达梦常用脚本工具

（一）服务脚本

（二）注册服务

DM 提供了将 DM 服务脚本注册成操作系统服务的脚本，同时也提供了卸载操作系统服务的脚本。注册和卸载脚本文件所在目录为安装目录的“/scripts/root”子目录下。

注册服务脚本为dm_service_installer.sh，用户可以使用注册服务脚本将服务脚本注册成为操作系统服务，命令参数如下所示

示例：

dm_service_installer.sh -t dmserver -dm_ini /data/DAMENG/dm.ini -p DMSERVER

（三）可视化工具

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台