ITPub博客

首页 > 数据库 > Oracle > 运维排查问题常用sql

运维排查问题常用sql

原创 Oracle 作者:zhcunique 时间:2021-03-24 09:09:10 0 删除 编辑
  1. 等待时长过大的sql

    select sql_text from gv$sql where hash_value in (select sql_hash_value from gv$session where seconds_in_wait > 1000 and sid in(select session_id from gv$locked_object ))

  2. 当前被锁的数据库对象

    select b . owner , b . object_name ,a. session_id ,a. locked_mode from gv$locked_object a, dba_objects b where b . object_id =a. object_id

  3. 等待事件

    select sql_id , event , machine , last_call_et from gv$session where wait_class <> 'Idle'

  4. 当前数据库权限查询并导出赋权脚本

    select 'grant ' || owner || '.' || table_name || ' to ' || grantee || ';' from dba_tab_privs where grantee in ( '' , '' , '' , '' , '' , '' )

  5. 查询数据库对象最后DDL时间

    ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

    SELECT CREATED ,last_ddl_time from dba_objects where owner='SDX' AND OBJECT_NAME='TSDX_FXPC_BDLS';

  6. 查session事件历史

    select event,count(*) from gv$active_session_history where to_char((sample_time),'yyyymmdd hh24:mi:ss') between '20201126 17:50:00' and '20201126 18:10:00' group by event;

  7. 查数据库对象被哪个应用锁了

    select a. object_name , b . session_id , c . serial# , c .program, c . username , c . command , c . machine , c . lockwait from all_objects  a, gv$locked_object  b , gv$session  c  where a. object_id = b . object_id and c .sid= b . session_id ;

  8. 表空间中占空间较大的对象查询

    select * from (select segment_name , PARTITION_NAME , segment_type , bytes / 1024 / 1024 from dba_segments where tablespace_name = 'SYSAUX' order by 4 desc) where rownum<= 10

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69994536/viewspace-2764548/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2021-02-04

  • 博文量
    63
  • 访问量
    460269