运行情况查询汇总

# 正在执行的SQL
正在执行语句，查询出来数据包含本语句的所有库内执行语句，仅做排查使用。
```sql
SELECT B.SID,
       B.SERIAL# ZID,
       B.USERNAME 用户名,
       C.MODULE 调用模块,
       B.MACHINE 计算机名,
       C.SQL_ID,
       C.SQL_TEXT,
       C.SQL_FULLTEXT SQL,
       'alter system kill session ' || '''' || b.sid || ',' || b.SERIAL# || ',@' ||
       b.INST_ID || '''' || ' immediate;' sd
  FROM gV$PROCESS A, gV$SESSION B, gV$SQL C
 WHERE A.ADDR = B.PADDR
   AND B.SQL_HASH_VALUE = C.HASH_VALUE
```
# 查看当前会话信息
```sql
SELECT t.INST_ID 数据库ID,
       t.SID,
       t.SERIAL#,
       t.USERNAME 用户名,
       t.COMMAND 执行命令类型,
       t.STATUS 状态,
       t.SERVER,
       t.OSUSER 客户端系统用户名,
       t.MACHINE 客户端操作系统机器名,
       t.TERMINAL 客户端操作系统终端名,
       utl_inaddr.get_host_address(t.terminal) 客户端IP,
       t.PROCESS 客户端进程id号_线程号,
       t.port 客户端端口号,
       t.PROGRAM 客户端程序名,
       t.MODULE,
       t.TYPE 会话类型,
       t.SQL_ADDRESS,
       t.SQL_HASH_VALUE,
       t.SQL_ID,
       (select s.SQL_TEXT
          from V$sql s
         where s.ADDRESS = t.SQL_ADDRESS
           and s.HASH_VALUE = t.SQL_HASH_VALUE
           and s.CHILD_NUMBER = t.SQL_CHILD_NUMBER) 当前_SQL语句,
       t.SQL_CHILD_NUMBER,
       t.SQL_EXEC_START sql执行开始时间,
       t.PREV_SQL_ADDR,
       t.PREV_HASH_VALUE,
       t.PREV_SQL_ID,
       (select s.SQL_TEXT
          from V$sql s
         where s.ADDRESS = t.SQL_ADDRESS
           and s.HASH_VALUE = t.SQL_HASH_VALUE
           and s.CHILD_NUMBER = t.SQL_CHILD_NUMBER) 前一个_SQL语句,
       t.PREV_CHILD_NUMBER,
       t.PREV_EXEC_START 前一个sql执行开始时间,
       t.EVENT,
       t.P1TEXT,
       t.p1,
       t.P2TEXT,
       t.P2,
       t.P3TEXT,
       t.p3,
       t.WAIT_CLASS
  FROM GV$SESSION t;
```

