Oracle Applications R12: More Schema Analysis
Some more analysis of the schemas and objects in Oracle Applications R12 sample database, VIS.
Lots of objects, triggers, and packages.
And over 57 million lines of source code in the APPS schema alone!
(Again, sorry for the hard to read fonts and text. To increase the size of the font, you can always press: control-shift-+, in Windows at least. WordPress doesn’t make it easy. 😦 )
------------------------------------------------------------ ------------------------------------------------------------ Triggers by Owner: select OWNER as trigger_owner , count(*) from dba_triggers group by OWNER order by 1 TRIGGER_OWNER COUNT(*) ------------------------------ ---------- APPS 4183 <- AR 7 B2B 1528 CFD 26 D4OSYS 1 DEMO 6 FLOWS_010500 159 FLOWS_020000 182 FLOWS_030000 219 FLOWS_FILES 1 HERMAN 1 HR 3 INV 1 JTF 14 LBACSYS 4 MASTER 27 MDSYS 47 MGDSYS 1 MOBILEADMIN 81 ODM 1 OE 1 OLAPSYS 48 ORABPEL 14 ORASSO 39 PTG 4 RE 2 SCOTT 1 SYS 9 SYSTEM 2 WIRELESS 108 WKSYS 36 WK_TEST 1 XDB 21 33 rows selected. APPS has lots of triggers! -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- Schemas, triggers, and Tables Ratios select tables.owner , nvl( triggers.trigger_count, 0) as trigger_count , tables.table_count , nvl( triggers.trigger_count, 0) / tables.table_count * 100 AS trigger_ratio from ( select owner , count (*) as table_count from dba_tables group by owner ) tables , ( select TABLE_OWNER , count(*) as trigger_count from dba_triggers group by TABLE_OWNER ) triggers where tables.owner = triggers.table_owner (+) order by 1 OWNER TRIGGER_COUNT TABLE_COUNT TRIGGER_RATIO ------------------------------ ------------- ----------- ------------- ADS10GEUL_US 0 48 0 AHL 0 164 0 AK 0 59 0 AMS 0 375 0 AMV 0 42 0 AMW 0 176 0 AOLDEMO 0 6 0 AP 47 327 14.3730887 APPLSYS 49 937 5.22945571 APPS 1412 1492 94.6380697 <- AR 56 686 8.16326531 ASF 0 2 0 ASG 0 60 0 ASL 0 24 0 ASO 5 69 7.24637681 ASP 0 2 0 AST 0 11 0 AX 0 52 0 AZ 0 40 0 B2B 1528 553 276.311031 <- BAM 0 16 0 BEN 457 529 86.389414 BIC 0 26 0 BICRM 0 40 0 ... WKSYS 36 56 64.2857143 WK_TEST 1 13 7.69230769 WMS 0 133 0 WPS 0 1 0 WSH 5 128 3.90625 WSM 0 53 0 XDB 20 22 90.9090909 XDO 0 28 0 XDP 0 78 0 XLA 0 216 0 XLE 0 22 0 XNB 0 4 0 XNP 4 50 8 XTR 57 216 26.3888889 ZPB 0 200 0 ZX 0 127 0 229 rows selected. Some schemas have multiple triggers per table. ------------------------------------------------------------ ------------------------------------------------------------ Triggers by table: select TABLE_OWNER , TABLE_name , count(*) from dba_triggers group by TABLE_OWNER , TABLE_name order by 3, 1, 2 TABLE_OWNER TABLE_NAME COUNT(*) ------------------------------ ------------------------------ ---------- ... B2B TIP_WAITENTRY_RT 5 B2B TIP_WAITINGEVENT_RT 5 GMD GMD_RECIPE_VALIDITY_RULES 5 HR FF_DATABASE_ITEMS 5 HR FF_USER_ENTITIES 5 HR PAY_BALANCE_FEEDS_F 5 HR PAY_ELEMENT_ENTRIES_F 5 HR PAY_USER_COLUMNS 5 HR PAY_USER_COLUMN_INSTANCES_F 5 HR PAY_USER_TABLES 5 HR PER_ASSIGNMENT_BUDGET_VALUES_F 5 HR PER_PERFORMANCE_REVIEWS 5 HR PER_PERSON_TYPE_USAGES_F 5 JTF JTF_AUTH_PRINCIPAL_MAPS 5 ONT OE_ORDER_LINES_ALL 5 ORASSO WWSEC_GROUP$ 5 ORASSO WWSEC_PERSON$ 5 PA PA_PROJECTS_ALL 5 PO PO_NOTES 5 WIP WIP_DISCRETE_JOBS 5 AP AP_HOLDS_ALL 6 AP AP_INVOICES_ALL 6 AP AP_INVOICE_DISTRIBUTIONS_ALL 6 CN CN_HIERARCHY_EDGES_ALL 6 GMD FM_FORM_MST_B 6 GMI IC_ITEM_MST_B 6 HR PAY_BALANCE_CATEGORIES_F 6 HR PAY_ELEMENT_TYPES_F 6 CZ CZ_PS_NODES 7 AR RA_CUSTOMER_TRX_ALL 8 FA FA_MASS_ADDITIONS 8 HR FF_FORMULAS_F 8 HR GHR_GROUPBOXES 8 HR GHR_GROUPBOX_USERS 8 HR HR_ORGANIZATION_INFORMATION 8 HR PER_ABSENCE_ATTENDANCES 8 HR PER_ORG_STRUCTURE_ELEMENTS 8 HR PER_PAY_PROPOSALS 8 HR PER_PERIODS_OF_SERVICE 8 HR PER_ADDRESSES 9 HR PAY_PERSONAL_PAYMENT_METHODS_F 10 HR PAY_USER_ROWS_F 10 INV MTL_SYSTEM_ITEMS_B 11 HR PER_ALL_PEOPLE_F 12 HR FF_GLOBALS_F 14 HR PER_ALL_ASSIGNMENTS_F 15 SYS 26 3908 rows selected. A number of tables, have multiple triggers. ------------------------------------------------------------ ------------------------------------------------------------ For each schema, what is the ratio of tables, that have triggers? select owner , sum (table_trigger_flag) as tab_w_triggers , count(*) as tot_tab_count , (sum (table_trigger_flag) / count(*)) * 100 as ratio_tables_with_triggers from ( select tables.owner , tables.table_name , triggers.table_trigger_count , nvl2( triggers.table_trigger_count, 1, 0) table_trigger_flag from ( select owner , table_name from dba_tables ) tables left outer join ( select TABLE_OWNER , TABLE_name , count(*) as table_trigger_count from dba_triggers group by TABLE_OWNER , TABLE_name ) triggers on tables.owner = triggers.table_owner and tables.table_name = triggers.table_name ) group by owner order by 1 OWNER TAB_W_TRIGGERS TOT_TAB_COUNT RATIO_TABLES_WITH_TRIGGERS ------------------------------ -------------- ------------- -------------------------- ADS10GEUL_US 0 48 0 AHL 0 164 0 AK 0 59 0 AMS 0 375 0 AMV 0 42 0 AMW 0 176 0 AOLDEMO 0 6 0 AP 20 327 6.11620795 APPLSYS 22 937 2.34791889 APPS 54 1492 3.61930295 AR 26 686 3.79008746 ASF 0 2 0 ASG 0 60 0 ASL 0 24 0 ASO 3 69 4.34782609 ASP 0 2 0 … B2B 331 553 59.8553345 BAM 0 16 0 BEN 456 529 86.2003781 … CFD 13 14 92.8571429 CLN 0 28 0 … HERMAN 1 3 33.3333333 HR 887 1300 68.2307692 HRI 98 326 30.0613497 HXC 57 98 58.1632653 HXT 32 35 91.4285714 … XTR 40 216 18.5185185 ZPB 0 200 0 ZX 0 127 0 229 rows selected. ------------------------------------------------------------ ------------------------------------------------------------ select owner , object_type , count(*) from dba_objects group by owner , object_type order by 1, 2 ... HR INDEX 2799 HR INDEX PARTITION 37 HR LOB 65 HR PACKAGE 4 HR PACKAGE BODY 4 HR SEQUENCE 793 HR TABLE 1300 HR TABLE PARTITION 12 HR TRIGGER 3 HR only has 3 triggers! Odd. ------------------------------------------------------------ ------------------------------------------------------------ Turns out that APPS owns 1170 the triggers on HR schema, and HR owns 3. select owner as trigger_owner ,TABLE_OWNER , TABLE_name from dba_triggers where table_owner = 'HR' TRIGGER_OWNER TABLE_OWNER TABLE_NAME ------------------------------ ------------------------------ ------------------------------ ... APPS HR PQP_GAP_ABSENCE_PLANS APPS HR PQP_GAP_DAILY_ABSENCES APPS HR PQP_GAP_DURATION_SUMMARY APPS HR PQP_PENSION_TYPES_F APPS HR PQP_SERVICE_HISTORY_PERIODS APPS HR PQP_VEHICLE_ALLOCATIONS_F APPS HR PQP_VEHICLE_DETAILS APPS HR PQP_VEHICLE_REPOSITORY_F APPS HR PQP_VEH_ALLOC_EXTRA_INFO APPS HR PQP_VEH_ALLOC_INFO_TYPES APPS HR PQP_VEH_REPOS_EXTRA_INFO APPS HR PQP_VEH_REPOS_INFO_TYPES 1173 rows selected. ------------------------------------------------------------ ------------------------------------------------------------ APPS has many, many objects! select owner , object_type , count(*) from dba_objects group by owner , object_type order by 1, 2 ... APPS DATABASE LINK 29 APPS EVALUATION CONTEXT 1 APPS FUNCTION 60 APPS INDEX 2157 APPS INDEX PARTITION 178 APPS INDEX SUBPARTITION 48 APPS JAVA CLASS 644 APPS JAVA RESOURCE 6 APPS LOB 152 APPS LOB PARTITION 14 APPS LOB SUBPARTITION 48 APPS MATERIALIZED VIEW 745 APPS PACKAGE 52328 APPS PACKAGE BODY 51284 <- APPS PROCEDURE 147 APPS QUEUE 112 APPS RULE 3 APPS RULE SET 5 APPS SEQUENCE 70 APPS SYNONYM 42331 APPS TABLE 1492 APPS TABLE PARTITION 143 APPS TABLE SUBPARTITION 48 APPS TRIGGER 4183 <- APPS TYPE 3775 APPS TYPE BODY 101 APPS VIEW 32991 <- ... PACKAGE BODY 51284 VIEW 32991 TRIGGER 4183 -------------------------------------------------- -------------------------------------------------- Packages By Owner: select owner , object_type , count(*) from dba_objects where object_type = 'PACKAGE' group by owner , object_type order by 3 ... BAM PACKAGE 6 AR PACKAGE 8 MOBILEADMIN PACKAGE 8 RE PACKAGE 9 B2B PACKAGE 9 SYSTEM PACKAGE 11 D4OSYS PACKAGE 11 OPMOR PACKAGE 14 DSGATEWAY PACKAGE 17 ODS PACKAGE 22 WKSYS PACKAGE 22 LBACSYS PACKAGE 24 OSM PACKAGE 25 ORDSYS PACKAGE 27 XDB PACKAGE 31 DMSYS PACKAGE 36 WIRELESS PACKAGE 37 OLAPSYS PACKAGE 46 MDSYS PACKAGE 75 CTXSYS PACKAGE 94 OWF_MGR PACKAGE 106 FLOWS_010500 PACKAGE 133 FLOWS_020000 PACKAGE 160 FLOWS_030000 PACKAGE 174 ORASSO PACKAGE 177 SYS PACKAGE 625 APPS PACKAGE 52328 230 rows selected. -------------------------------------------------- -------------------------------------------------- Procedures By Owner: select owner , object_type , count(*) from dba_objects where object_type = 'PROCEDURE' group by owner , object_type order by 3 OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- OUTLN PROCEDURE 1 DISCOVERER5 PROCEDURE 1 SCOTT PROCEDURE 1 DCM PROCEDURE 1 ODS PROCEDURE 1 DBSNMP PROCEDURE 1 SYSTEM PROCEDURE 1 MOBILEADMIN PROCEDURE 1 D4OSYS PROCEDURE 1 CTXSYS PROCEDURE 2 ORABPEL PROCEDURE 2 ODM PROCEDURE 2 OPMOR PROCEDURE 3 MDSYS PROCEDURE 3 LBACSYS PROCEDURE 4 XDB PROCEDURE 5 WKSYS PROCEDURE 5 PTG PROCEDURE 6 ORDSYS PROCEDURE 8 ORASSO PROCEDURE 8 B2B PROCEDURE 9 WIRELESS PROCEDURE 15 FLOWS_030000 PROCEDURE 19 FLOWS_020000 PROCEDURE 28 FLOWS_010500 PROCEDURE 34 SYS PROCEDURE 83 APPS PROCEDURE 147 27 rows selected. -------------------------------------------------- -------------------------------------------------- How much source code by Owner: SELECT OWNER , COUNT( DISTINCT NAME ) AS CNT_NAME , COUNT( NAME ) AS CNT_NAME_LINES , COUNT(*) AS CNT_LINES FROM DBA_SOURCE GROUP BY OWNER ORDER BY 1 OWNER CNT_NAME CNT_LINES ------------------------------ ---------- ---------- AHL 2 127 AK 2 127 AMS 2 127 AMV 2 127 AMW 2 127 AP 2 127 APPLSYS 5 439 APPS 60217 57946832 <- AR 8 3712 ASF 2 127 ASG 5 565 ASL 2 127 ASN 2 127 ASO 2 127 ASP 2 127 AST 2 127 AX 2 127 AZ 2 127 B2B 1552 95181 <- BAM 11 1275 BEN 2 127 … ONT 2 127 OPI 2 127 OPMOR 18 675 ORABPEL 19 711 ORACLE_OCM 3 2172 ORASSO 282 60578 <- ORDPLUGINS 5 233 ORDSYS 519 5119 OSM 25 8147 OTA 2 127 OUTLN 1 9 OWAPUB 1 152 OWF_MGR 110 142518 <- OZF 2 127 PA 2 127 APPS has 57,946,832 (57 million) lines of source code! In 60,217 objects such as: packages, triggers, procedures, functions Average: 214.8 lines of code per object. /**/