达梦数据库维护常见问题记录

2023 年 01 月 11 日

540 次浏览

1691字数

# 达梦数据库用户登录限制查询

select 
    b.username as "达梦数据库用户名",
    a.failed_num as "失败次数限制",
    a.failed_attemps as "失败尝试次数",
    a.lock_time as "锁定时间(min)" 
    from sysusers a right join all_users b on a.id=b.user_id;

![][1]

# 达梦数据库用户锁定状态查询

select 
    username as "达梦数据库用户名", 
    account_status as "账户状态",
    lock_date as "锁定时间" 
    from dba_users;

![][2]

# 当前正在执行语句

SELECT * FROM (
    SELECT SESS_ID,SQL_TEXT,DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) SS,
           SF_GET_SESSION_SQL(SESS_ID) FULLSQL
     FROM V$SESSIONS WHERE STATE='ACTIVE')
     WHERE SS>=1;

![][3]

# 当前导致锁表的SQL查询

select * from v$sessions a, v$lock b where a.trx_id = b.trx_id and a.state='ACTIVE';

# 查询数据库总大小

select sum(bytes)/1024/1024/1024 as "Size(GB)" from SYS.DBA_DATA_FILES;

![][4]

# 查表

select getname(c_name),c_tablename,c_code 
    from tb_inf_infoset where c_tablename= upper('tb_sta_workflow_history');

# 查字段

select getname(c_name),c_column_name,c_constant_type 
    from tb_inf_infoitem where c_infoset_code='TB_STA_WORKFLOW_HISTORY';

# 查参数值

select getname(c_name),c_code from tp_code_item where c_type_code='PROC_STATUS';

# 根据字段查表

select getname(c_name), c_tablename, c_code from tb_inf_infoset
    where c_code in 
    (select c_infoset_code from tb_inf_infoitem where getname(c_name) like '%入本单位时间%');
    
    select * fom user_tab_colums where column_name =upper('字段名');

[1]: https://resource.if010.com/dm_database_01.png
  [2]: https://resource.if010.com/dm_database_02.png
  [3]: https://resource.if010.com/dm_database_03.png
  [4]: https://resource.if010.com/dm_database_04.png

最后修改：2023 年 06 月 28 日