[ORACLE] Management-related script collection

-----------------------------cryking Original ------------------------------
----------------------- Reprint Please specify the source, thank you ! ------------------------

1. Query the name of the AWR-related view

Select table_name from  dba_tables T WHERE table_name like ' wrh$% ' and not       EXISTS (select ' x ' from          Dba_tab_colu MNS C         WHERE c.column_name = ' snap_id ' and               c.table_name = t.table_name);

2. CPU time-consuming 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 ', ' W Ait '), 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 V              Alue 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 (Sampl E_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 E Nd_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_c       Har (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, Su M (Decode (class, ' cpu_ora_consumed ', aas,0)) cpu_ora_consumed, SUM (Decode (class, ' Cpu_ora_demand ', aas,0)) Cpu_ora_dem       And, 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 is state! = ' waiting ' Then ' working ' ELSE ' waiting ' END as state, case WH EN state! = ' waiting ' Then ' on Cpu/runqueue ' ELSE event END as Sw_event, seq#, seconds_in_wait sec_in _wait, case State "Waiting" then NVL2 (p1text,p1text| | ' = ', null) | | case where P1 < 536870912 then To_char (P1) ELSE ' 0x ' | | Rawtohex (p1raw) end ELSE null END SW_P1, case state if ' waiting ' then NVL2 (p2text,p2text| | ' = ', null) | | case where P2 < 536870912 then To_char (P2) ELSE ' 0x ' | | Rawtohex (p2raw) end ELSE null END SW_P2, case state if ' waiting ' then NVL2 (p3text,p3text| | ' = ', null) | | case where P3 < 536870912 then To_char (P3) ELSE ' 0x ' | | Rawtohex (p3raw) end ELSE null END SW_P3, case state when ' waiting ' and case is event like ' Curso r:% ' Then ' 0x ' | |  Trim (To_char (P1, ' xxxxxxxxxxxxxxxx ')) when (the event like ' enq% ' OR event = ' DFS lock handle ') and the 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 the 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,RP AD (' 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 the event like ' Library cache pin ' and ' 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 current listening connection information

SELECT Host_short | | '.' || Sid | | ' = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ' | | hostname | | ') (port = ' | | | port | | ')) (Connect_data = (SERVER = dedicated) ' | | case where 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.ho St_name, '. ') -1)) Host_short, (SUBSTR (T.host_name, INSTR (T.host_name, '. ') + 1, LENGTH (T.host_nam e))) DOMAIN, (SELECT p.property_value from mgmt$target_properties p WHERE p.property_name = ' P Ort ' and p.target_guid = t.target_guid) port, ' sys/[email protected] ' | | 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_n AME) IP, (SELECT p.property_value from mgmt$target_properties p WHERE p.property_name = ' Dbver Sion ' 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_n Ame = ' 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_n                                Ame = ' StartTime ' and p.target_guid = T.target_guid) , ' Yyyy-mm-dd HH24:MI:SS '), 0) Days_uptime, (SELECT p.property_value from Mgmt$target_prope Rties P WHEREP.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_g UID = t.target_guid) versioncategory, (SELECT p.property_value from Mgmt$target_properties p W           Here P.property_name = ' Versionbanner ' and p.target_guid = t.target_guid) Versionbanner, case                        When (The 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 E Dition, (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 = ' Co Ntrol_file_record_keep_time ' and a.target_guid = T.target_guid) control_file_record_keep_time, (SEL ECT 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 = ' Oracl E_database ' and b.name = ' optimizer_features_enable ' and A.target_guid = t.target_guid) op     Timizer_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 = ' Oracl        E_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_data Base ' and A.target_guid = T.target_guid) Sessions_highwater, (SELECT sessions_current FR OM mgmt$ecm_visible_snapshots A, Sysman. MGMT_DB_LICENSE_ECM B WHERE a.ecm_snapshot_id = b.ecm_snapshot_id and Target_type = ' Oracle_data Base ' and A.target_guid = T.target_guid) sessions_current from Mgmt$target t WHERE t.targ Et_type in (' Oracle_database ')) Raw_data ORDER by Host_short, Sid;

More DBA scripts see a foreigner's blog:http://www.idevelopment.info/cgi/oracle_dba_scripts.cgi