当前位置: 首页 > news >正文

DBA常用数据库查询语句(2)

5 数据库对象

5.1 没有主键的非系统表

Select owner, table_name
from dba_tables
where owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')minus
Select owner, table_name
from dba_constraints
where constraint_type = 'P'and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS');

5.2 没有索引的外键

SELECT acc.owner,acc.table_name,acc.constraint_name,acc.column_name
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_nameAND ac.constraint_type = 'R'and acc.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')AND (acc.owner, acc.table_name, acc.column_name, acc.position)IN(SELECT acc.owner, acc.table_name, acc.column_name, acc.positionFROM all_cons_columns acc, all_constraints acWHERE ac.constraint_name = acc.constraint_nameAND ac.constraint_type = 'R'MINUSSELECT table_owner, table_name, column_name, column_positionFROM all_ind_columns)
ORDER BY acc.owner,acc.table_name, acc.constraint_name,acc.column_name;

5.3 建有6个以上索引的非系统表

Select table_owner, table_name, count(*) index_count
from dba_indexes
where table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
having count(*) > 6
group by table_owner, table_name
order by 1,3 desc;

5.4 指向对象不存在的Public同义词

Select s.synonym_name, s.table_owner, s.table_name
from  sys.DBA_synonyms s
where not exists (Select 'x'from sys.DBA_objects owhere o.owner = s.table_ownerand   o.object_name = s.table_name)
and db_link is null  and s.owner = 'PUBLIC'
and s.table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;

5.5 指向对象不存在的非Public同义词

Select s.owner, s.synonym_name, s.table_owner, s.table_name
from  sys.DBA_synonyms s
where not exists (Select 'x'from sys.DBA_objects owhere o.owner = s.table_ownerand   o.object_name = s.table_name)and db_link is null    and s.owner <> 'PUBLIC'
and s.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;

5.6 没有授予给任何角色和用户的角色

Select role
from dba_roles r
whererole not in ('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE','WM_ADMIN_ROLE','IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE','OEM_MONITOR','HS_ADMIN_ROLE')andnot exists (Select 1from   dba_role_privs pwhere  p.granted_role = r.role);

5.7 将System表空间作为临时表空间的用户(除Sys外)

Select username
from   dba_users
where  temporary_tablespace = 'SYSTEM';

5.8 将System表空间作为默认表空间的用户(除Sys外)

Select username
from   dba_users
where  default_tablespace = 'SYSTEM'and    username <> 'SYS' ;

5.9 没有授予给任何用户的profiles

Select distinct profile
from dba_profilesminus
Select distinct profile
from dba_users;
5.10	没有和Package相关联的Package Body
Select pb.owner, pb.object_name
from   dba_objects pb
where  pb.object_type = 'PACKAGE BODY'and not exists (Select 1from   dba_objects pwhere  p.object_type = 'PACKAGE'and    p.owner = pb.ownerand    p.object_name = pb.object_name)
and pb.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1,2;

5.11 被Disabled的约束

Select owner, table_name, constraint_name, CONSTRAINT_TYPE
from dba_constraints
where status = 'DISABLED'
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
ORDER BY 1,2,3;

5.12 被Disabled的触发器

Select owner, nvl(table_name, '<system trigger>') table_name, trigger_name
from dba_triggers
where status = 'DISABLED'
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
ORDER BY 1,2,3;

5.13 Invalid Objects

Select OWNER, OBJECT_NAME, OBJECT_TYPE
from dba_objects
where status = 'INVALID'
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
ORDER BY 1,2,3;

5.14 执行失败或中断的Jobs

select job, to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "Last Date",to_char(this_date,'yyyy-mm-dd hh24:mi:ss') "This Date",broken,failures, schema_user, what
from dba_jobs where broken='Y' or failures>0;

5.15 当前未执行且下一执行日期已经过去的Jobs

select job, to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "Last Date",to_char(this_date,'yyyy-mm-dd hh24:mi:ss') "This Date",broken,failures, schema_user, what
from dba_jobs
where job not in (select job from dba_jobs_running)and broken='N' and next_date<sysdate;

5.16 含有未分析的非系统表的Schemas

Select distinct owner "Schema"
from DBA_tables
where num_rows is nulland owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;

5.17 含有未分析的非系统分区表的Schemas

Select distinct table_owner "Schema"
from DBA_tab_partitions
where num_rows is nulland table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;

5.18 含有未分析的非系统索引的Schemas

Select distinct owner "Schema" from DBA_indexes
where leaf_blocks is nulland owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;

5.19 含有未分析的非系统分区索引的Schemas

