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 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/ai20110304/article/details/123674658