# OWBr10g.def: Collects Oracle Warehouse Builder Information (9.2, 10.1, 10.2) # $Id: OWBr10g.def,v 1.11 2008/02/25 17:32:55 mschenke Exp $ # ARCS: $Header: /home/cvs/repository/RDA_4/src/scripting/lib/modules/OWBr10g.def,v 1.11 2008/02/25 17:32:55 mschenke Exp $ # # Change History # 20080225 MSC Change the copyright notice. =head1 NAME OWBr10g - Collects Oracle Warehouse Builder Information (9.2, 10.1, or 10.2) =head1 DESCRIPTION This module collects Oracle Warehouse Builder-related information (9.2, 10.1, or 10.2). =cut # Initialisation import $TOC,$TOP # Load the common macros run DBinfo() # Check if required tables are present debug ' Inside OWB module, checking 9.2, 10.1, or 10.2 repository objects' set $sql {SELECT DECODE(COUNT(*),0,'NOTFOUND','PRESENT') " FROM all_objects " WHERE object_name IN ('ALL_IV_INSTALLATIONS','WB_RTV_SERVICE_NODES') " AND OWNER != 'OWBSYS'; } if grepSql($sql,'NOTFOUND') {echo 'No (9.2, 10.1, or 10.2) design or runtime repositories found' return } =head2 report10g - Repository Information (9.2, 10.1, or 10.2) Gathers release-related information. =cut debug ' Inside OWB module, getting 9.2, 10.1, or 10.2 repository information' report report10g var $TTL = '---+!! Repository Information (9.2, 10.1, or 10.2)' var @TTL = ('',\ '---+ Design Repositories',\ '---+ Design Repository Multi User Environment',\ '---+ Design Repository Size',\ '---+ Design Repository Statistics Detection',\ '---+ Runtime Repositories',\ '---+ Runtime Access Users',\ '---+ Runtime Repository Size',\ '---+ Runtime Repository Statistics Detection',\ '',\ '---+ OWB OWBRT_SYS.OWBRTPS',\ '---+ OWB Targets',\ '---+ OWB WB_RT_Service_Job_Logs',\ '---+ OWB Locations Registered',\ '---+ Enqueue Resources') var @HDR = () set $sql {SET serveroutput on "DECLARE " l_dat DATE; " l_ins VARCHAR2(15); " l_own VARCHAR2(30); " l_rel VARCHAR2(15); " l_sql VARCHAR2(200) := NULL; " l_ver VARCHAR2(10); " " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'ALL_IV_INSTALLATIONS' " AND owner != 'PUBLIC'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Design Repositories installed|'); " ELSE " dbms_output.put_line('|*Owner*|*Version*|*Release*|*Creation Date*|'); " LOOP " l_sql := 'SELECT installed_version,release,created_on " FROM ' || l_own || '.all_iv_installations'; " EXECUTE IMMEDIATE l_sql " INTO l_ins,l_rel,l_dat; " dbms_output.put_line('|' || " l_own || ' |' || " l_ins || ' |' || " l_rel || ' |' || " TO_CHAR(l_dat,'DD-Mon-YYYY') || ' |'); " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(2)___ "DECLARE " l_own VARCHAR2(30); " l_rep VARCHAR2(25); " l_usr VARCHAR2(25); " " CURSOR c_own IS " SELECT DISTINCT grantee, " SUBSTR(granted_role,5,25) " FROM dba_role_privs " WHERE SUBSTR(granted_role,1,4) = 'OWB_' " AND SUBSTR(granted_role,1,6) NOT IN ('OWB_A_','OWB_D_','OWB_O_','OWB_R_') " AND default_role = 'NO' " AND admin_option = 'NO'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_usr,l_rep; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line( " '|No OWB Design Users installed (no multi user environment)|'); " ELSE " dbms_output.put_line('|*Owner Design Repository*|*User Design Repository*|'); " LOOP " dbms_output.put_line('|' || l_rep || ' |' || l_usr || ' |'); " FETCH c_own " INTO l_usr,l_rep; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(3)___ "DECLARE " l_cnt NUMBER; " l_flg BOOLEAN := TRUE; " l_sql VARCHAR2(200) := NULL; " l_tbl dba_tables.table_name%TYPE; " " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'ALL_IV_INSTALLATIONS' " AND owner != 'PUBLIC'; " CURSOR c_tbl(p_own VARCHAR2) IS " SELECT table_name " FROM dba_tables " WHERE table_name IN ('CMPALLCLASSES','CMPMMMCLASSES') " AND owner = p_own " ORDER BY table_name; "BEGIN " FOR r1 IN c_own LOOP " OPEN c_tbl(r1.owner); " FETCH c_tbl " INTO l_tbl; " IF c_tbl%FOUND " THEN " l_sql := 'SELECT COUNT(*) " FROM ' || r1.owner || '.' || l_tbl; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " IF l_flg " THEN " dbms_output.put_line('|*Owner*| *Rows*|'); " l_flg := FALSE; " END IF; " dbms_output.put_line('|' || " r1.owner || ' ' || LOWER(l_tbl) || '| ' || " l_cnt || '|'); " END IF; " CLOSE c_tbl; " END LOOP; " IF l_flg " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Design Repositories installed (Size)|'); " END IF; "END; "/ "PROMPT ___Macro_separator(4)___ "DECLARE " l_idx NUMBER; " l_flg BOOLEAN := FALSE; " l_own VARCHAR2(30); " l_sql VARCHAR2(200) := NULL; " l_tbl NUMBER; " " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'ALL_IV_INSTALLATIONS' " AND owner != 'PUBLIC'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Design Repositories installed|'); " ELSE " dbms_output.put_line('|*Owner*|*Description*|'); " LOOP " l_sql := 'SELECT SUM(num_rows) " FROM dba_tables " WHERE owner = ''' || l_own || ''''; " EXECUTE IMMEDIATE l_sql " INTO l_tbl; " IF l_tbl > 0 " THEN " dbms_output.put_line('|' || l_own || ' |Statistics detected on tables.|'); " l_flg := TRUE; " END IF; " l_sql := 'SELECT SUM(num_rows) " FROM dba_indexes " WHERE owner = ''' || l_own || ''''; " EXECUTE IMMEDIATE l_sql " INTO l_idx; " IF l_idx > 0 " THEN " dbms_output.put_line('|' || l_own || ' |Statistics detected on indexes.|'); " l_flg := TRUE; " END IF; " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " IF NOT l_flg " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line( " '|The OWB Design Repositories are free from statistics|'); " END IF; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(5)___ "DECLARE " l_act NUMBER(2); " l_ena NUMBER(2); " l_flg VARCHAR2(1); " l_hom VARCHAR2(80); " l_hst VARCHAR2(30); " l_ins NUMBER(2); " l_lck VARCHAR2(128); " l_lim INTEGER := 0; " l_nam VARCHAR2(50); " l_nod NUMBER(2); " l_own VARCHAR2(30); " l_prt VARCHAR2(5); " l_sql VARCHAR2(255) := NULL; " l_sta NUMBER; " l_svc VARCHAR2(30); " l_ver VARCHAR2(11); " " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'WB_RTV_SERVICE_NODES'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Runtime Repositories installed|'); " ELSE " dbms_output.enable(100000); " dbms_output.put_line('|*Owner*| *Available* | *Node*| *Inst*|*Host*| ' || " '*Port*|*Service Name*| *Enabled*|*RT Version*|' || " '*Server Side Home*|*Instance Name*| ' || " '*Currently Active*|'); " LOOP " l_sql := 'SELECT node_id, " instance_number, " host, " port,service_name, " enabled, " runtime_version, " server_side_home, " instance_name, " currently_active " FROM ' || l_own || '.wb_rtv_service_nodes'; " OPEN c_dyn FOR l_sql; " LOOP " FETCH c_dyn " INTO l_nod,l_ins,l_hst,l_prt,l_svc,l_ena,l_ver,l_hom,l_nam,l_act; " EXIT WHEN c_dyn%NOTFOUND; " dbms_lock.allocate_unique('WBLK_' || l_own,l_lck); " l_sta := dbms_lock.request(l_lck,dbms_lock.x_mode,l_lim,TRUE); " IF l_sta = 0 " THEN " l_sta := dbms_lock.release(l_lck); " l_flg := 'N'; " ELSE " l_flg := 'Y'; " END IF; " dbms_output.put_line('|' || " l_own || ' | ' || " l_flg || ' | ' || " l_nod || '| ' || " l_ins || '|' || " l_hst || ' | ' || " l_prt || '|' || " l_svc || ' | ' || " l_ena || '|' || " l_ver || ' |' || " l_hom || ' |' || " l_nam || ' | ' || " l_act || '|'); " END LOOP; " CLOSE c_dyn; " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(6)___ "DECLARE " l_own VARCHAR2(30); " l_rep VARCHAR2(25); " l_usr VARCHAR2(25); " " CURSOR c_own IS " SELECT DISTINCT grantee, " SUBSTR(granted_role,6,25) rt_owner " FROM dba_role_privs " WHERE SUBSTR(granted_role,1,3) = 'WB_' " AND default_role = 'NO'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_usr,l_rep; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Runtime Access Users installed|'); " ELSE " dbms_output.put_line('|*Runtime Access User* |*Connected To Runtime* |'); " LOOP " dbms_output.put_line('|' || l_usr || ' |' || l_rep || ' |'); " FETCH c_own " INTO l_usr,l_rep; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(7)___ "DECLARE " l_chk NUMBER; " l_cnt NUMBER; " l_sql VARCHAR2(200) := NULL; " l_own VARCHAR2(30); " " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'WB_RT_SERVICE_NODES' " AND owner != 'OWBSYS'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Runtime Repositories installed|'); " ELSE " dbms_output.enable(100000); " dbms_output.put_line('|*Owner* |*Activity* | *Rows*|'); " LOOP " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_stores'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_stores " WHERE store_name = ''PlatformSchema'''; " EXECUTE IMMEDIATE l_sql " INTO l_chk; " IF l_chk = 1 " THEN " dbms_output.put_line('|' || " l_own || ' |wb_rt_stores| ' || " l_cnt || ' rows PlatformSchema seeded correctly|'); " ELSE " dbms_output.put_line('|' || " l_own || ' |wb_rt_stores| ' || " l_cnt || ' rows PlatformSchema not seeded|'); " END IF; " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_platform_properties'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " IF l_cnt > 2 " THEN " dbms_output.put_line('|' || " l_own || ' |wb_rt_platform_properties| ' || " l_cnt || ' rows Platform properties seeded correctly|'); " ELSE " dbms_output.put_line('|' || " l_own || ' |wb_rt_platform_properties| ' || " l_cnt || ' rows Not all platform properties loaded|'); " END IF; " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_audit_executions'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_audit_executions| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_audit_deployments'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_audit_deployments| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_tasks'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_tasks| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_deployment_adapters'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_deployment_adapters| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_execution_adapters'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_execution_adapters| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_execution_operators'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_execution_operators| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_file_types'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_file_types| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_object_types'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_object_types| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_object_type_defs'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_object_type_defs| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_operator_results'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_operator_results| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_store_types'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_store_types| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_store_type_params'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_store_type_params| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_system_parameters'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_system_parameters| ' || " l_cnt || '|'); " l_sql := 'SELECT COUNT(*) " FROM dba_tables " WHERE table_name = ''WB_RT_DEF_IMPL_TYPES'' " AND OWNER = ''' || l_own || ''''; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " IF l_cnt = 1 " THEN " -- This is Paris " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_impl_types'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_impl_types| ' || " l_cnt || '|'); " END IF; " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_def_system_parameters'; " EXECUTE IMMEDIATE l_sql " INTO l_cnt; " dbms_output.put_line('|' || " l_own || ' |wb_rt_def_system_parameters| ' || " l_cnt || '|'); " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(8)___ "DECLARE " l_flg BOOLEAN := FALSE; " l_idx NUMBER; " l_own VARCHAR2(30); " l_sql VARCHAR2(200) := NULL; " l_tbl NUMBER; " " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'WB_RT_SERVICE_NODES'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Design Repositories installed|'); " ELSE " dbms_output.put_line('|*Owner*|*Description*|'); " LOOP " l_sql := 'SELECT SUM(num_rows) " FROM dba_tables " WHERE owner = ''' || l_own || ''''; " EXECUTE IMMEDIATE l_sql " INTO l_tbl; " IF l_tbl > 0 " THEN " dbms_output.put_line('|' || l_own || ' |Statistics detected on tables.|'); " l_flg := TRUE; " END IF; " l_sql := 'SELECT SUM(num_rows) " FROM dba_indexes " WHERE owner = ''' || l_own || ''''; " EXECUTE IMMEDIATE l_sql " INTO l_idx; " IF l_idx > 0 " THEN " dbms_output.put_line('|' || l_own || ' |Statistics detected on indexes.|'); " l_flg := TRUE; " END IF; " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " IF NOT l_flg " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line( " '|The OWB Runtime Repositories are free from statistics|'); " END IF; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(10)___ "DECLARE " l_cnt NUMBER; " l_key VARCHAR2(35); " l_sql VARCHAR2(255); " l_val VARCHAR2(255); " " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; "BEGIN " SELECT COUNT(*) " INTO l_cnt " FROM dba_objects " WHERE object_name = 'OWBRTPS' " AND OWNER = 'OWBRT_SYS'; " IF l_cnt = 0 " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|OWBRT_SYS.OWBRTPS not found|'); " ELSE " l_sql := 'SELECT key,value " FROM owbrt_sys.owbrtps " ORDER BY key'; " dbms_output.put_line('|*Key*|*Value*|'); " OPEN c_dyn FOR l_sql; " LOOP " FETCH c_dyn " INTO l_key,l_val; " EXIT WHEN c_dyn%NOTFOUND; " dbms_output.put_line('|' || l_key || ' |' || l_val || '|'); " END LOOP; " CLOSE c_dyn; " END IF; "END; "/ "PROMPT ___Macro_separator(11)___ "DECLARE " l_own VARCHAR2(30); " l_rep VARCHAR2(30); " l_sql VARCHAR2(200) := NULL; " l_ver VARCHAR2(11); " " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'WB_RT_PLATFORM_REPOSITORY' " AND owner NOT IN (SELECT owner " FROM dba_objects " WHERE OBJECT_NAME = 'CMPMMMCLASSES'); "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Target Schemas installed|'); " ELSE " dbms_output.put_line( " '|*Target*|*RTP/Control Center Service*|*RT Version*|'); " LOOP " l_sql := 'SELECT ' || l_own || '.wb_rt_platform_repository,' || " l_own || '.wb_rt_version " FROM sys.dual'; " EXECUTE IMMEDIATE l_sql " INTO l_rep,l_ver; " dbms_output.put_line('|' || " l_own || ' |' || " l_rep || ' |' || " l_ver || ' |'); " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(12)___ "DECLARE " l_cnt NUMBER; " l_max NUMBER; " l_msg VARCHAR2(2000); " l_off NUMBER; " l_own VARCHAR2(30) ; " l_sql VARCHAR2(400) := NULL; " l_tim VARCHAR2(30); " " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'WB_RT_SERVICE_JOB_LOGS'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Runtime Repositories installed|'); " ELSE " dbms_output.enable(300000); " dbms_output.put_line('|*Owner*|*Time Stamp*|*File Path*|*Message*|'); " LOOP " -- number of rows in wb_rt_service_job_logs " l_sql := 'SELECT COUNT(*) " FROM ' || l_own || '.wb_rt_service_job_logs'; " EXECUTE IMMEDIATE l_sql " INTO l_max; " l_sql := 'SELECT TO_CHAR(time_stamp,''DD-Mon-YYYY HH24:MI:SS'') time, " REPLACE(SUBSTR(message,9+instr(message,''command''),500), " CHR(10),''%BR%'') " FROM ' || l_own || '.wb_rt_service_job_logs " ORDER BY time_stamp'; " OPEN c_dyn FOR l_sql; " l_cnt := 0; " LOOP " FETCH c_dyn " INTO l_tim,l_msg; " EXIT WHEN c_dyn%NOTFOUND; " IF l_cnt >= l_max - 10 " THEN " -- display last 10 entries " l_off := INSTR(l_msg,'run_service'); " IF l_off > 0 " THEN " dbms_output.put_line('|' || " l_own || ' |' || " l_tim || ' |' || " SUBSTR(l_msg,1,14 + l_off) || ' |\'); " dbms_output.put_line( " SUBSTR(l_msg,15 + l_off,200) || ' |'); " ELSE " dbms_output.put_line('|' || " l_own || ' |' || " l_tim || ' |\'); " dbms_output.put_line(SUBSTR(l_msg,1,240) || ' ||'); " END IF; " END IF; " l_cnt := l_cnt + 1; " END LOOP; " CLOSE c_dyn; " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(13)___ "DECLARE " l_str_nam VARCHAR2(64); " l_loc VARCHAR2(64); " l_typ_nam VARCHAR2(64); " l_typ_ver VARCHAR2(64); " l_trg NUMBER; " l_par_nam VARCHAR2(64); " l_par_val VARCHAR2(100); " l_sql VARCHAR2(600) := NULL; " l_own VARCHAR2(30); " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; " " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'WB_RT_SERVICE_NODES' " AND owner != 'OWBSYS'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Runtime Repositories installed|'); " ELSE " dbms_output.enable(1000000); " dbms_output.put_line('|*Owner*|*Store Name*|*Store Type Name*| ' || " '*Store Type Ver*|*Parameter Name*|*Parameter Value*|'); " LOOP " l_sql := 'SELECT store_name, " REPLACE(store_type_name,'' '','' ''), " store_type_version, " parameter_name, " parameter_value " FROM ' || l_own || '.wb_rt_stores, " ' || l_own || '.wb_rt_store_parameters, " ' || l_own || '.wb_rt_def_store_types " WHERE wb_rt_stores.store_id = wb_rt_store_parameters.store_id " AND wb_rt_stores.store_type_id = " wb_rt_def_store_types.store_type_id " AND parameter_name <> ''Password'' " AND parameter_value IS NOT NULL " ORDER BY store_name,parameter_name'; " OPEN c_dyn FOR l_sql; " l_loc := 'X'; " LOOP " FETCH c_dyn " INTO l_str_nam,l_typ_nam,l_typ_ver,l_par_nam,l_par_val; " EXIT WHEN c_dyn%NOTFOUND; " IF l_loc <> l_str_nam " THEN " dbms_output.put_line('|' || " l_own || ' |' || " l_str_nam || ' |' || " l_typ_nam || ' |' || " l_typ_ver || ' |' || " l_par_nam || ' |' || " l_par_val || ' |'); " l_loc := l_str_nam; " ELSE " dbms_output.put_line('| ||||' || l_par_nam || ' |' || l_par_val || ' |'); " END IF; " END LOOP; " CLOSE c_dyn; " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " END IF; " CLOSE c_own; "END; "/ "PROMPT ___Macro_separator(14)___ "DECLARE " l_cnt NUMBER; " l_val NUMBER; "BEGIN " dbms_output.put_line('|*Description*| *Value*| *Recommended Value*|'); " SELECT COUNT(*) " INTO l_cnt " FROM v$parameter " WHERE name = 'enqueue_resources'; " IF l_cnt <> 0 " THEN " SELECT value " INTO l_val " FROM v$parameter " WHERE name = 'enqueue_resources'; " IF l_val < 3000 " THEN " dbms_output.put_line('|ENQUEUE_RESOURCES| ' || l_val || " '|Should be = 3000|'); " END IF; " END IF; "END; "/ } call separator(1) call writeSql($sql) call separator(0,'Repository Information (9.2, 10.1, or 10.2)') # OWB WB_RT_PLATFORM_REPOSITORY Information set $sql {SET serveroutput on "DECLARE " l_sql VARCHAR2(1500) := NULL; " l_own VARCHAR2(30); " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; " TYPE rec_sql IS RECORD(l_pth VARCHAR2(75),l_val VARCHAR2(75)); " TYPE tab_sql IS TABLE OF rec_sql INDEX BY BINARY_INTEGER; " dst_sql TAB_SQL; " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'WB_RT_SERVICE_NODES'; "BEGIN " OPEN c_own; " FETCH c_own " INTO l_own; " IF c_own%NOTFOUND " THEN " dbms_output.put_line('---+ RuntimePlatform Properties'); " dbms_output.put_line('|*Description*|'); " dbms_output.put_line('|No OWB Runtime Repositories installed|'); " dbms_output.put_line('[[#Top][Back to top]]'); " ELSE " dbms_output.enable(100000); " LOOP " l_sql := 'SELECT property_path,property_value " FROM ' || l_own || '.wb_rt_platform_properties " WHERE property_path IN ( " ''property.RuntimePlatform.0.delete_all_job_on_complete'', " ''property.RuntimePlatform.0.exceptions'', " ''property.RuntimePlatform.0.logfile_max_size'', " ''property.RuntimePlatform.0.max_number_logfiles'', " ''property.RuntimePlatform.0.messages'', " ''property.RuntimePlatform.0.platform'', " ''property.RuntimePlatform.0.platform_net_host_name'', " ''property.RuntimePlatform.0.platform_net_service_name'', " ''property.RuntimePlatform.0.purge_delay_hours'', " ''property.RuntimePlatform.0.purge_minimum_minutes'', " ''property.RuntimePlatform.0.recovery'', " ''property.RuntimePlatform.0.runtime_home'', " ''property.RuntimePlatform.0.service_command'', " ''property.RuntimePlatform.0.service_parameters'', " ''property.RuntimePlatform.0.temp_dir_name'', " ''property.RuntimePlatform.0.trace'', " ''property.RuntimePlatform.0.trace_control_on'', " ''property.RuntimePlatform.0.uoid'', " ''property.RuntimePlatform.0.version'', " ''property.RuntimePlatform.0.min_service_version'')'; " OPEN c_dyn FOR l_sql; " FETCH c_dyn BULK COLLECT INTO dst_sql; " IF (dst_sql.COUNT > 0) " THEN " dbms_output.put_line('---+ RuntimePlatform Properties for ' || l_own); " dbms_output.put_line('|*Property Path*|*Property Value*|'); " FOR i IN dst_sql.FIRST..dst_sql.LAST " LOOP " dbms_output.put_line('|' || " dst_sql(i).l_pth || ' |' || " dst_sql(i).l_val || ' |'); " END LOOP; " dbms_output.put_line('[[#Top][Back to top]]'); " END IF; " CLOSE c_dyn; " l_sql := 'SELECT SUBSTR(property_path,1,75),SUBSTR(property_value,1,75) " FROM ' || l_own || '.wb_rt_platform_properties " WHERE property_path LIKE " ''property.RuntimePlatform.0.NativeExecution.SQLLoader%'' " AND property_path NOT LIKE ''%oem%'''; " OPEN c_dyn FOR l_sql; " FETCH c_dyn BULK COLLECT INTO dst_sql; " IF (dst_sql.COUNT > 0) " THEN " dbms_output.put_line('---+ RuntimePlatform Properties SQLLDR for ' || " l_own); " dbms_output.put_line('|*Property Path*|*Property Value*|'); " FOR i IN dst_sql.FIRST..dst_sql.LAST " LOOP " dbms_output.put_line('|' || " dst_sql(i).l_pth || ' |' || " dst_sql(i).l_val || ' |'); " END LOOP; " dbms_output.put_line('[[#Top][Back to top]]'); " END IF; " CLOSE c_dyn; " l_sql := 'SELECT SUBSTR(property_path,1,75),SUBSTR(property_value,1,75) " FROM ' || l_own || '.wb_rt_platform_properties " WHERE property_path LIKE " ''property.RuntimePlatform.0.NativeExecution.SQLPlus%'' " AND property_path NOT LIKE ''%oem%'''; " OPEN c_dyn FOR l_sql; " FETCH c_dyn BULK COLLECT INTO dst_sql; " IF (dst_sql.COUNT > 0) " THEN " dbms_output.put_line('---+ RuntimePlatform Properties SQLPLUS for ' || " l_own); " dbms_output.put_line('|*Property Path*|*Property Value*|'); " FOR i IN dst_sql.FIRST..dst_sql.LAST " LOOP " dbms_output.put_line('|' || " dst_sql(i).l_pth || ' |' || " dst_sql(i).l_val || ' |'); " END LOOP; " dbms_output.put_line('[[#Top][Back to top]]'); " END IF; " CLOSE c_dyn; " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " END LOOP; " CLOSE c_own; " END IF; "END; "/ } call writeSql($sql) # RUN_SERVICE.SH / RUN_SERVICE.BAT set $sql {SET serveroutput on "DECLARE " l_hom VARCHAR2(100); " l_own VARCHAR2(30); " l_pth VARCHAR2(100); " l_sql VARCHAR2(200) := NULL; " l_val VARCHAR2(512); " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; " CURSOR c_own IS " SELECT owner " FROM dba_objects " WHERE object_name = 'WB_RT_SERVICE_NODES'; "BEGIN " OPEN c_own; " LOOP " FETCH c_own " INTO l_own; " EXIT WHEN c_own%NOTFOUND; " l_sql := 'SELECT server_side_home " FROM ' || l_own || '.wb_rt_service_nodes'; " OPEN c_dyn FOR l_sql; " LOOP " FETCH c_dyn " INTO l_hom; " EXIT WHEN c_dyn%NOTFOUND; " l_sql := 'SELECT property_value " FROM ' || l_own || '.wb_rt_platform_properties " WHERE property_path = " ''property.RuntimePlatform.0.service_command'''; " EXECUTE IMMEDIATE l_sql " INTO l_pth; " dbms_output.put_line(l_own || '|' || l_hom || '|' || l_pth); " END LOOP; " CLOSE c_dyn; " END LOOP; " CLOSE c_own; "END; "/ } prefix {write '---+ RUN_SERVICE.SH / RUN_SERVICE.BAT' write '|*Runtime Repository*|*Run Service*|' } call loadSql($sql) var $cnt = 0 loop $lin (getSqlLines()) {break match($lin,'(ORA-|ERROR)') var ($key,$hom,$pth) = split('\|',$lin,3) if or($hom,$pth) {if match($pth,'run_service\.sh') var $fil = catFile($hom,'owb','bin','unix','run_service.sh') else var $fil = catFile($hom,'owb','bin','win32','run_service.bat') if testFile('r',$fil) {suspend report data $key call writeData($fil) var $url = getFile('.') resume report write '|',$key,'|',concat('[[',$url,'][_blank][',$fil,']]'),'|' } else {incr $cnt write '|',$key,'|',$fil,'|' } } else write '|',$key,'| |' } if $cnt {if $OWB_LOCAL write 'Note: Files are not accessible for RDA. Permission problems or \ incorrect ``SERVER_SIDE_HOME`` in table ``WB_RT_SERVICE_NODES`` are \ possible causes.%BR%' else write 'Note: Files are not accessible for RDA. If not located on a remote \ system, permission problems or incorrect ``SERVER_SIDE_HOME`` in \ table ``WB_RT_SERVICE_NODES`` are possible causes.%BR%' } if hasOutput(true) write $TOP =begin credits =over 10 =item RDA 4.10: Sarath Babu, Jean-Philippe Peelman. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2008, Oracle. All rights reserved. =head1 TRADEMARK NOTICE Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. =cut