oracle check

---SESSION:
select * from gv$RESOURCE_LIMIT where RESOURCE_NAME in('processes','sessions');

--DATAFILE
select round((sum(a.bytes_alloc)-sum(nvl(b.bytes_free, 0)))/1024/1024/1024,3)  "used",  
       round(sum(a.bytes_alloc)/1024/1024/1024,2) "total "
from  (select  f.tablespace_name,
               sum(f.bytes) bytes_alloc
             from dba_data_files f
        group by tablespace_name) a,
      (select  f.tablespace_name,
               sum(f.bytes)  bytes_free
         from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+);

--TBS
select * from (
select df.tablespace_name Tablespace,
round((df.bytes-sum(fs.bytes))*100/df.bytes)||'%' "%Used",
round(sum(fs.bytes)*100/df.bytes)||'%' "% Free",
df.bytes/(1024*1024) "Total Size(MB)",
sum(fs.bytes)/(1024*1024) "Free Size(MB)",
round((df.bytes-sum(fs.bytes))*100/df.bytes) Used
from dba_free_space fs, 
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df          
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
) ORDER BY 6 DESC;

--or TBS
SELECT m.tablespace_name, round(max(m.used_percent), 1) as used
  FROM dba_tablespace_usage_metrics m,
       dba_tablespaces              t,
       dba_data_files               d,
       dba_thresholds               tt
 WHERE m.tablespace_name = t.tablespace_name
   AND d.tablespace_name = t.tablespace_name
   and tt.metrics_name = 'Tablespace Space Usage'
   and tt.object_name is null
and t.tablespace_name not like '%UNDO%'
 GROUP BY m.tablespace_name
 order by 2 desc;


--archive log
select trunc(completion_time),round(sum(mb)/1024,2)||' G' day_GB from
(select name,completion_time,blocks*block_size/1024/1024 mb from v$archived_log
 where creator='ARCH' AND completion_time>sysdate-3 )
group by trunc(completion_time)
order by 1;

----------------auto---------------------
--PCT USED
select A.TBS,SIZE_GB,USAGE||'%' USAGE,MAX_GB,PCT_USED from (
select df.tablespace_name TBS,
round(df.bytes/(1024*1024*1024),1) SIZE_GB,
round((df.bytes-sum(fs.bytes))*100/df.bytes,2) USAGE
from dba_free_space fs, 
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df          
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
) A
,
(select TBS, MAX_GB,
       round(100 * used_gb / max_gb) pct_used
  from (select a.tablespace_name TBS,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30), 2) used_gb,
               round(a.maxbytes / power(2, 30), 2) max_gb
          from (select f.tablespace_name,
                       sum(f.bytes) bytes_alloc,
                       sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
                  from dba_data_files f
                 group by tablespace_name) a,
               (select f.tablespace_name, sum(f.bytes) bytes_free
                  from dba_free_space f
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name(+))) B
         WHERE A.TBS=B.TBS
         ORDER BY 3 DESC


SELECT SEQUENCE#,to_char(COMPLETION_TIME,'YYYY-MM-DD HH24:MI') FROM v$archived_log
WHERE COMPLETION_TIME IN(
select MAX(COMPLETION_TIME) from v$archived_log
where STANDBY_DEST='YES' and applied='YES') and STANDBY_DEST='YES'

SELECT value||'/alert_'||SYS_CONTEXT ('USERENV', 'instance_name')||'.log' FROM GV$DIAG_INFO
where NAME='Diag Trace'



sqlplus -prelim / as sysdba

kill -9
ps -ef|grep -i local=no|grep -v grep|awk '{print $2}'|xargs kill -9

最后不行kill pmon 进程，启动db



----redo size ----
 select sum(lt) from (
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,
       dhsso.object_name,
       SUM(db_block_changes_delta) lt
  FROM dba_hist_seg_stat     dhss,
       dba_hist_seg_stat_obj dhsso,
       dba_hist_snapshot     dhs
 WHERE dhs.snap_id = dhss. snap_id
   AND dhs.instance_number = dhss. instance_number
   AND dhss.obj# = dhsso. obj#
   AND dhss.dataobj# = dhsso.dataobj#
   AND begin_interval_time between to_date ('20200519 16:00','YYYYMMDD HH24:MI' )and to_date ('20200519 16:59','YYYYMMDD HH24:MI' )
   and object_name = 'WIP_D_IRIS_CAB_DATA'
 GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
          dhsso.object_name
 order by 3 desc);
 
 164528
 22512
 
 
 SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, 
       COUNT(*) TOTAL 
FROM v$log_history  a
   where first_time>=to_char(sysdate-11)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) 
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
 
 
select * from dba_hist_sqlstat

sELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
       dbms_lob.substr(sql_text, 4000, 1),
       dhss.instance_number,
       dhss.sql_id,MODULE,
       executions_delta,
       rows_processed_delta
  FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst
 WHERE UPPER(dhst.sql_text) LIKE '%WIP_D_IRIS_CAB_DATA%'
   AND dhss.snap_id = dhs.snap_id
   AND dhss.instance_Number = dhs.instance_number
   AND dhss.sql_id = dhst.sql_id
   and begin_interval_time>to_date('2005201600','YYMMDDHH24MI') 



 SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY
 WHERE SQL_ID='5s0fc5dxafnwq'
  ----redo size end--------


----active session ---
SELECT sample_time,
       session_id,
       sql_id,
       program,
       blocking_session,
       blocking_session_serial#,
       b.name
  FROM SYS.wrh$_active_session_history a, v$event_name b
 WHERE sample_time BETWEEN TO_DATE ('2012/06/13 22:20', 'yyyy/mm/dd hh24:mi')
                       AND  TO_DATE ('2012/06/13 22:55',
                                     'yyyy/mm/dd hh24:mi')
       AND a.event_id = b.event_id;


复制