Oracle Applications R12: More Schema Analysis


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. 

/**/
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: