达梦数据库和人大金仓数据库对数据库的运行查看情况
1、查看服务器自身资源使用情况
查看内存： free -g
查看整体负载: top
查看磁盘io ： iostat -d -x 1
2、查看数据库占用服务器内存情况,登录DM管理工具，达梦数据库使用的内存大致等于 BUFFER + MPOOL，对应的 SQL 语句为：
select
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE,
( select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as mem_pool,
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from
v$mem_pool)||'MB' as TOTAL_SIZE
From dual;
3、查看服务器会话情况，此处统计>2s,登录DM管理工具
select * from ( SELECT sess_id,sql_text,state,datediff(ss,last_recv_time,sysdate) Y_EXETIME,to_char(SF_GET_SESSION_SQL(SESS_ID)) fullsql,clnt_ip FROM V$SESSIONS WHERE STATE='ACTIVE')
where Y_EXETIME>=2;
4、查看数据库空间使用情况
select a.tablespace_name,round(a.SPACE_LIMIT/1024/1024/1024,4) "总空间(GB)" , round((a.alloc_space-b.alloc_space_free)/1024/1024/1024,4) "已使用(GB)" ,round((a.alloc_space-b.alloc_space_free)/a.SPACE_LIMIT,6)*100 "使用率(%)" from
(select tablespace_name,sum(BYTES) alloc_space,sum(MAXBYTES) SPACE_LIMIT from SYS.DBA_DATA_FILES group by TABLESPACE_NAME) a,
(select tablespace_name,sum(BYTES) alloc_space_free from DBA_FREE_SPACE group by tablespace_name) b --已分配后的free空间，包括已使用后删除的部分
where a.tablespace_name=b.tablespace_name;
基于PostgreSQL 系列
SELECTpg_size_pretty(pg_size_bytes(current_setting('shared_buffers'))) AS shared_buffers,pg_size_pretty(pg_size_bytes(current_setting('work_mem'))) AS work_mem,pg_size_pretty(pg_total_relation_size('pg_catalog.pg_class')) AS buffer_size
FROM pg_settings
WHEREname = 'shared_buffers';
2查看服务器会话情况（统计执行时间 > 2s）
SELECT pid AS sess_id,query AS sql_text,state,extract(epoch from (now() - query_start)) AS y_exetime,client_addr AS clnt_ip
FROM pg_stat_activity
WHERE state = 'active' AND extract(epoch from (now() - query_start)) >= 2;
3查看数据库空间使用情况
对于查看数据库空间使用情况，可以使用 pg_tablespace 和 pg_class 视图来计算空间的使用情况：
SELECT t.spcname AS tablespace_name,pg_size_pretty(pg_tablespace_size(t.spcname)) AS total_space,pg_size_pretty(pg_tablespace_size(t.spcname) - COALESCE(SUM(pg_total_relation_size(c.oid)), 0)) AS used_space,ROUND((pg_tablespace_size(t.spcname) - COALESCE(SUM(pg_total_relation_size(c.oid)), 0)) * 100.0 / pg_tablespace_size(t.spcname), 2) AS usage_percentage
FROM pg_tablespace t
LEFT JOIN pg_class c ON t.oid = c.relnamespace
GROUP BY t.spcname;
- 备份表注释
备份表注释的 SQL 查询，已修改为使用 pg_description：
SELECT concat('COMMENT ON TABLE ', t.table_schema, '.', t.table_name, ' IS ''', coalesce(d.description, ''), ''';'
)
FROM information_schema.tables t
LEFT JOIN pg_catalog.pg_description d ON d.objoid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = t.table_name AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = t.table_schema))
WHERE t.table_schema = 'public';- 清除表注释
要清除表的注释：
SELECT concat('COMMENT ON TABLE ', t.table_schema, '.', t.table_name, ' IS '''';'
) AS sql_statement
FROM information_schema.tables t
WHERE t.table_schema = 'public' -- 替换为您要清除注释的模式名称
AND t.table_type = 'BASE TABLE'; -- 仅选择基本表
- 备份列定义
备份列定义的 SQL 查询：
SELECT concat('ALTER TABLE ', c.table_schema, '.', c.table_name, ' ALTER COLUMN ', c.column_name, ' TYPE ', c.data_type, CASE WHEN c.is_nullable = 'YES' THEN '' ELSE ' SET NOT NULL' END,CASE WHEN c.column_default IS NULL THEN ''WHEN c.data_type IN ('character varying', 'character') OR (c.data_type IN ('date', 'timestamp without time zone', 'timestamp with time zone') AND c.column_default != 'CURRENT_TIMESTAMP') THEN concat(' DEFAULT ''', c.column_default, '''')ELSE concat(' DEFAULT ', c.column_default)END,' COMMENT ''', coalesce(dc.description, ''), ''';'
) AS s
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_description dc ON dc.objoid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = c.table_name AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = c.table_schema)
)
WHERE c.table_schema = 'public'
GROUP BY c.table_schema, c.table_name, c.column_name, c.data_type, c.is_nullable, c.column_default, dc.description;适用于 PostgreSQL 的表名、列名、数据类型及其相应的描述
SELECT t.table_name AS 表名,obj_description(t.table_name::regclass) AS 表注释,c.column_name AS 列名,c.data_type AS 数据类型,c.character_maximum_length AS 长度,c.is_nullable AS 是否为空,c.column_default AS 默认值,col_desc.description AS 备注,CASE WHEN c.column_name IN (SELECT a.attname FROM pg_index i JOIN pg_attribute a ON a.attnum = ANY(i.indkey) WHERE i.indrelid = t.table_name::regclass AND i.indisprimary) THEN 'pk' ELSE '' END AS extra
FROM information_schema.tables t
JOIN information_schema.columns c ON c.table_name = t.table_name AND c.table_schema = t.table_schema
LEFT JOIN pg_catalog.pg_description col_desc ON col_desc.objoid = c.table_name::regclass AND col_desc.objsubid = c.ordinal_position
WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE';