[ORACLE] management script collection

----------------------------- Cryking original ------------------------------
----------------------- Please indicate the source for reprinting. Thank you! ------------------------

1. query AWR-related view names

SELECT table_name  FROM dba_tables t WHERE table_name LIKE 'WRH$%'       AND NOT EXISTS (SELECT 'x'          FROM dba_tab_columns c         WHERE c.column_name = 'SNAP_ID'               AND c.table_name = t.table_name);

2. CPU time consumption Query

with AASSTAT as (           select                 decode(n.wait_class,'User I/O','User I/O',                                     'Commit','Commit',                                     'Wait')                               CLASS,                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS,                 BEGIN_TIME ,                 END_TIME           from  v$waitclassmetric  m,                 v$system_wait_class n           where m.wait_class_id=n.wait_class_id             and n.wait_class != 'Idle'           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME          union             select 'CPU_ORA_CONSUMED'                                     CLASS,                    round(value/100,3)                                     AAS,                 BEGIN_TIME ,                 END_TIME             from v$sysmetric             where metric_name='CPU Usage Per Sec'               and group_id=2          union            select 'CPU_OS'                                                CLASS ,                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS,                 BEGIN_TIME ,                 END_TIME            from              ( select value busy, BEGIN_TIME,END_TIME from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter          union             select               'CPU_ORA_DEMAND'                                            CLASS,               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS,               cast(min(SAMPLE_TIME) as date) BEGIN_TIME ,               cast(max(SAMPLE_TIME) as date) END_TIME             from v$active_session_history ash              where SAMPLE_TIME >= (select BEGIN_TIME from v$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )               and SAMPLE_TIME < (select END_TIME from v$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 ))select       to_char(BEGIN_TIME,'HH:MI:SS') BEGIN_TIME,       to_char(END_TIME,'HH:MI:SS') END_TIME,       ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +       CPU_ORA_CONSUMED +        decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,       decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,       CPU_ORA_CONSUMED CPU_ORA,       decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,       COMMIT,       READIO,       WAITfrom (select       min(BEGIN_TIME) BEGIN_TIME,       max(END_TIME) END_TIME,       sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,       sum(decode(CLASS,'CPU_ORA_DEMAND'  ,AAS,0)) CPU_ORA_DEMAND,       sum(decode(CLASS,'CPU_OS'          ,AAS,0)) CPU_OS,       sum(decode(CLASS,'Commit'          ,AAS,0)) COMMIT,       sum(decode(CLASS,'User I/O'        ,AAS,0)) READIO,       sum(decode(CLASS,'Wait'            ,AAS,0)) WAITfrom AASSTAT)

3. Wait for event information

select     sid sw_sid,     CASE WHEN state != 'WAITING' THEN 'WORKING'         ELSE 'WAITING'    END AS state,     CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'         ELSE event    END AS sw_event,     seq#,     seconds_in_wait sec_in_wait,     CASE state WHEN 'WAITING' THEN NVL2(p1text,p1text||'= ',null)||CASE WHEN P1 < 536870912 THEN to_char(P1) ELSE '0x'||rawtohex(P1RAW) END ELSE null END SW_P1,    CASE state WHEN 'WAITING' THEN NVL2(p2text,p2text||'= ',null)||CASE WHEN P2 < 536870912 THEN to_char(P2) ELSE '0x'||rawtohex(P2RAW) END ELSE null END SW_P2,    CASE state WHEN 'WAITING' THEN NVL2(p3text,p3text||'= ',null)||CASE WHEN P3 < 536870912 THEN to_char(P3) ELSE '0x'||rawtohex(P3RAW) END ELSE null END SW_P3,    CASE state WHEN 'WAITING' THEN         CASE             WHEN event like 'cursor:%' THEN                '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))                    WHEN (event like 'enq%' OR event = 'DFS lock handle') AND state = 'WAITING' THEN                 '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||                chr(bitand(p1, -16777216)/16777215)||                chr(bitand(p1,16711680)/65535)||                ' mode '||bitand(p1, power(2,14)-1)            WHEN event like 'latch%' AND state = 'WAITING' THEN                   '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||(                        select name||'[par'                             from v$latch_parent                             where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))                        union all                        select name||'[c'||child#||']'                             from v$latch_children                             where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))                  )            WHEN event like 'library cache pin' THEN                  '0x'||RAWTOHEX(p1raw)        ELSE NULL END     ELSE NULL END AS sw_p1translFROM     v$session_wait ORDER BY    state,    sw_event,    p1,    p2,    p3;

4. query the connection information of the current listener

SELECT host_short || '.' || sid || '=    (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = ' || hostname || ')(PORT = ' || port || '))    (CONNECT_DATA =      (SERVER = DEDICATED)      ' || CASE         WHEN instr(service_name, 'XDB') > 0 THEN          NULL         ELSE          '(SERVICE_NAME = ' || service_name || ')'       END || '(SID = ' || SID || ')    ))  '  FROM (SELECT --target_name,         UPPER(host_name) hostname        ,UPPER(SUBSTR(t.host_name, 1, INSTR(t.host_name, '.') - 1)) HOST_SHORT        ,(SUBSTR(t.host_name                ,INSTR(t.host_name, '.') + 1                ,LENGTH(t.host_name))) DOMAIN        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'Port'                 AND p.target_guid = t.target_guid) port        ,'sys/anything@' || host_name || ':' ||         (SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'Port'                 AND p.target_guid = t.target_guid) || '/' ||         (SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'ServiceName'                 AND p.target_guid = t.target_guid) || ' as sysdba' Connection_string        ,(SELECT tp.property_value            FROM mgmt$target_properties tp           WHERE tp.target_type = 'host'                 AND tp.property_name = 'IP_address'                 AND tp.target_name = t.host_name) ip        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'DBVersion'                 AND p.target_guid = t.target_guid) DB_Version        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'OracleHome'                 AND p.target_guid = t.target_guid) oh        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'ServiceName'                 AND p.target_guid = t.target_guid) Service_name        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'log_archive_mode'                 AND p.target_guid = t.target_guid) logmode        ,UPPER((SELECT p.property_value                 FROM mgmt$target_properties p                WHERE p.property_name = 'SID'                      AND p.target_guid = t.target_guid)) sid        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'CPUCount'                 AND p.target_guid = t.target_guid) CPU        ,ROUND(SYSDATE - TO_DATE((SELECT p.property_value                                   FROM mgmt$target_properties p                                  WHERE p.property_name = 'StartTime'                                        AND p.target_guid = t.target_guid)                                ,'YYYY-MM-DD HH24:MI:SS')              ,0) Days_Uptime        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'StartTime'                 AND p.target_guid = t.target_guid) Uptime        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'VersionCategory'                 AND p.target_guid = t.target_guid) VersionCategory        ,(SELECT p.property_value            FROM mgmt$target_properties p           WHERE p.property_name = 'VersionBanner'                 AND p.target_guid = t.target_guid) VersionBanner        ,CASE           WHEN (INSTR((SELECT UPPER(p.property_value)                         FROM mgmt$target_properties p                        WHERE p.property_name = 'VersionBanner'                              AND p.target_guid = t.target_guid)                      ,'ENTERPRISE')) > 0 THEN            'Enterprise'           ELSE            'Standard/Standard One'         END Edition        ,(SELECT b.VALUE            FROM MGMT$ECM_VISIBLE_SNAPSHOTS     A                ,SYSMAN.MGMT_DB_INIT_PARAMS_ECM B           WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID                 AND a.TARGET_TYPE = 'oracle_database'                 AND b.name = 'control_file_record_keep_time'                 AND a.target_guid = t.target_guid) control_file_record_keep_time        ,(SELECT b.VALUE            FROM MGMT$ECM_VISIBLE_SNAPSHOTS     A                ,SYSMAN.MGMT_DB_INIT_PARAMS_ECM B           WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID                 AND a.TARGET_TYPE = 'oracle_database'                 AND b.name = 'optimizer_features_enable'                 AND a.target_guid = t.target_guid) optimizer_features_enable        ,(SELECT ROUND(b.VALUE / 1024 / 1024 / 1024, 2)            FROM MGMT$ECM_VISIBLE_SNAPSHOTS     A                ,SYSMAN.MGMT_DB_INIT_PARAMS_ECM B           WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID                 AND a.TARGET_TYPE = 'oracle_database'                 AND b.name = 'memory_target'                 AND a.target_guid = t.target_guid) memory_target        ,(SELECT sessions_highwater            FROM MGMT$ECM_VISIBLE_SNAPSHOTS A                ,SYSMAN.MGMT_DB_license_ECM B           WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID                 AND TARGET_TYPE = 'oracle_database'                 AND a.target_guid = t.target_guid) sessions_highwater        ,(SELECT sessions_current            FROM MGMT$ECM_VISIBLE_SNAPSHOTS A                ,SYSMAN.MGMT_DB_license_ECM B           WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID                 AND TARGET_TYPE = 'oracle_database'                 AND a.target_guid = t.target_guid) sessions_current          FROM mgmt$target t         WHERE t.target_type IN ('oracle_database')) Raw_data ORDER BY host_short         ,sid;