达梦常用SQL

--创建角色与赋权
CREATE ROLE "TEST";
GRANT CREATE SCHEMA, 
CREATE TABLE, 
CREATE VIEW TO "TEST";

--创建用户指定默认表空间
CREATE USER "TEST" IDENTIFIED BY "1QAZ2WSX#EDC" DEFAULT TABLESPACE "TEST" DEFAULT INDEX TABLESPACE "TEST";
--将角色赋予用户
GRANT "DBA","PUBLIC","SOI" TO "TEST";

--创建表
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)

--创建作业(全备)
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('全量备份');

--创建作业(增备)
周一到周六增备 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('增量备份');

--查询所有的模式名
SELECT DISTINCT object_name FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'SCH'
--根据模式名查询表名
SELECT table_name FROM dba_tables WHERE owner = '模式名'
--模式和表名查询表字段
SELECT COLUMN_NAME FROM all_tab_columns WHERE onwer = '模式名' AND Table_Name = '表名'
--查询单个字段值
SELECT '字段名' From '表名'


--查看当前用户的表
--SELECT * FROM USER_TABLES;
SELECT TABLE_NAME FROM USER_TABLES;
--查看所有用户的表
--SELECT * FROM ALL_TABLES;
SELECT TABLE_NAME FROM ALL_TABLES;
--查看所有表(包含系统表)
SELECT * FROM DBA_TABLES ;

--获取表字段
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';

--查看表索引字段
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'SYSJOBHISTORIES2';

达梦常用的系统表和视图：

系统表
表名	描述
SYSOBJECTS	可查询所有对象
SYSCOLUMNS	可查询表的所有列的信息
dba_tables	可查询表信息（包括系统表）
dba_users	可查询系统所有用户
dba_data_files	可查询数据库文件信息
dba_segments	可查询模式
all_tables	可查询所有用户的表
user_tables	可查询当前用户所拥有的所有表
user_tablespaces	可查询表空间
系统视图
视图名	描述
v$database	可查询数据库信息
v$tablespace	可查询表空间信息
v$datafile	可查询数据文件信息
v$dm_arch_ini	可查询归档信息
v$dm_ini	可查询参数
v$instance	可查询数据库实例信息
v$rlogfile	可查询日志文件信息
v$systeminfo	可查询操作系统CPU和内存的信息
v$version	可查询数据库版本
v$wait_class	可查询等待（wait）情况

-- 达梦常用命令
--查询定义被修改的时间
SELECT OBJECT_NAME, 
       OBJECT_TYPE, 
       LAST_DDL_TIMEFROM DBA_OBJECTSWHERE OWNER = 'SYSDBA' 
   AND OBJECT_NAME = 'T_NULL';

--查询索引信息
  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 索引名称, 
         索引类型, 
         表名
--查询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';

--锁查询
select o.name, 
       l.* 
  from v$lock l, 
       sysobjects o 
 where l.table_id=o.id 
   and blocked=1
--查询用户下表行数和数据量大小
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;

--查询表占用空间大小
  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';

--查询用户空闲断开时间
SELECT A.USERNAME, 
       b.CONN_IDLE_TIME 
  FROM ALL_USERS A , 
       SYSUSERS B 
 WHERE A.USER_ID =B.ID;

--内存使用情况：
  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;

--阻塞信息查询
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;

--查询普通二级索引
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 ;

--查看表结构
select TABLEDEF('CLOUD', 'BFBANK');

--全库更新统计信息 CALL SP_DB_STAT_INIT ();

--查询表空间使用率
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;


--查看定时作业
select * from sysjob.SYSJOBSTEPS;
--查看管理用户
select username from dba_users where account_status='OPEN';

--查看用户表空间
select username,default_tablespace from dba_users where account_status='OPEN';

--查看拥有DBA角色的用户
select grantee,granted_role from dba_role_privs where granted_role='DBA';

查看当前数据库会话信息
--总会话数
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;

达梦社区地址：https://eco.dameng.com