運維排查問題常用sql

等待時長過大的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 ))

當前被鎖的資料庫物件

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

等待事件

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

當前資料庫許可權查詢並匯出賦權指令碼

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

查詢資料庫物件最後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';

查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;

查資料庫物件被哪個應用鎖了

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 ;

表空間中佔空間較大的物件查詢

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