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.
/**/