# 检查等待事件
```sql
SELECT SID, A.USERNAME, EVENT, WAIT_CLASS, T1.SQL_TEXT
FROM V$SESSION A, V$SQLAREA T1
WHERE WAIT_CLASS <> 'Idle'
AND A.SQL_ID = T1.SQL_ID;
```
# 历史会话
```sql
select ls.tjsj 时间, count(1) 会话连接数
  from (select to_char(t.SAMPLE_TIME, 'yyyy-mm-dd HH24:MI') as tjsj,
               t.SESSION_ID
          from V$active_Session_History t
         where t.SAMPLE_TIME > sysdate - 1) ls
 group by (ls.tjsj)
 order by tjsj;
```
# 历史DBTime
```sql
SELECT BEGIN_DATE 日期,
       MAX(BEGIN_TIME) AS 时间,
       ROUND(SUM(DBTIME) / SUM(TIMEINTERVAL) / 1000000, 2) AS 平均DBTIME,
       ROUND(SUM(DBCPU) / SUM(TIMEINTERVAL) / 1000000, 2) AS 平均DBCPU,
       ROUND(SUM(REDOSIZE) / SUM(TIMEINTERVAL) / 1024 / 1024, 2) AS 平均REDOSIZE,
       ROUND(SUM(PHYSICALREAD) / SUM(TIMEINTERVAL) / 1024 / 1024, 2) AS 平均PHYSICALREAD,
       ROUND(SUM(PHYSICALWRITE) / SUM(TIMEINTERVAL) / 1024 / 1024, 2) AS 平均PHYSICALWRITE
  from (SELECT A.*,
               DBTIME_E.VALUE - DBTIME_S.VALUE AS DBTIME,
               DBCPU_E.VALUE - DBCPU_S.VALUE AS DBCPU,
               REDOSIZE_E.VALUE - REDOSIZE_S.VALUE AS REDOSIZE,
               PHYSICALREAD_E.VALUE - PHYSICALREAD_S.VALUE AS PHYSICALREAD,
               PHYSICALWRITE_E.VALUE - PHYSICALWRITE_S.VALUE AS PHYSICALWRITE
          FROM (SELECT a.INSTANCE_NUMBER,
                       to_number(to_char(A.BEGIN_INTERVAL_TIME, 'yyyymmdd')) AS BEGIN_DATE,
                       TRUNC(((A.BEGIN_INTERVAL_TIME + 0) -
                             trunc(A.BEGIN_INTERVAL_TIME + 0)) * 48) AS SNAPORDER,
                       SUBSTR('0' ||
                              TRUNC(TRUNC(((A.BEGIN_INTERVAL_TIME + 0) -
                                          trunc(A.BEGIN_INTERVAL_TIME + 0)) * 48) / 2) ||
                              (CASE
                                WHEN mod(TRUNC(((A.BEGIN_INTERVAL_TIME + 0) -
                                               trunc(A.BEGIN_INTERVAL_TIME + 0)) * 48),
                                         2) = 1 THEN
                                 '30'
                                ELSE
                                 '00'
                              END),
                              -4) AS BEGIN_TIME,
                       A.BEGIN_INTERVAL_TIME + 0 AS BEGIN_INTERVAL_TIME,
                       A.END_INTERVAL_TIME + 0 AS END_INTERVAL_TIME,
                       ((A.END_INTERVAL_TIME + 0) -
                       (A.BEGIN_INTERVAL_TIME + 0)) * 60 * 60 * 24 AS TIMEINTERVAL,
                       B.SNAP_ID AS STARTSNAP_ID,
                       A.SNAP_ID AS ENDSNAP_ID
                  FROM DBA_HIST_SNAPSHOT A
                 INNER JOIN DBA_HIST_SNAPSHOT B /*寻找前一个SNAP_ID*/
                    ON A.SNAP_ID - 1 = B.SNAP_ID
                   AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
                 ORDER BY A.SNAP_ID) A
          LEFT OUTER JOIN DBA_HIST_SYS_TIME_MODEL DBTIME_S
            ON a.STARTSNAP_ID = DBTIME_S.SNAP_ID
           AND A.INSTANCE_NUMBER = DBTIME_S.INSTANCE_NUMBER
           AND DBTIME_S.STAT_NAME = 'DB time'
          LEFT OUTER JOIN DBA_HIST_SYS_TIME_MODEL DBTIME_E
            ON a.ENDSNAP_ID = DBTIME_E.SNAP_ID
           AND A.INSTANCE_NUMBER = DBTIME_E.INSTANCE_NUMBER
           AND DBTIME_E.STAT_NAME = 'DB time'
          LEFT OUTER JOIN DBA_HIST_SYS_TIME_MODEL DBCPU_S
            ON a.STARTSNAP_ID = DBCPU_S.SNAP_ID
           AND A.INSTANCE_NUMBER = DBCPU_S.INSTANCE_NUMBER
           AND DBCPU_S.STAT_NAME = 'DB CPU'
          LEFT OUTER JOIN DBA_HIST_SYS_TIME_MODEL DBCPU_E
            ON a.ENDSNAP_ID = DBCPU_E.SNAP_ID
           AND A.INSTANCE_NUMBER = DBCPU_E.INSTANCE_NUMBER
           AND DBCPU_E.STAT_NAME = 'DB CPU'
          LEFT OUTER JOIN DBA_HIST_SYSSTAT REDOSIZE_S
            ON A.STARTSNAP_ID = REDOSIZE_S.SNAP_ID
           AND A.INSTANCE_NUMBER = REDOSIZE_S.INSTANCE_NUMBER
           AND REDOSIZE_S.STAT_NAME = 'redo size'
          LEFT OUTER JOIN DBA_HIST_SYSSTAT REDOSIZE_E
            ON A.ENDSNAP_ID = REDOSIZE_E.SNAP_ID
           AND A.INSTANCE_NUMBER = REDOSIZE_E.INSTANCE_NUMBER
           AND REDOSIZE_E.STAT_NAME = 'redo size'
          LEFT OUTER JOIN DBA_HIST_SYSSTAT PHYSICALREAD_S
            ON A.STARTSNAP_ID = PHYSICALREAD_S.SNAP_ID
           AND A.INSTANCE_NUMBER = PHYSICALREAD_S.INSTANCE_NUMBER
           AND PHYSICALREAD_S.STAT_NAME = 'physical read bytes'
          LEFT OUTER JOIN DBA_HIST_SYSSTAT PHYSICALREAD_E
            ON A.ENDSNAP_ID = PHYSICALREAD_E.SNAP_ID
           AND A.INSTANCE_NUMBER = PHYSICALREAD_E.INSTANCE_NUMBER
           AND PHYSICALREAD_E.STAT_NAME = 'physical read bytes'
          LEFT OUTER JOIN DBA_HIST_SYSSTAT PHYSICALWRITE_S
            ON A.STARTSNAP_ID = PHYSICALWRITE_S.SNAP_ID
           AND A.INSTANCE_NUMBER = PHYSICALWRITE_S.INSTANCE_NUMBER
           AND PHYSICALWRITE_S.STAT_NAME = 'physical write bytes'
          LEFT OUTER JOIN DBA_HIST_SYSSTAT PHYSICALWRITE_E
            ON A.ENDSNAP_ID = PHYSICALWRITE_E.SNAP_ID
           AND A.INSTANCE_NUMBER = PHYSICALWRITE_E.INSTANCE_NUMBER
           AND PHYSICALWRITE_E.STAT_NAME = 'physical write bytes'
           AND DBTIME_E.VALUE - DBTIME_S.VALUE > 0 /*重启可能导致重新累积，不加入这些异常数据*/
        --WHERE A.INSTANCE_NUMBER=1
         ORDER BY A.STARTSNAP_ID) SYSTEMLOAD
 WHERE INSTANCE_NUMBER = 1
   AND BEGIN_DATE = '20210910'
 GROUP BY BEGIN_DATE, SNAPORDER
 ORDER BY BEGIN_DATE, SNAPORDER
```
## 空间
```sql
--非系统的表
  SELECT (A.BYTES / 1024 / 1024) AS SIZE_MB,
         A.owner,
         case
           when A.segment_type = 'LOBSEGMENT' then
            (SELECT B.TABLE_NAME || '【' || B.COLUMN_NAME||'】'
               FROM DBA_LOBS B
              WHERE A.owner = B.OWNER
                AND A.segment_name = B.SEGMENT_NAME)
           else
            A.segment_name
         end as segment_name，
         a.segment_type
    FROM DBA_SEGMENTS A
   WHERE A.owner IN ('JHEMR', 'JHFILE','JHCDR','JHLCP','JHNIS','JHCDAS')
   ORDER BY BYTES DESC
--系统
 SELECT (A.BYTES / 1024 / 1024) AS SIZE_MB,
        A.owner,
        case
          when A.segment_type = 'LOBSEGMENT' then
           (SELECT B.TABLE_NAME || '【' || B.COLUMN_NAME || '】'
              FROM DBA_LOBS B
             WHERE A.owner = B.OWNER
               AND A.segment_name = B.SEGMENT_NAME)
          else
           A.segment_name
        end as segment_name， a.segment_type
   FROM DBA_SEGMENTS A
  where a.tablespace_name = 'SYSTEM'
  ORDER BY BYTES DESC;
```
# 数据库SQL执行计划
```sql
select * from table (dbms_xplan.display_cursor('f5w4nakg4rd42' , null, 'ALLSTATS LAST cost'));
```