Select distinct index_owner "Schema"
from DBA_ind_partitions
where leaf_blocks is nulland index_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;

5.20 回滚段空间配置

select r.segment_name segment_name,r.owner owner,r.tablespace_name tablespace_name,r.status status,round(r.initial_extent / 1024 / 1024) initial_extent,round(r.next_extent / 1024 / 1024) next_extent,s.extents,0 extents,ROUND(s.rssize / 1024 / 1024) rssize,s.xacts active_transfrom dba_rollback_segs r, v$rollname n, v$rollstat swhere r.segment_name = n.nameand n.usn = s.usn;

5.21 用户角色查询

select username,ACCOUNT_STATUS,default_tablespace,temporary_tablespace,granted_rolefrom dba_users u, dba_role_privs rwhere u.username = r.granteeorder by username;

5.22 表和索引在同一表空间(不包含USERS,SYSAUX,SYSMAN,SYSTEM,TEMP 表空间)

select a.owner,a.tablespace_name tbsname,a.table_name tname,b.index_name inamefrom dba_tables a,dba_indexes bwherea.tablespace_name = b.tablespace_nameand b.table_name = a.table_nameand a.owner = b.ownerand b.owner NOT in ('SYS', 'SYSTEM')and a.tablespace_name not in ('USERS', 'SYSAUX', 'SYSMAN', 'SYSTEM', 'TEMP')order by owner;

5.23 单个用户大小估算

select nvl(t.owner, 'total:') owner,casewhen (to_char(sum(bytes) / 1024 / 10241)) < 1 then'0' || to_char(round(sum(bytes) / 1024 / 10241, 2))elseto_char(round(sum(bytes) / 1024 / 10241, 2))end "大小/Mb"from dba_segments tgroup by rollup(t.owner);

5.24 具有DBA角色的用户

select grantee,granted_role from dba_role_privs where granted_role='DBA';

5.25 具有SYSDBA权限的用户

SELECT * FROM v$pwfile_users;

5.26 系统表空间中非SYS对象

select OWNER,SEGMENT_NAME,SEGMENT_TYPE,decode(segment_type,'TABLE','alter table ' || OWNER || '.' || SEGMENT_NAME ||' MOVE TABLESPACE &' || 'TABLESPACE;','INDEX','alter index ' || OWNER || '.' || SEGMENT_NAME ||' REBUILD TABLESPACE &' || 'TABLESPACE NOLOGGING;',null) SCRIPTfrom dba_segments twhere t.tablespace_name = 'SYSTEM'AND OWNER NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'WMSYS');

5.27 检测SYSTEM表空间里的用户对象

select owner, segment_type, segment_namefrom dba_segmentswhere owner not in ('SYS', 'SYSTEM')and tablespace_name = 'SYSTEM'order by 1;

