DM达梦数据库常用命令整理
- 1.数据库自身信息
- 1.1 查询实例信息
- 1.2 查询数据库当前状态
- 1.3 查询DB_MAGIC
- 1.4 查询是否归档
- 1.5 查询授权截止有效期
- 1.6 查看等待情况
- 1.7 查看数据库配置端口
- 1.8 查询数据库最大连接数
- 1.9 查询命令执行计划
- 1.10 查询用户密码限制登录次数和密码过期天数
- 1.11 查询数据库字符集
- 1.12 修改密码策略
- 1.13 查看密码策略
- 1.14 查看每个用户的密码策略
- 2.数据库文件/空间信息
- 2.1 查询归档信息
- 2.2 查看控制文件
- 2.3 查询日志文件
- 2.4 查询数据库占用空间
- 2.5 查询数据文件位置
- 2.6 查询表空间大小
- 2.7 查看表空间使用情况
- 2.8 查询当前用户模式
- 3.数据库用户/角色信息
- 3.1 查询数据库有哪些用户
- 3.2 查询数据库用户信息
- 3.3 查看数据库对象
- 3.4 查询用户对象
- 3.5 查看角色类型
- 3.6 查看用户的角色和权限
- 3.7 表空间脱机/在线
- 4.数据库运维信息
- 4.1 创建表空间
- 4.2 查询总表数量
- 4.3 关闭数据库
- 4.4 创建用户
- 5.数据库表/列/视图信息
- 5.1 查询当前用户所有表
- 5.2 查询表的大小
- 5.3 创建新表
- 5.4 增加表的列
- 5.5 删除表的列
- 5.6 重命名表名
- 5.7 增加表注释
- 5.8 查询表注释
- 5.8 增加列注释
- 5.9 查询列注释
- 5.10 创建视图
说明： 主要对DM V8数据库，常用SQL命令进行整理，方便日后进行查找，以及对状态结果是否正确的确认。命令设计的均为简单的命令，即查询，创建，定义等方式成功即可，不涉及复杂的限制参数，参考条件等。此次使用的为DM V8单实例数据库，不涉及特殊模式下，个别特殊命令的查询。
1.数据库自身信息
1.1 查询实例信息
SQL> select name inst_name from v$instance;
行号 INST_NAME
---------- ---------
1 DMSERVER
已用时间: 11.211(毫秒). 执行号:15.
1.2 查询数据库当前状态
SQL> select status$ from v$instance;
行号 STATUS$
---------- -------
1 OPEN
1.3 查询DB_MAGIC
SQL> select db_magic from v$rlog;
行号 DB_MAGIC
---------- --------------------
1 1040931148
1.4 查询是否归档
SQL> select arch_mode from v$database;
行号 ARCH_MODE
---------- ---------
1 N
1.5 查询授权截止有效期
SQL> select EXPIRED_DATE from v$license;
行号 EXPIRED_DATE
---------- ------------------
1 2021-06-01
1.6 查看等待情况
SQL> select class_name,total_waits count from v$wait_class;
未选定行
已用时间: 116.087(毫秒). 执行号:27.
1.7 查看数据库配置端口
SQL> select para_name,para_value from v$dm_ini where para_name like '%PORT%';
行号 PARA_NAME PARA_VALUE
---------- -------------------- ----------
1 PORT_NUM 5236
2 ELOG_REPORT_LINK_SQL 0
3 DFS_HUGE_SUPPORT 1
4 EXTERNAL_JFUN_PORT 6363
5 EXTERNAL_AP_PORT 4236
6 DCRS_PORT_NUM 6236
7 AP_PORT_NUM 0
8 DW_PORT 0
9 DCP_PORT_NUM 5237
10 HA_OTHER_INST_PORT 65534
11 HA_INST_CHECK_PORT 65534
11 rows got
已用时间: 96.994(毫秒). 执行号:8.
1.8 查询数据库最大连接数
SQL> select SF_GET_PARA_VALUE(2,'MAX_SESSIONS');
行号 SF_GET_PARA_VALUE(2,'MAX_SESSIONS')
---------- -----------------------------------
1 100
已用时间: 1.961(毫秒). 执行号:5.
1.9 查询命令执行计划
SQL> explain select * from test_table;
1 #NSET2: [0, 1, 56]
2 #PRJT2: [0, 1, 56]; exp_num(2), is_atom(FALSE)
3 #CSCN2: [0, 1, 56]; INDEX33555607(TEST_TABLE)
已用时间: 3.655(毫秒). 执行号:0.
1.10 查询用户密码限制登录次数和密码过期天数
SQL> select ,p.FAILED_NUM,p.life_time from SYSUSERS p,dba_users u where p.FAILED_NUM not in ('0') order by 1,2 ;
行号 USERNAME FAILED_NUM LIFE_TIME
---------- ---------- ----------- -----------
1 SYS 3 0
2 SYSAUDITOR 3 0
3 SYSDBA 3 0
4 SYSSSO 3 0
5 TEST 3 0
已用时间: 12.079(毫秒). 执行号:66.
注释：life_time为0，表示不限制密码过期天数
1.11 查询数据库字符集
SQL> select SF_GET_UNICODE_FLAG();
行号 SF_GET_UNICODE_FLAG()
---------- ---------------------
1 0
已用时间: 0.321(毫秒). 执行号:38.
SQL> select UNICODE ();
行号 UNICODE()
---------- -----------
1 0
已用时间: 0.288(毫秒). 执行号:39.
注释：0 表示 GB18030，1 表示 UTF-8，2 表示 EUC-KR
1.12 修改密码策略
SQL> SP_SET_PARA_VALUE(1,'PWD_POLICY',3);
DMSQL 过程已成功完成
已用时间: 10.188(毫秒). 执行号:7.
系统支持的口令策略有：
0无策略
1禁止与用户名相同
2口令长度不小于9
4至少包含一个大写字母（A-Z）
8至少包含一个数字（0-9）
16至少包含一个标点符号（英文输入法状态下，除”和空格外的所有符号）
口令策略可单独应用，也可组合应用。比如我们即要求禁止用户名与密码相同，又要求口令至少包含一个大写字母，则设置口令策略为1+4=5即可。
1.13 查看密码策略
SQL> select * from v$dm_ini a where a.PARA_NAME = 'PWD_POLICY';
行号 PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE
---------- ---------- ---------- --------- --------- ------- ---------- ----------
DESCRIPTION PARA_TYPE
----------------------- ---------
1 PWD_POLICY 2 0 31 N 2 2
Flag of password policy SYS
已用时间: 16.146(毫秒). 执行号:4.
1.14 查看每个用户的密码策略
SQL> select username,password_versions,account_status from dba_users;
行号 USERNAME PASSWORD_VERSIONS ACCOUNT_STATUS
---------- ---------- ----------------- --------------
1 SYSDBA 0 OPEN
2 SYS 0 OPEN
3 TEST12345 3 OPEN
4 TEST 2 OPEN
5 SYSSSO 0 OPEN
6 SYSAUDITOR 0 OPEN
6 rows got
已用时间: 185.814(毫秒). 执行号:11.
2.数据库文件/空间信息
2.1 查询归档信息
SQL> select * from v$dm_arch_ini;
行号 ARCH_NAME ARCH_TYPE ARCH_DEST ARCH_FILE_SIZE
---------- -------------- --------- --------------------------------------- --------------
ARCH_SPACE_LIMIT ARCH_HANG_FLAG ARCH_TIMER_NAME ARCH_IS_VALID ARCH_WAIT_APPLY
---------------- -------------- --------------- ------------- ---------------
ARCH_INCOMING_PATH ARCH_CURR_DEST
------------------ ---------------------------------------
1 ARCHIVE_LOCAL1 LOCAL /home/dmdba/opt/dmdbms/data/DAMENG/arch 128
5000 1 NULL Y NULL
NULL /home/dmdba/opt/dmdbms/data/DAMENG/arch
已用时间: 0.565(毫秒). 执行号:12.
2.2 查看控制文件
SQL> select para_value name from v$dm_ini where para_name='CTL_PATH';
行号 NAME
---------- --------------------------
1 /dmdbms/data/DAMENG/
已用时间: 31.907(毫秒). 执行号:11.
2.3 查询日志文件
SQL> select GROUP_ID ,FILE_ID,PATH,CLIENT_PATH from v$rlogfile;
行号 GROUP_ID FILE_ID PATH CLIENT_PATH
---------- ----------- ----------- ----------------------------------------------- ------------
1 2 0 /home/dmdba/opt/dmdbms/data/DAMENG/
2 2 1 /home/dmdba/opt/dmdbms/data/DAMENG/
2.4 查询数据库占用空间
注释：默认为M为单位
SQL> select sum(bytes/1024/1024)|| 'M' from dba_data_files;
行号 SUM(BYTES/1024/1024)||'M'
---------- -------------------------
1 289M
2.5 查询数据文件位置
SQL> select GROUP_ID , ID ,path,STATUS$ from v$datafile;
行号 GROUP_ID ID PATH STATUS$
---------- ----------- ----------- --------------------------------------------- -----------
1 0 0 /home/dmdba/opt/dmdbms/data/DAMENG/ 1
2 1 0 /home/dmdba/opt/dmdbms/data/DAMENG/ 1
3 3 0 /home/dmdba/opt/dmdbms/data/DAMENG/ 1
4 4 0 /home/dmdba/opt/dmdbms/data/DAMENG/ 1
2.6 查询表空间大小
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024||'M' from dba_data_files;
行号 FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024||'M'
---------- --------------------------------------------- ----------- --------------- --------------------
1 /home/dmdba/opt/dmdbms/data/DAMENG/ 0 SYSTEM 23M
2 /home/dmdba/opt/dmdbms/data/DAMENG/ 0 TEST 50M
3 /home/dmdba/opt/dmdbms/data/DAMENG/ 0 MAIN 128M
4 /home/dmdba/opt/dmdbms/data/DAMENG/ 0 TEMP 10M
5 /home/dmdba/opt/dmdbms/data/DAMENG/ 0 ROLL 128M
2.7 查看表空间使用情况
SQL> select tablespace_name,
t2.FREE_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' free_space,
t2.TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' total_space,
t2.FREE_SIZE*100/t2.total_size "% FREE"
from V$TABLESPACE t1, V$DATAFILE t2 where =t2.GROUP_ID;
行号 TABLESPACE_NAME FREE_SPACE TOTAL_SPACE % FREE
---------- --------------- ---------- ----------- --------------------
1 SYSTEM 17M 24M 74
2 MAIN 120M 128M 94
3 TEMP 9M 10M 99
4 ROLL 119M 128M 93
2.8 查询当前用户模式
SQL> SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;
行号 SYS_CONTEXT('userenv','current_schema')
---------- ---------------------------------------
1 SYSDBA
已用时间: 79.134(毫秒). 执行号:6.
3.数据库用户/角色信息
3.1 查询数据库有哪些用户
SQL> select username from dba_users;
行号 USERNAME
---------- ----------
1 SYSDBA
2 SYS
3 SYSSSO
4 SYSAUDITOR
已用时间: 9.740(毫秒). 执行号:6.
3.2 查询数据库用户信息
SQL> select username,user_id,default_tablespace,profile from dba_users;
行号 USERNAME USER_ID DEFAULT_TABLESPACE PROFILE
---------- ---------- ----------- ------------------ ---------------------------------------------
1 SYSDBA 50331649 MAIN /home/dmdba/opt/dmdbms/data/DAMENG/
2 SYS 50331648 SYSTEM /home/dmdba/opt/dmdbms/data/DAMENG/
3 SYSSSO 50331651 SYSTEM /home/dmdba/opt/dmdbms/data/DAMENG/
4 SYSAUDITOR 50331650 SYSTEM /home/dmdba/opt/dmdbms/data/DAMENG/
3.3 查看数据库对象
SQL> select owner,$ object_type, status,count(1) count# from sysobjects t1,sysobjects t2 where = and !=0 group by ,$,;
行号 OWNER OBJECT_TYPE STATUS COUNT#
---------- ---------- ----------- ------ --------------------
1 SYS INDEX Y 287
2 DEM INDEX Y 149
3 CTISYS STAB Y 1
4 CTISYS CONS Y 1
5 CTISYS INDEX Y 1
6 SYSSSO STAB Y 8
7 SYSSSO CONS Y 9
8 SYSSSO INDEX Y 9
9 SYSAUDITOR STAB Y 4
10 SYSAUDITOR CONS Y 2
11 SYSAUDITOR INDEX Y 5
3.4 查询用户对象
select * from user_objects;
行号 OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- ---------------- -------------- --------- -------------- -----------
CREATED
----------------------------------------------------------------------------------------------------
LAST_DDL_TIME
----------------------------------------------------------------------------------------------------
TIMESTAMP
----------------------------------------------------------------------------------------------------
STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
------ --------- --------- --------- --------- ------------
312 ADMIN_AUDIT_SQL NULL 1287 NULL TABLE
2020-08-20 18:07:18.521000
2020-08-20 18:07:18.000000
2020-08-20 18:07:18.521000
VALID N N NULL NULL NULL
312 rows got
已用时间: 793.928(毫秒). 执行号:55.
3.5 查看角色类型
SQL> select * from dba_roles;
行号 ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
---------- ---------------- ----------------- -------------------
1 DBA NULL NULL
2 DB_AUDIT_ADMIN NULL NULL
3 DB_AUDIT_OPER NULL NULL
4 DB_AUDIT_PUBLIC NULL NULL
5 DB_AUDIT_SOI NULL NULL
6 DB_AUDIT_VTI NULL NULL
7 DB_POLICY_ADMIN NULL NULL
7 rows got
已用时间: 5.827(毫秒). 执行号:9.
3.6 查看用户的角色和权限
SQL> select * from SYS.DBA_ROLE_PRIVS ;
行号 GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
---------- --------------- ---------------- ------------ ------------
1 SYSDBA DBA Y NULL
2 DBA VTI Y NULL
3 SYSDBA SYS_ADMIN N NULL
4 SYSDBA SOI Y NULL
5 TEST SOI N NULL
6 SYSDBA RESOURCE Y NULL
7 SYSDBA PUBLIC Y NULL
8 TEST PUBLIC N NULL
9 DB_POLICY_ADMIN DB_POLICY_VTI Y NULL
10 SYSSSO DB_POLICY_SOI Y NULL
11 SYSSSO DB_POLICY_PUBLIC Y NULL
行号 GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
---------- -------------- --------------- ------------ ------------
12 SYSSSO DB_POLICY_OPER Y NULL
13 SYSSSO DB_POLICY_ADMIN Y NULL
14 DB_AUDIT_ADMIN DB_AUDIT_VTI Y NULL
15 SYSAUDITOR DB_AUDIT_SOI Y NULL
16 SYSAUDITOR DB_AUDIT_PUBLIC Y NULL
17 SYSAUDITOR DB_AUDIT_OPER Y NULL
18 SYSAUDITOR DB_AUDIT_ADMIN Y NULL
18 rows got
已用时间: 4.989(毫秒). 执行号:48.
3.7 表空间脱机/在线
SQL> alter tablespace "TEST" offline;
操作已执行
已用时间: 280.323(毫秒). 执行号:4.
SQL> alter tablespace "TEST" online;
操作已执行
已用时间: 121.133(毫秒). 执行号:5.
4.数据库运维信息
4.1 创建表空间
注释：默认大小为M
SQL> create tablespace TEST datafile '/home/dmdba/opt/dmdbms/data/DAMENG/' size 50;
操作已执行
已用时间: 15.041(毫秒). 执行号:28.
4.2 查询总表数量
SQL> select count(*) from dba_tables;
行号 COUNT(*)
---------- --------------------
1 363
4.3 关闭数据库
SQL> shutdown normal; 关闭数据库
SQL> shutdown immediate; 正常方式关闭数据库
SQL> shutdown abort; 立即方式关闭数据库。数据库并不立即关闭，而是在执行某些清除工作后才关闭（终止会话、释放会话资源），需要10到20S。
4.4 创建用户
SQL> create user test1 identified by 123456789 default tablespace test;
操作已执行
已用时间: 13.383(毫秒). 执行号:36.
5.数据库表/列/视图信息
5.1 查询当前用户所有表
SQL> select table_name,tablespace_name from user_tables;
行号 TABLE_NAME TABLESPACE_NAME
---------- ------------------------------------ ---------------
1 ##PLAN_TABLE TEMP
2 ##HISTOGRAMS_TABLE TEMP
3 ADMIN_ROLE MAIN
4 ADMIN_SYS_CONFIG MAIN
5 ADMIN_USER MAIN
6 ADMIN_AUDIT MAIN
7 ADMIN_AUDIT_SQL MAIN
8 DBTOOL_DTS_TRANSFORM MAIN
9 DBTOOL_DTS_TRANSFORM_EXECUTE MAIN
5.2 查询表的大小
注释：页数，默认8KB大小
SQL> SELECT TABLE_USED_SPACE ('SYS','SYSOBJECTS') ;
行号 TABLE_USED_SPACE('SYS','SYSOBJECTS')
---------- ------------------------------------
1 160
5.3 创建新表
SQL> create table tests ( id char not null) ;
操作已执行
已用时间: 97.526(毫秒). 执行号:16.
5.4 增加表的列
SQL> alter table test_rename add ids int;
操作已执行
已用时间: 76.447(毫秒). 执行号:8.
5.5 删除表的列
SQL> alter table test_table drop ids;
操作已执行
已用时间: 107.795(毫秒). 执行号:10.
5.6 重命名表名
SQL> alter table tests rename to test_rename;
操作已执行
已用时间: 128.985(毫秒). 执行号:26.
5.7 增加表注释
SQL> comment on table test_rename is 'AAAAAAA';
操作已执行
已用时间: 52.596(毫秒). 执行号:37.
5.8 查询表注释
SQL> select comments from user_tab_comments where table_name = 'TEST_RENAME';
行号 COMMENTS
---------- --------
1 AAAAAAA
已用时间: 2.094(毫秒). 执行号:39.
5.8 增加列注释
SQL> comment on column test_rename.id is 'Primary';
操作已执行
已用时间: 5.780(毫秒). 执行号:40.
5.9 查询列注释
SQL> select * from user_col_comments where owner = 'SYSDBA' and table_name = 'TEST_RENAME' and column_name = 'ID';
行号 OWNER TABLE_NAME COLUMN_NAME COMMENTS
---------- ------ ----------- ----------- --------
1 SYSDBA TEST_RENAME ID Primary
已用时间: 66.837(毫秒). 执行号:48.
5.10 创建视图
SQL> create view v_test as select C1,C2 from T1 where C3='r';
操作已执行
已用时间: 123.383(毫秒). 执行号:12.