作为数据库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%';标签:运维,用户,视图,查询,达梦,SQL,where,select,user From: https://blog.51cto.com/jackin/5806330