# 数据库主键、索引
## 主键
```sql
select t.constraint_name,
       t.table_name,
       (select  listagg(a.column_name, ',') within group(order by a.position asc) table_name
          from dba_cons_columns a
         where a.constraint_name not like 'BIN$%' and a.constraint_name=t.constraint_name and a.table_name=t.table_name and a.owner=t.owner) COLS
  from dba_constraints t
 where t.CONSTRAINT_TYPE = 'P'
   and t.TABLE_NAME not like 'BIN$%'
   and t.owner in('DATACENTER','JHFILE','JHCDR','JHNIS','JHLCPV6','REPORT')
   order by t.table_name;
```
## 索引
```sql
   select t.index_name,
       t.table_name,
       (select  listagg(a.column_name, ',') within group(order by a.COLUMN_POSITION asc) table_name
          from dba_ind_columns a
         where a.TABLE_NAME not like 'BIN$%' and a.INDEX_NAME=t.index_name and a.table_name=t.table_name and a.TABLE_OWNER=t.table_owner) COLS
  from dba_indexes t
 where  t.index_name not like 'SYS_IL%'
   and t.table_owner in('DATACENTER','JHFILE','JHCDR','JHNIS','JHLCPV6','REPORT')
    order by t.table_name; --索引
```

## 查询所有没有不是PK打头的主键
```sql
select t.constraint_name,
       t.table_name,
       (select  listagg(a.column_name, ',') within group(order by a.position asc) table_name
          from dba_cons_columns a
         where a.constraint_name not like 'BIN$%' and a.constraint_name=t.constraint_name and a.table_name=t.table_name and a.owner=t.owner)
  from dba_constraints t
 where t.CONSTRAINT_TYPE = 'P'
   and t.TABLE_NAME not like 'BIN$%'
  and t.constraint_name not like 'PK%'
   and t.owner in('DATACENTER','JHFILE','JHCDR','JHNIS','JHLCPV6','REPORT'); 
```