六安门户网站建设哪家好,网站建设和app制作,网站域名起名,网站改版 百度收录1、查看服务器自身资源使用情况 查看内存#xff1a; free -g 查看整体负载: top 查看磁盘io #xff1a; iostat -d -x 1 2、查看数据库占用服务器内存情况,登录DM管理工具#xff0c;达梦数据库使用的内存大致等于 BUFFER MPOOL#xff0c;对应的 SQL 语句为#xff1a…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 STATEACTIVE)
where Y_EXETIME2;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_nameb.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;
