达梦数据库运维常用脚本
1、查询数据库版本:
- 查询数据库大版本号:
Select * from v$version;
- 查询小版本号:
Select id_code;
2、表空间相关信息查询
- 查询数据文件和联机日志文件信息
select b.tablespace_name, b.file_name, b.bytes/1024/1024 size_m from dba_data_files b union all select 'RLOG', a.path, a.rlog_size/1024/1024 from v$rlogfile a;
- 查询表空间占用率
select b.file_name, b.tablespace_name, b.bytes/1024/1024 size_m, (b.bytes-(nvl(a.bytes,0)))/1024/1024 used_m, round((b.bytes-(nvl(a.bytes,0)))/(b.bytes)*100,2) usedrate from dba_free_space a,dba_data_files b where a.file_id(+) = b.file_id and a.tablespace_name(+) = b.tablespace_name order by b.tablespace_name;
3、模式相关信息查询
- 查看模式和用户的对应关系
select a.NAME schname, a.ID schid, b.id userid, b.NAME username from sysobjects a, sysobjects bwhere a."TYPE$"='SCH' and a.pid = b.id;
- 查看模式下所有对象信息
select a.object_name, a.object_id, a.object_type, a.created, a.status from dba_objects a where a.owner='DMHR'; -- 用户/模式名
- 批量禁用某个用户/模式下所有外键约束
将查询出的拷贝执行即可禁用外键约束。
select 'alter table '|| t.owner||'.'||t.table_name ||' disable constraint '||t.constraint_name||';' from dba_constraints t where t.owner = 'DMHR' -- 用户/模式名 and t.constraint_type = 'R';
- 查看系统外键约束的引用关系
查询某个用户或模式下外键约束的引用关系:
select t1.owner,t1.table_name, t2.table_name as "TABLE_NAME(R)", t1.constraint_name, t1.r_constraint_name as "CONSTRAINT_NAME(R)", a1.column_name, a2.column_name as "COLUMN_NAME(R)"from dba_constraints t1, dba_constraints t2, dba_cons_columns a1, dba_cons_columns a2where t1.owner = t2.owner and a1.owner = a2.OWNER and t1.owner = 'DMHR' and t1.r_constraint_name = t2.constraint_name and t1.constraint_name = a1.constraint_name and t1.r_constraint_name = a2.constraint_name;
4、作业相关
- 查看系统中的作业信息:
select t.job, t.schema_user, t.last_date, t.last_sec, t.next_date, t.next_sec, t."INTERVAL", t.broken, t.failures,t.what from dba_jobs t;
- 运行作业
call dbms_job.run(1637544901); --参数为jobid
- 历史作业运行情况
select * from sysjob.SYSJOBHISTORIES2;
5、会话/事务相关
- 查看会话信息
select t.SESS_ID, t.SQL_TEXT, t.STATE, t.TRX_ID, t.THRD_ID from v$sessions t;
- 查看当前会话
select sessid;
- 事务等待
使用如下sql查询数据库中的事务等待信息,如果为空,则表示当前无事务等待。
select * from v$trxwait;
或
select b.name, t.* from v$lock t, SYSOBJECTS b where t.BLOCKED = 1 and t.TABLE_ID = b.ID;
- 某个会话的历史sql信息
select * from V$SQL_HISTORY t where T.SESS_ID = '140577090115912'; -