Viewing the Whole iasdb Instance - Oracle

If you need to handle the administration and management of Oracle Application Server 10g, this article covers the metadata repository (iasdb), the Single Sign-On (SSO) security framework, and the Oracle Application Server 10g Management Services. It is excerpted from chapter 2 of the book, Oracle Application Server 10g Administration Handbook, written by John Garmany and Donald Burleson (McGraw-Hill/Osborne, 2004; ISBN: 0072229586).

TABLE OF CONTENTS:

By: McGraw-Hill/Osborne

Poor Best

Rating:

/ 12

May 05, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

The entire iasdb instance contains more than 10,000 database objects and more than 1800 tables. You can run the following script to show the counts for each object type within iasdb.

iasdb_component_count.sql

set lines 60
set pages 999
ttitle 'OracleAS iasdb|Object Report'
spool obj_count.lst
col c1 heading 'Owner' format a30
col c2 heading 'Object|Type' format a15
col c3 heading 'Object|Count' format 99,999
break on c1 skip 2
compute sum of c3 on c1
select
   owner c1,
   object_type c2,
   count(*) c3
from
dba_objects
where
owner in (
'OSE$HTTP$ADMIN',
'DCM',
'DISCOVERER5',
'ORASSO_PS',
'ORASSO_PUBLIC',
'ORASSO',
'ODS',
'ORAOCA_PUBLIC',
'UDDISYS',
'WCRSYS',
'OCA',
'IP',
'OWF_MGR',
'WIRELESS',
'DSGATEWAY',
'PORTAL_APP',
'PORTAL_PUBLIC',
'PORTAL',
'ORASSO_PA',
'ORASSO_DS',
'WKPROXY',
'INTERNET_APPSERVER_REGISTRY',
'SPIERSON',
'SYSADMIN',
'WFADMIN',
'ORDPLUGINS'
)
group by
    owner,
   object_type
order by
    c1,
    c3 desc
;
spool off

The output from this script is shown in the following listing. Even though there are more than 10,000 objects in the iasdb database, when you break them down into their components, you see that IP is the largest schema with 400+ tables, followed by PORTAL with 350+ tables, and WIRELESS and OWF_MGR with over 200 tables each.

OracleAS iasdb
Object Report
Owner Object Type Object Count
----------------------------- --------------- -------
DCM INDEX

TABLE 11

LOB 1

SEQUENCE 1

****************************** -------

sum 29

DISCOVERER5 INDEX 34

SEQUENCE 8

TABLE 8

LOB 3

****************************** -------

sum 53

DSGATEWAY INDEX 30

TABLE 30

PACKAGE 17

PACKAGE BODY 16

SEQUENCE 12

TYPE 12

LOB 3

****************************** -------

sum 121

INTERNET_APPSERVER_REGISTRY INDEX 2

TABLE 1

****************************** -------

sum 4

42 Oracle Application Server 10 gAdministration Handbook

IP TRIGGER 1,293

INDEX 762

TABLE 475

LOB 223

PACKAGE 9

PROCEDURE 9

QUEUE 9

PACKAGE BODY 8

SYNONYM 7

SEQUENCE 4

TYPE 4

FUNCTION 1

****************************** -------

sum 3,241

OCA INDEX 11

TABLE 11

SEQUENCE 5

LOB 2

PACKAGE 2

PACKAGE BODY 2

****************************** -------

sum 34

ODS INDEX 300

TABLE 156

TYPE 23

PACKAGE 20

PACKAGE BODY 20

SEQUENCE 7

LOB 2

PROCEDURE 1

****************************** -------

sum 529

ORASSO PACKAGE 176

PACKAGE BODY 162

INDEX 155

TABLE 91

TYPE 51

SEQUENCE 39

TRIGGER 35

TYPE BODY 10

PROCEDURE 8

LOB 7

FUNCTION 4

JAVA CLASS 2

SYNONYM 2

LIBRARY 1

****************************** -------

sum 786

ORASSO_DS SYNONYM 4

****************************** -------

sum 4

ORASSO_PA SYNONYM 1

****************************** -------

sum 1

ORASSO_PS SYNONYM 5

****************************** -------

sum 5

ORDPLUGINS PACKAGE 14

PACKAGE BODY 14

****************************** -------

sum 28

OSE$HTTP$ADMIN TABLE 3

SEQUENCE 2

****************************** -------

sum 5

OWF_MGR INDEX 261

TABLE 204

PACKAGE 136

PACKAGE BODY 132

SEQUENCE 49

LOB 36

QUEUE 36

TYPE 9

UNDEFINED 9

SYNONYM 8

TYPE BODY 3

TABLE PARTITION 2

****************************** -------

sum 970

PORTAL PACKAGE 729

PACKAGE BODY 704

INDEX 584

TABLE 379

JAVA CLASS 199

TRIGGER 130

TYPE 114

SEQUENCE 89

LOB 50

TYPE BODY 38

PROCEDURE 12

FUNCTION 5

SYNONYM 5

JAVA RESOURCE 4

JAVA SOURCE 1

****************************** -------

sum 3,200

PORTAL_APP SYNONYM 3

****************************** -------

sum 3

UDDISYS INDEX 130

TABLE 46

LOB 4

PACKAGE 3

PACKAGE BODY 2

SEQUENCE 2

TYPE 1

****************************** -------

sum 189

WCRSYS TABLE 10

INDEX 9

SEQUENCE 2

PACKAGE 1

PACKAGE BODY 1

****************************** -------

sum 23

WIRELESS INDEX 403

TABLE 236

TRIGGER 92

SEQUENCE 54

LOB 46

TYPE 43

QUEUE 41

PACKAGE 33

PACKAGE BODY 32

PROCEDURE 16

FUNCTION 7

CLUSTER 1

****************************** -------

sum 1,071

It should come as no surprise that the iasdb database contains complex data structures and stored packages, but administrators must understand how these database components are used by the Application Server 10g application components. Of course, the iasdb schema components should never be altered or directly manipulated, but there are some important logging components within iasdb. Let’s take a close look at some of the Application Server 10g infrastructure log tables.

Next: The Infrastructure Log Tables >>