5.28 未建索引的表(不包含表空间为’SYSTEM’, ‘SYSAUX’, ‘SYSMAN’, ‘USERS’, 'TEMP’下的用户)

SELECT owner,segment_name,segment_type,tablespace_name,TRUNC(BYTES / 1024 / 1024, 1) size_mbFROM dba_segments tWHERE NOT EXISTS(SELECT 'x'FROM dba_indexes iWHERE t.owner = i.table_ownerAND t.segment_name = i.table_name)AND t.segment_type IN ('TABLE', 'TABLE PARTITION')AND t.owner IN (select usernamefrom dba_users dwhere d.default_tablespace not in('SYSTEM', 'SYSAUX', 'SYSMAN', 'USERS', 'TEMP')and d.account_status = 'OPEN')ORDER BY 5 DESC;

5.29 sort_segment检查

select tablespace_name,extent_size db_blocks_per_extent,total_extents,used_extents,free_extents from v$sort_segment;

5.30 超过2g的segment(单个表超过2g建议使用分区表)

select *from (Select segment_name,bytes / 1024 / 1024 size_M,segment_type,tablespace_namefrom dba_segmentswhere bytes > 2 * 1024 * 1024 * 1024order by bytes desc);

5.31 定时任务(JOB)

SELECT JOB,LOG_USER,PRIV_USER,SCHEMA_USER,LAST_DATE,THIS_DATE,NEXT_DATE,TOTAL_TIME,DECODE(BROKEN, 'Y', 'YES', 'N', 'NO') "JOB_BROKEN",INTERVAL,FAILURES,TRANSLATE(WHAT, chr(10), ' ') WHATFROM DBA_JOBSORDER BY JOB;

5.32 Rollback信息

select  substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",substr(sys.dba_segments.OWNER,1,8) "Owner",substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",substr(sys.dba_segments.EXTENTS,1,6) "Extent#",substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
from sys.dba_segments, sys.dba_rollback_segs
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name andsys.dba_segments.segment_type = 'ROLLBACK'
order by sys.dba_rollback_segs.segment_id;

5.33 查看表分区的信息

select t.table_name, kc.column_name, t.partitioning_typefrom dba_part_key_columns kc, dba_part_tables twhere kc.owner = t.ownerand kc.name = t.table_name;

5.34 查看超过16G的索引对像

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTESFROM DBA_SEGMENTSWHERE SEGMENT_TYPE='INDEX' AND BYTES>=17179869184;
## 可以通过重建索引减少空间:
ALTER INDEX index_name REBUILD [ONLINE];
## REBUILD和REBUILD ONLINE的区别:
ALTER INDEX REBUILD  ## 只扫描现有的索引块来实现索引的重建。
ALTER INDEX REBUILD ONLINE ## 实质上是扫描表而不是扫描现有的索引块来实现索引的重建

6 性能

6.1 锁等待检测

SELECT substr(lpad('--->',DECODE(request,0,0,4))||sid,1,20)   "SESSID", id1, id2, lmode, request, type
FROM V$LOCK WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1,request;

6.2 死锁检测

SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID,vss.SERIAL#, vss.action Action,vss.osuser OSUSER, vss.process AP_Process_ID,VPS.SPID DB_Process_ID 
from v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS 
where lo.OBJECT_ID = dob.OBJECT_ID and lo.SESSION_ID = vss.SID AND VSS.paddr = VPS.addr 
order by 2,3,DOB.object_name;

6.3 锁信息

select s.sid    sid,s.username  username,s.machine  machine,l.type    type,o.object_name  object_name,DECODE(l.lmode,0,'None',1,'Null',2,'Row Share',  3,'Row Exlusive',  4,'Share',  5,'Sh/Row Exlusive',  6,'Exclusive') lmode,DECODE(l.request,0,'None',1,'Null',2,'Row Share',  3,'Row Exlusive',  4,'Share',  5,'Sh/Row Exlusive',  6,'Exclusive') request,l.block    block  
fromv$lock l,v$session s,dba_objects o
wherel.sid = s.sidandusername != 'SYSTEM'ando.object_id(+) = l.id1;

6.4 用户会话情况

select max_proc, sess_cnt, round((sess_cnt*100)/max_proc,2) "USED%"
from
(select to_number(value) max_proc from v$parameter where lower(name)='processes') a,
(select count(*) sess_cnt from v$session) b;

6.5 Top I/O Wait

SELECT /*+ rule */ event,segment_type,segment_name,file_id,block_id,blocks  
FROM   dba_extents, gv$session_wait  
WHERE  p1text='file#'  AND p2text='block#'  AND p1=file_id and  p2 between block_id AND block_id+blocks  
ORDER BY segment_type,segment_name;

6.6 Top 10 Wait

select * 
from ( select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Total" from v$session_Wait group by event order by 4 desc ) 
where rownum<=10;

6.7 Top 10 bad SQL

SELECT *
FROM (SELECT parsing_user_id executions,sorts, command_type,disk_reads,sql_textFROM v$sqlareaORDER BY disk_reads DESC)
WHERE rownum < 10;

6.8 Top most expensive SQL (Buffer Gets by Executions)

select buffer_gets, executions,buffer_gets/ decode(executions,0,1, executions) gets_per_exec,hash_value,sql_text
from  v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc;

6.9 Top most expensive SQL (Physical Reads by Executions)

select disk_reads, executions,disk_reads / decode(executions,0,1, executions) reads_per_exec,hash_value,sql_text
from  v$sqlarea
where disk_reads > 10000
order by disk_reads desc;

6.10 Top most expensive SQL (Rows Processed by Executions)

select rows_processed, executions,rows_processed / decode(executions,0,1, executions) rows_per_exec,hash_value,sql_text
from   v$sqlarea
where  rows_processed > 10000
order by rows_processed desc;

6.11 Top most expensive SQL (Buffer Gets vs Rows Processed)

select buffer_gets, lpad(rows_processed ||decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",executions, loads,(decode(rows_processed,0,1,1))*buffer_gets/ decode(rows_processed,0,1,rows_processed) avg_cost,sql_text
from   v$sqlarea
Where decode(rows_processed,0,1,1) * buffer_gets/
decode(rows_processed,0,1,rows_processed)>10000
order by 5 desc;

6.12 Top 10 等待事件

select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAITfrom (select *from v$system_eventwhere event not like '%rdbms%'and event not like '%message%'and event not like 'SQL*Net%'order by total_waits desc)WHERE ROWNUM <= 10;

6.13 数据库长事务(执行超过6S)

select s.username, q.sql_text, s.elapsed_seconds, opnamefrom v$session_longops s, v$sqlarea qwhere s.sql_hash_value = q.hash_valueorder by s.ELAPSED_SECONDS desc;

6.14 产生大量物理读的进程

select st.sid, st.value, sn.name, s.usernamefrom v$sesstat st, v$statname sn, v$session swhere st.sid = s.sidAND st.statistic# = sn.statistic#and st.value > 100000and s.username is not nulland sn.name like '%physical read%'order by 2 desc;

6.15 产生归档日志过快的进程

select sysdate,se.username,se.sid,se.serial#,se.SQL_HASH_VALUE,se.status,se.machine,se.osuser,round(st.value / 1024 / 1024) redosize,sa.sql_textfrom v$session se, v$sesstat st, v$sqlarea sawhere se.sid = st.sidand st.STATISTIC# =(select STATISTIC# from v$statname where NAME = 'redo size')and se.username is not nulland st.value > 10 * 1024 * 1024and se.SQL_ADDRESS = sa.ADDRESSand se.SQL_HASH_VALUE = sa.HASH_VALUEorder by redosize;

6.16 等待事件对应的SQL语句

select b.sql_text text,a.sid      sid,a.serial#  serial#,a.username "user",a.machine  machinefrom v$session a, v$sqltext b, v$session_wait cwhere a.sid = c.sidand b.address = a.sql_addressand b.hash_value = a.sql_hash_valueorder by a.sid, a.serial#, b.piece;

6.17 占用大量temp表空间的session和sql监控

select su.extents, su.segtype, su.sqlhash, se.sid, se.serial#, se.last_call_et, se.username, se.machine ,sa.sql_textfrom v$sort_usage su, v$session se ,v$sqlarea sa
where su.session_addr=se.saddrand se.SQL_ADDRESS = sa.ADDRESSand se.SQL_HASH_VALUE = sa.HASH_VALUEand su.extents>10;

6.18 回滚段争用情况

select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where a.usn=b.usn;

6.19 Session等待事件

select sid, event, p1, p1text from v$session_wait s;

6.20 Listing Memory Used By All Sessions

select se.sid, n.name, max(se.value) maxmemfrom v$sesstat se, v$statname nwhere n.statistic# = se.statistic#and n.name in ('session pga memory','session pga memory max','session uga memory','session uga memory max')group by n.name, se.sidorder by 1,3;
http://www.lryc.cn/news/598756.html

相关文章:

  • 详解FreeRTOS开发过程(六)-- 队列
  • Redis操作
  • PostgreSQL 跨库查询方法
  • CMake ARGV变量使用指南
  • 基于C语言的Zynq SOC FPGA嵌入式裸机设计和开发教程
  • 外企本土化布局对国内连接器企业影响几何?
  • 模型的存储、加载和部署
  • rust-切片类型
  • centos7中把nginx更新到1.26 版(centos7默认只能更新到1.20)
  • IROS-2025 | OIKG:基于观察-图交互与关键细节引导的视觉语言导航
  • 【LeetCode 热题 100】39. 组合总和——(解法一)选或不选
  • windwos11网页切换残留/卡屏/冻结/残影问题
  • Java学习---Spring及其衍生(下)
  • 基于SpringBoot+Vue的电脑维修管理系统(WebSocket实时聊天、Echarts图形化分析)
  • 类和包的可见性
  • 磁性材料如何破解服务器电源高频损耗难题?
  • Linux C 网络基础编程
  • Redis高可用架构演进面试笔记
  • 13-C语言:第13天笔记
  • mysql索引底层B+树
  • HTTP/1.0、HTTP/1.1 和 HTTP/2.0 主要区别
  • OpenLayers 综合案例-基础图层控制
  • 主要分布在背侧海马体(dHPC)CA1区域(dCA1)的位置细胞对NLP中的深层语义分析的积极影响和启示
  • 《Java语言程序设计》第2章复习题(3)
  • 高亮标题里的某个关键字正则表达式
  • JMeter 性能测试实战笔记
  • 云端哨兵的智慧觉醒:Deepoc具身智能如何重塑工业无人机的“火眼金睛”
  • 无人机正摄影像自动识别与矢量提取系统
  • 无人机保养指南
  • 无人机速度模块技术要点分析