--创建角色与赋权
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