SQL运维秘招
DM技术交流QQ群：940124259
温馨提示：我们数据库运维管理中常用SQL，可能对于开发人员用处不大，但可以起到参考学习的作用。此类SQL有很多，持续更新中。。。。
001. 统计表占用空间大小和记录条数
TIPS：适用（DTS迁移工具或dexp/dimp导出导入工具）迁移任务结束后对比数据。 |
|---|
-- WITH FUNCTION
with function #sf_page_mb() return dec
as begin return 1.0 * page() / 1024 / 1024; end;
select
#t.owner, -- 表的所属者（即模式）00
#t.table_name as tname, -- 表名
#t.tablespace_name as ts_name, -- 表空间名称
#sf_page_mb * table_used_space(#t.owner, #t.table_name) as total_mb, -- 表占用空间大小(已分配空间，可以理解成类似虚拟机的厚置备置零)
#sf_page_mb * table_used_pages(#t.owner, #t.table_name) as use_mb, -- 表实际使用空间大小
table_rowcount(#t.owner, #t.table_name) rowcnt -- 表的数据行记录数
from dba_tables #t
where #t.owner in ('cqsoft', 'CQSOFT') -- 根据自己数据库环境填写实际的模式名
order by 6 desc, 5 desc;
002. 三个维度汇总查询用户和模式空间大小的占用
TIPS：速查数据库中用户空间的具体占用情况，分三个维度（所有用户、单个用户、用户单模式）。 |
|---|
select
ur.name as user_name, -- 用户名
sch.name as schema_name, -- 模式名
sum(table_used_space(sch.name, t.name)) * 1.0 * page() / 1024 / 1024 as total_mb -- 空间占用总大小
from sysobjects ur
join sysobjects sch on ur.id = sch.pid and sch.type$='SCH'
join sysobjects t on t.schid = sch.id and t.subtype$='UTAB'
where ur.type$='UR' and ur.subtype$='USER'
-- and ur.name not in ('SYSDBA', 'SYS', 'SYSSSO', 'SYSAUDITOR') -- 可选项，用来排除系统预置用户
group by rollup(ur.name, sch.name)
order by 1 nulls last, 2 nulls last;
003. 预览数据库所有用户分配的默认表空间
| 查询用户的默认数据表空间(大小)和默认索引表空间(大小)以及用户真实占用空间多大 |
|---|
TIPS：快速定位用户数据量大的表空间，和在无管理工具图形界面情况下查得某用户的默认表空间 。 |
with function #sf_page_mb() return dec
as begin return 1.0 * page() / 1024 / 1024; end;
select
username as "用户名",
#sf_page_mb() * user_used_space(username) as "用户占用空间总大小(MB)",
dat_ts as "默认数据表空间名称",
#sf_page_mb() * ts_used_space(dat_ts) as "默认数据表空间大小(MB)",
ind_ts as "默认索引表空间大小(MB)",
#sf_page_mb() * ts_used_space(ind_ts) as "默认索引表空间大小(MB)"
from
(
select
o.name as username,
(
select name from v$tablespace tp where o.info3 & 0xFF = tp.id
) as dat_ts, -- 数据表空间
(
select
name
from
v$tablespace tp
where
decode(info3 >> 16, 0, o.info3 & 0xFF, info3 >> 16) = tp.id
) as ind_ts -- 索引表空间
from
sysobjects o
where
o.type$ ='UR'
and o.subtype$='USER'
-- and o.name='U1'
);
--------------------------------- 华丽分割线 ---------------
-- 新版：加入用户表空间使用上限，弥补管理工具图形界面bug(误导)
with function #sf_page_mb() return dec
as begin return 1.0 * page() / 1024 / 1024; end;
select
username as "用户名",
#sf_page_mb() * user_used_space(username) as "用户占用空间总大小(MB)",
dat_ts as "默认数据表空间名称",
#sf_page_mb() * ts_used_space(dat_ts) as "默认数据表空间大小(MB)",
ifnull(#sf_page_mb() * space_limit, '无限制') as "空间使用上限(MB)" ,
ind_ts as "默认索引表空间大小(MB)",
#sf_page_mb() * ts_used_space(ind_ts) as "默认索引表空间大小(MB)"
from
(
select
o.name as username,
(
select name from v$tablespace tp where o.info3 & 0xFF = tp.id
) as dat_ts, -- 数据表空间
(
select
name
from
v$tablespace tp
where
decode(info3 >> 16, 0, o.info3 & 0xFF, info3 >> 16) = tp.id
) as ind_ts , -- 索引表空间
r.space_limit -- 空间使用上限
from
sysobjects o
left join v$resource_limit r on o.id = r.id
where
o.type$ ='UR'
and o.subtype$='USER'
-- and o.name='U1'
) ;
-- 修改用户使用指定表空间上限大小
alter user ZFQ DISKSPACE limit 100; -- 限定使用默认表空间100MB.
004. 快速排查位图索引与函数索引
TIPS:针对OLTP业务系统，存在位图索引的表频繁更新，无疑是毁灭性的灾难。 |
|---|
select
sf_get_schema_name_by_id(oi.schid) as index_owner, -- 索引所属模式
oi.name as index_name, -- 索引名称
tp.name as index_tablespace, -- 索引表空间
decode(i.type$, 'BM', '位图索引', '函数索引') as index_type, -- 索引类型
oi.valid as valid, -- 索引是否有效
case
when (I.XTYPE & 0x00010000) = 0 then 'VISIBLE'
else 'INVISIBLE'
end as visibility, -- 索引是否可见
indexdef(i.id, 1) as index_ddl -- 索引创建语句
from sysindexes i
join sysobjects oi on i.id = oi.id
join v$tablespace tp on i.groupid = tp.id
where dm_bit_test(i.xtype, 2) = 1
or i.type$='BM';
005. 列出数据库中所有账户与模式对应关系
TIPS：方便直观定位某些模式隶属于某账户，对于开发人员乱用账户登录操纵数据报错很有帮助。 |
|---|
select
sf_get_username_by_id(pid) username,
listagg(name, '|') within group(order by name) schname
from
sysobjects
where
type$='SCH'
group by
sf_get_username_by_id(pid)
order by
1;
006. 快速获取数据库含自增列表信息
TIPS：采用SQL导入方式迁移数据，预先避免带自增列的字段显式插入行记录的雷区（插入失败），单独拎出迁移。 |
|---|
select
sf_get_schema_name_by_id(st.schid) as table_owner, -- 模式名
st.name as table_name, -- 表名
sco.name as column_name, -- 列名
sco.type$ as column_type , -- 列类型
ident_current(sf_get_schema_name_by_id(st.schid)||'.'||st.name) as ident_current, -- 自增列当前值
ident_seed(sf_get_schema_name_by_id(st.schid)||'.'||st.name) as ident_seed, -- 种子值（起始值）
ident_incr(sf_get_schema_name_by_id(st.schid)||'.'||st.name) as ident_increment -- 增量值
from syscolumns sco
join sysobjects st on sco.id = st.id and st.subtype$='UTAB'
where bitand(sco.info2, 0x0001) = 1
order by 1,2;
007. 快速检索前10条慢SQL
TIPS：快速检索出当前时刻数据库执行时间1秒以上的前十条慢SQL语句，定位发起端。 |
|---|
select
top 10
*
from
(
select
sess_id, -- 会话ID，唯一标识符
user_name, -- 登录用户名
curr_sch as default_schema, -- 用户登录后操纵的默认模式
datediff(ms, last_recv_time,sysdate) as exec_ms, -- 持续执行多长时间(毫秒)
convert(varchar, sf_get_session_sql(sess_id)) as exec_sql, -- 大字段类型转成普通字符类型方便查看，SQL语句通常很少32767个字符的。
clnt_host, -- 远程访问主机名
clnt_ip, -- 远程访问主机地址IPv4/IPv6
clnt_type, -- 客户访问端类型(JDBC/SQL3)
appname -- 客户端工具名称
from v$sessions
where state='ACTIVE'
--and run_status='RUNNING'
)
where exec_ms >= 1000
order by exec_ms desc;
008. 速查库初始化参数
TIPS：方便查阅目标库的初始化参数，用于排查dexp/dimp和DTS迁移时。 |
|---|
select '数据库版本' as "初始化参数", id_code() as "参数值"
union all
select '库名' as "初始化参数", CUR_DATABASE() as "参数值"
union all
select '实例名', SF_GET_PARA_STRING_VALUE(2, 'INSTANCE_NAME')
union all
select '端口号', convert(varchar, SF_GET_PARA_VALUE(2, 'PORT_NUM'))
union all
select '字符集', convert(varchar, unicode())
union all
select '页大小' , convert(varchar, page())
union all
select '簇大小', convert(varchar, SF_GET_EXTENT_SIZE())
union all
select '大小写敏感', convert(varchar, case_sensitive())
union all
select 'varchar是否字符单位', convert(varchar, sf_get_para_value(2, 'LENGTH_IN_CHAR'))
union all
select '空白填充模式', convert(varchar, sf_get_para_value(2, 'BLANK_PAD_MODE'));
-----------------------华丽的分割线----------------------------------------
select
ss.p_name as "初始化参数",
ss.p_value as "参数值"
from (
select '数据库版本' as p_name, id_code() as p_value from dual
union all
select '库名' as p_name, CUR_DATABASE() as p_value from dual
union all
select
s.p_name ,
s.p_value
from
(
select
decode(para_name,
'INSTANCE_NAME', '实例名' ,
'PORT_NUM', '端口号',
'GLOBAL_CHARSET', '字符集',
'GLOBAL_PAGE_SIZE', '页大小',
'GLOBAL_EXTENT_SIZE', '簇大小' ,
'GLOBAL_STR_CASE_SENSITIVE', '大小写敏感',
'LENGTH_IN_CHAR', 'varchar是否字符单位' ,
'BLANK_PAD_MODE', '空白填充模式') as p_name ,
convert(varchar(50), para_value) as p_value
from v$option
) s
where s.p_name is not null
) ss
order by
(case
when ss.p_name = '数据库版本' then 0
when ss.p_name = '库名' then 1
when ss.p_name = '实例名' then 2
when ss.p_name = '端口号' then 3
when ss.p_name = '字符集' then 4
when ss.p_name = '页大小' then 5
when ss.p_name = '簇大小' then 6
when ss.p_name = '大小写敏感' then 7
when ss.p_name = 'varchar是否字符单位' then 8
when ss.p_name = '空白填充模式' then 9
end);
009. 获取数据库指定范围的索引
TIPS：适用于优化某SQL时，想快速查看指定表或者所有表上所有相关(有效，IS_VALID根据实际情况过滤)索引。 |
|---|
select * from
(
select -- 排除默认隐藏聚集(ROWID)和系统索引
sf_get_schema_name_by_id(i.schid) as owner, -- 模式名
sf_get_tablename_by_id(pid) as table_name, -- 表名
i.name as index_name, -- 索引名
i.valid as is_valid, -- 索引有效性
indexdef(s.id, 1) as index_ddl -- 索引定义DDL语句
from sysobjects i -- index object
join sysindexes s on s.id = i.id and i.subtype$='INDEX' -- index abstract
where dm_bit_test(s.flag, 1) = 0
UNION ALL
select -- 主键索引(索引）、唯一约束（索引）
sf_get_schema_name_by_id(i.schid) as owner,
t.name as table_name,
i.name as index_name,
i.valid as is_valid,
convert(varchar, dbms_metadata.get_ddl('INDEX', i.name, sf_get_schema_name_by_id(i.schid))) as index_ddl
from syscons c -- constraint outline
join sysobjects i on c.indexid = i.id and i.subtype$='INDEX' and c.type$ in ('P', 'U') -- constraint (pimary & unique)-- index object
join sysobjects t on t.id = c.tableid and t.subtype$='UTAB' -- table object
)
-- 外层过滤条件，根据自身需求而定
where owner='SYSDBA' -- 模式名
--and table_name='XXXXX' ; -- 表名
order by 1,2;
010. 速查外键无索引的表
| 快速补建外键索引 | TIPS：如果一张引用表（子表）的引用列（外键）没自建索引（不像MySQL建外键时自动建索引），当遇到大批量剧烈的OLTP业务（父子表之间的删除操作，可能引起阻塞）时，性能备受煎熬。 |
|---|
select
sf_get_schema_name_by_id(t.schid) as owner,
t.name as table_name,
sc.name as constraint_name,
sc.valid as enabled,
rtrim(
replace(
replace(dbms_metadata.get_ddl('CONSTRAINT', sc.name, sf_get_schema_name_by_id(SC.schid)),
'ADD CONS', 'MODIFY CONS'),
'FOREIGN KEY', 'TO FOREIGN KEY')
, ';' )
|| ' WITH INDEX;' as foreign_index_ddl
from syscons c -- constraint detail
join sysobjects sc on c.id = sc.id and c.type$='F' and sc.subtype$='CONS' -- constraint outline
join sysobjects t on c.tableid = t.id -- retrieve table
join sysindexes i on c.indexid = i.id -- index compare
where (i.xtype & 0x00000001) = 1
and (i.flag & 0x00000003) = 0x00000003; -- 虚索引bit(1) + PK bit(2)
011. 检查数据库备份情况
TIPS：快速遍历数据库BAK_PATH参数的默认备份目录，获取整个数据库备份状态和大小。 |
|---|
i提示：如果备份放在dm.ini文件参数BAK_PATH指定路径以外，请先用SF_BAKSET_BACKUP_DIR_ADD('DISK', '备份文件的父路径')加载，此函数仅对当前会话生效。 |
|---|
select
decode(d.level, 0, '联机备份', 1, '脱机备份') as backup_online,
decode(d.range#, 1, '整库备份', 2, '表空间备份', 3, '表级备份') as backup_range,
decode(d.type, 0, '基备份', 1, '增量备份', 2, '表备份', 3, '归档备份') as backup_type,
d.backup_name, d.backup_path, d.backup_time, cc.bakset_mb
from v$backupset d,
(
select
c.GRPCOL, 1.0 * sum(c.fil_sz) / 1024 / 1024 bakset_mb
from
(
select
decode(a.parent_id, -1, a.backup_id, a.parent_id) GRPCOL,
b.bkp_len fil_sz
from v$backupset a, v$backupset_bkp b
where a.backup_name = b.backupname
) c
group by c.GRPCOL
) cc
where cc.GRPCOL = d.backup_id and d.parent_id = -1
order by d.backup_time desc;
012. 快速检索库中有哪些临时表
TIPS：关注数据库中临时表的存在和临时表类型源头，核查临时表空间的磁盘空间。 |
|---|
-- 代码1
select owner,
table_name,
(case when duration='SYS$TRANSACTION' then '事务级' else '会话级' end) as temp_level
from dba_tables
where temporary = 'Y'
and tablespace_name= 'TEMP'
-- and owner = 'SYSDBA' -- 限定搜索范围：按模式
;
-----------------------------------------------------------------------------
-- 代码2
select sf_get_schema_name_by_id(schid) owner,
name table_name,
(case when info3 & 0x10000 <> 0 then '会话级' else '事务级' end) as temp_level
from sysobjects
where subtype$='UTAB'
and (info3 & 0x40) <> 0
-- and sf_get_schema_name_by_id(schid) = 'SYSDBA' -- 限定搜索范围：按模式
;
013. 表空间监控
TIPS：日常运维中高度关注的数据库资源监控项，在第三方运维监控平台设置告警阀值，尽早暴露资源不足，预防因磁盘空间不足而影响业务运转。
| 看到此SQL编写，可能有人觉得好复杂，其实不然，通过CTE层层递进方式推导而来，目的结构清晰，涉及扩展与非扩展的混合，及TEMP特殊的受限控制，所以中途改写几个版本，最终选用此版本。 |
|---|
| 有时表空间可能夹杂多个数据文件，其一是自动扩展文件自身最大上限（与页大小有关），其二是自动扩展文件手工设置最大上限，其三是非自动扩展文件的最大上限，即初始分配大小，最终合计该表空间的总上限。 |
| 已修复TEMP临时表空间的最大上限的准确取值，受TEMP_SPACE_LIMIT参数影响。 |
| 百分比以小数后6位取近似值，防止百分比为0的错觉（就算没有存数据，也会占用一丁点空间用于表空间的描述）。 |
with x0 as
(
-- MB单位换算函数
with function sf_ps() return number(20, 2) as begin return 1024 * 1024; end;
-- 列出所有表空间中所有相应的数据文件分布(适用于MPP)
select
/*+ LOCAL_OBJECT(V$DATAFILE) LOCAL_OBJECT(V$TABLESPACE)*/
ts.name tsname , -- 表空间名称
ts.file_num , -- 文件个数
d.id fno , -- 文件编号
decode(d.auto_extend, 0, 'NO', 1, 'YES', 'UNDEFINED') autoextensible, -- 自动扩展
ts.total_size * d.page_size / sf_ps ts_alloc_mb , -- 表空间已分配大小
d.total_size * d.page_size / sf_ps f_alloc_mb , -- 数据文件分配大小
d.free_size * d.page_size / sf_ps f_free_mb , -- 数据文件空闲大小
(case
when ts.name = 'TEMP'
then decode(ts.max_size, 0, d.total_size * d.page_size / sf_ps, ts.max_size * d.page_size / sf_ps)
else decode(d.max_size, 0, d.total_size * d.page_size / sf_ps, d.max_size)
end) f_max_mb -- 数据文件大小上限
from
v$tablespace ts
join v$datafile d on ts.id = d.group_id
where ts.status$ = 0 -- 必须是联机在线的可用表空间
), x1 as
(
select
tsname ,
file_num ,
ts_alloc_mb ,
sum(f_free_mb) ts_free_mb,
sum(f_max_mb) ts_max_mb ,
listagg(fno||decode(autoextensible, 'YES', ':Y', ':N'), ',')
within group(order by fno) fno_autoextend
from
x0
group by
tsname ,
file_num,
ts_alloc_mb
)
select
tsname , -- 表空间名称
file_num , -- 数据文件个数
fno_autoextend , -- 哪个文件自动扩展
ts_max_mb ts_total_mb , -- 表空间上限大小（包含自动扩展与非自动扩展）
(ts_alloc_mb - ts_free_mb) ts_use_mb, -- 已使用表空间大小
round(100.0 * ((ts_alloc_mb - ts_free_mb) / ts_max_mb), 6) ts_use_pct -- 使用百分比
from
x1
order by
ts_use_pct desc ;
版权声明：本文为ai20110304原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。