oracel 常用sql 语句

--管控系统临时表数量：
SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OBJECT_TYPE='TABLE' AND OWNER LIKE 'FMIS%'
AND (SUBSTR(OBJECT_NAME,1,4)='TEMP' OR SUBSTR(OBJECT_NAME,1,3)='TMP'
OR (SUBSTR(OBJECT_NAME,1,5)='BBHBG' AND LENGTH(OBJECT_NAME)>5)
OR (SUBSTR(OBJECT_NAME,1,10)='ZWCX_PZTMP' AND LENGTH(OBJECT_NAME)>10)
OR (SUBSTR(OBJECT_NAME,1,7)='PZCX_20' AND LENGTH(OBJECT_NAME)>7)
OR (SUBSTR(OBJECT_NAME,1,9)='LJZTABLE_' AND LENGTH(OBJECT_NAME)>9))；

--查询业务热点操作
select max(lcontent) as "功能点", count(lcontent) as "操作次数"
  from XTAUDITLOGEVT
 where INSTR(lcontent, '/') > 0
    or INSTR(lcontent, '\') > 0
 group by lcontent
having count(lcontent) > 1
 order by 2 desc

--补丁
select * from dba_registry_history;




--生成手工清除临时表语句
Select 'DROP TABLE ' || Owner || '.' || Object_Name || ' PURGE;' 
m Dba_Objects 
ere Object_Type = 'TABLE' And Owner Like 'FMIS%' And 
    (Substr(Object_Name, 1, 4) = 'TEMP' Or Substr(Object_Name, 1, 3) = 'TMP') 
  And    Rownum < 5000; 

--最近7天的DB TIME
WITH sysstat AS 
 (select sn.begin_interval_time begin_interval_time, 
         sn.end_interval_time end_interval_time, 
         ss.stat_name stat_name, 
         ss.value e_value, 
         lag(ss.value, 1) over(order by ss.snap_id) b_value 
    from dba_hist_sysstat ss, dba_hist_snapshot sn 
   where trunc(sn.begin_interval_time) >= sysdate - 7 
     and ss.snap_id = sn.snap_id 
     and ss.dbid = sn.dbid 
     and ss.instance_number = sn.instance_number 
     and ss.dbid = (select dbid from v$database) 
     and ss.instance_number = (select instance_number from v$instance) 
     and ss.stat_name = 'DB time') 
select to_char (BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') || to_char (END_INTERVAL_TIME, '  
hh24:mi') date_time, stat_name, round((e_value - nvl(b_value, 0)) / (extract(day 
  from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour 
  from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute 
  from(end_interval_time - begin_interval_time)) * 60 + extract(second 
  from(end_interval_time - begin_interval_time))), 0) per_sec 
  from sysstat 
 where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0 

--查看SESSION CURSOR CACHE情况:
select sn.name,ss.value where sn.statistic#=ss.statistic# and sn.name in ('session cursor cache hists','session cursor cache count','parse count(total)') and ss.sid=444444;

-- 查看归档空间情况db_recovery_file_dest/db_recovery_file_dest_size
select * from v$flash_recovery_area_usage;