达梦数据库和人大金仓数据库对数据库的运行查看情况

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 系列

SELECT
    pg_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
WHERE
    name = '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;

1. 备份表注释
   备份表注释的 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';

2. 清除表注释
   要清除表的注释：

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';  -- 仅选择基本表

3. 备份列定义
   备份列定义的 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';