达梦数据库运维常用基础SQL（三）

作为数据库DBA运维人员，经常需要查询和监控数据库的运行情况，定位某些问题，为此我们整理出部分常用SQL，帮助大家更好的使用达梦数据库。本次整理出数据库对象信息、用户、权限、对象依赖查询、归档和备份文件管理等相关维护SQL。

一、数据库对象信息

1.1、查询数据库中动态视图：

DM8新版本动态视图信息保存在v$dynamic_tables中，查询该视图可以获取所有动态视图：

select * from v$dynamic_tables a;

可以使用名称模糊匹配要查找的动态视图信息，比如使用如下语句查询备份相关动态视图：

select * from v$dynamic_tables t where t.name like 'V$BACKUP%';

1.2、查询系统函数：

DM提供v$ifun视图可查询数据库中的系统函数：

select * from v$ifun t;

如果要获取系统函数的参数信息可查询v$ifun_args：

select a.name, b.* from v$ifun a, v$ifun_arg b where a.id = b.id and a.name like 'SF_ARCHIVELOG_%';  --系统函数名

1.3、查询函数/存储过程/包源码信息：

查看某个PACKAGE包（例如P_TEST）的源码信息，可以使用下列语句查询：

select * from DBA_SOURCE t where name = 'P_TEST';  --查询对象名

二、对象依赖关系

2.1、查询某张表/视图被哪些对象依赖：

我们清理某张表或修改某张表时，想要了解这张表对哪些视图或对象有影响，可以使用语句查询。

select a.owner, a.name, a.type from dba_dependencies a where a.referenced_name ='P_TEST';   --查询的依赖对象

2.2、查询某对象依赖哪些表/视图等对象：

查看某个PACKAGE包（例如DBMS_METADATA）用到了哪些表、视图、包、同义词等，可以使用下列语句查询：

SELECT 
a.referenced_owner, 
a.referenced_name, 
a.referenced_type, 
a.referenced_link_name 
FROM dba_dependencies a 
WHERE 
a.NAME ='DBMS_METADATA'  --查询的依赖对象
and a.type = 'PACKAGE';   --查询的依赖对象类型

三、用户相关信息查询

3.1、查询所有用户信息

查询系统中所有用户信息（包含用户默认表空间、索引表空间等，用户状态等）：

select t.username, t.user_id, t.account_status, t.default_tablespace, t.default_index_tablespace, t.password_versions from dba_users t;

查询系统中用户资源限制信息（包含用户最大登录失败次数、密码有效期、会话连接限制等）：

select b.USERNAME, a.* from sysusers a,dba_users b where a.id = b.user_id;

3.2、管理用户资源限制

资源限制用于限制用户对DM数据库系统资源的使用。DM8新版本支持创建或修改用户时直接使用limit <资源设置>语句和创建PROFILE（兼容Oracle）的方式设置资源设置项。

比如使用limit语句直接修改HR用户的最大登录失败次数为5次，密码输入错误超过5次将锁定3分钟：

alter user hr limit failed_login_attemps 5, password_lock_time 3;

DM8新版本支持使用PROFILE设置资源设置项,命令参考如下：

  create profile profile1 limit failed_login_attemps 5, password_lock_time 3;
  alter user hr profile profile1;

3.3、用户锁定/解锁

默认情况下，DM数据库用户密码输入错误超过3次，用户即会被锁定。

查询系统中锁定的用户及对应的锁定时间：

select t.username, 
       t.user_id, 
       t.account_status, 
       t.lock_date
  from dba_users t
 where t.account_status = 'LOCKED';

对于被锁定的用户，可以使用SYSDBA或者具有alter user权限的用户解锁。

alter user hruser ACCOUNT UNLOCK;   --解锁用户
alter user hruser ACCOUNT LOCK;      --锁定某用户

3.4、用户密码策略

DM普通版本只支持设置系统的口令测试，安全版本可以支持设置用户的口令策略。系统的口令策略由参数PWD_POLICY指定，其参数值说明如下，可组合设置。

PWD_POLICY：2（缺省值）。动态，系统级
设置系统默认口令策略。
0：无策略；
1：禁止与用户名相同；
2：口令长度不小于9；
4：至少包含一个大写字母（A-Z）；
8：至少包含一个数字（0－9）；
16：至少包含一个标点符号（英文输入法状态下，除“和空格外的所有符号；若为其他数字，则表示配置值的和，如3＝1+2,表示同时启用第1项和第2项策略。当OMPATIBLE_MODE=1时，PWD_POLICY的实际值均为0

使用如下命令可以设置系统口令策略，比如修改为1+2+4+8=15（包含大写字母、数字、且口令与用户名不同，且长度不小于9），不需要重启数据库即可生效。

alter system set 'PWD_POLICY'=15 both;

四、权限相关

4.1、当前用户权限：

查询当前用户拥有的权限：

select * from SESSION_PRIVS;

4.2、角色信息：

查询数据库中角色信息：

select * from dba_roles;

4.3、用户/角色权限：

查看某用户/角色拥有的系统权限信息：

select * from dba_sys_privs t where t.GRANTEE= 'HRTEST';    -- 用户/角色名

或者使用该用户登录，直接查询user_sys_privs。

select * from user_sys_privs t;

查看某用户/角色拥有的对象权限信息：

select * from dba_tab_privs t where t.GRANTEE= 'HRTEST';    -- 用户/角色名

或者使用该用户登录，直接查询user_tab_privs。

查看某用户/角色拥有的角色权限信息：

select * from dba_role_privs t where t.GRANTEE= 'HRTEST';    -- 用户/角色名

或者使用该用户登录，直接查询user_role_privs。

查看某用户/角色拥有的列权限信息：

select * from dba_col_privs t where t.GRANTEE= 'HRTEST';    -- 用户/角色名

或者使用该用户登录，直接查询user_col_privs。

五、归档日志/备份文件管理

5.1、归档日志文件查询

select * from v$arch_file t;

或：

select * from v$archived_log t;

5.2、归档日志文件删除

删除N天前的归档日志文件（这里以十天为例）：

select sf_archivelog_delete_before_time(trunc(sysdate) -10);

删除指定LSN之前的归档日志文件（LSN在归档日志中可查询到）：

select SF_ARCHIVELOG_DELETE_BEFORE_LSN(33769);

5.3、备份信息查询

DM数据库备份时如果没有指定备份集路径，则备份集保存至数据库默认备份路径下，默认备份路径由参数BAK_PATH指定：

select * from v$parameter t where name = 'BAK_PATH';

查询数据库中的备份信息（默认查询数据库默认备份路径下的备份信息）：

select * from v$backupset;

该视图不包含备份集的数据库魔数信息，如果要查询数据库魔数信息，可查询如下视图：

select * from v$backupset_dbinfo;

如果要查询其他目录下的备份集，需要添加备份目录，使用如下语句添加/dm8/backup目录（只针对当前会话生效）：

select sf_bakset_backup_dir_add('DISK', '/dm8/backup');

5.4、备份文件删除

批量删除30天前的备份文件。

select sf_bakset_remove_batch ('DISK',sysdate-30,null,null);

更多备份相关函数可查询v$ifun视图：

select * from v$ifun t where t.name like 'SF_BAKSET%';