大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说oracle存储过程异常怎么捕捉_查询oracle数据库状态,希望您对编程的造诣更进一步.
- 数据库空间占用
SELECT
pg_database.datname,
pg_size_pretty ( pg_database_size ( pg_database.datname ) ) AS SIZE
FROM
pg_database
WHERE
pg_database.datistemplate = FALSE
代码100分
查询结果示例：
代码100分water_ommp 8633 kB
water_workflow 10137 kB
mdmres 10017 kB
water_all_dev 2098 MB
- 表空间占用
# 1）查询表空间
SELECT
spcname
FROM
pg_tablespace;
#结果示例：
pg_default
pg_global
#2）查询某个表空间大小
SELECT
pg_size_pretty ( pg_tablespace_size ( "pg_default" ) );
#结果示例：
25 GB
- 当前连接数
代码100分SELECT COUNT
( 1 )
FROM
pg_stat_activity;
- 最大连接数
SHOW max_connections;- 慢SQL查询
需要安装插件pg_stat_statements，安装方式可参见： https://blog.csdn.net/pg_hgdb/article/details/79594775
SELECT SUBSTRING
( query, 1, 1000 ) AS short_query,
round( total_time :: NUMERIC, 2 ) AS total_time,
calls,
round( ( 100 * total_time / SUM ( total_time :: NUMERIC ) OVER ( ) ) :: NUMERIC, 2 ) AS percentage_cpu
FROM
pg_stat_statements
WHERE
LOWER ( query ) LIKE"select%"
ORDER BY
total_time DESC
LIMIT 5;
元数据采集
- 数据库信息
SELECT
oid,
datname
FROM
pg_database
WHERE
datistemplate = FALSE;
- Schema信息
SELECT SCHEMA_NAME
FROM
information_schema.schemata
WHERE
CATALOG_NAME = "water_all_dev"
AND SCHEMA_NAME NOT LIKE"pg_%"
AND SCHEMA_NAME != "information_schema";
- 表信息
SELECT
tablename
FROM
pg_tables
WHERE
schemaname = "sda";
- 字段信息
SELECT
attname,
attnum,
TYPE.typname,
CASE
WHEN attlen < 0 THEN
atttypmod ELSE attlen
END AS attrlen,
attnotnull,
CASE
WHEN atthasdef THEN
def.adsrc ELSE NULL
END attdef,
CASE
WHEN con.contype = "p" THEN
TRUE ELSE FALSE
END attrpri
FROM
pg_class
C INNER JOIN pg_attribute attr ON C.oid = attr.attrelid
INNER JOIN pg_type TYPE ON TYPE.oid = attr.atttypid
INNER JOIN pg_namespace nps ON nps.oid = C.relnamespace
LEFT JOIN pg_attrdef def ON ( def.adrelid = C.oid AND def.adnum = attr.attnum )
LEFT JOIN pg_constraint con ON ( con.conrelid = C.oid AND attr.attnum = con.conkey [ 1 ] )
WHERE
relname = "sda_dict_t"
AND nps.nspname = "sda"
AND attnum > 0
ORDER BY
attnum;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/9912.html
