Table, Trigger, View, and Sequence Information for Oracle Server
This section describes the table and view information for Oracle Server.
Oracle Sequences
The PATROL HL KM creates the following Oracle sequences:
- P_APPS_SEQ
- P_HISTORY_SEQ
- P_INSTANCES_SEQ
Oracle Server Tables Populated by the PATROL HL KM
The PATROL HL KM populates the following Oracle Server tables:
- P_APPS
- P_INSTANCES
- P_HISTORY
- P_TMP_HISTORY_DATA
Oracle Server P_APPS Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
app_id | NUMBER | NOT NULL | application identifier; primary key |
node_name | VARCHAR2(32) | NOT NULL | node identifier; foreign key to p_nodes |
app_name | VARCHAR2(32) | NOT NULL | PATROL application name |
parameter_name | VARCHAR2(32) | NULL | PATROL parameter name |
Oracle Server P_INSTANCES Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
instance_id | NUMBER | NOT NULL | instance identifier; primary key |
instance_name | VARCHAR2(1000) | NULL | PATROL instance name |
Oracle Server P_HISTORY Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
history_id | NUMBER | NOT NULL | history identifier; primary key |
app_id | NUMBER | NOT NULL | application identifier; foreign key to p_apps |
instance_id | NUMBER | NOT NULL | instance identifier; foreign key to p_instances |
timestamp | DATE | NOT NULL | date and time of occurrence of value |
value | NUMBER | NOT NULL | value of the PATROL parameter |
Oracle Server P_TMP_HISTORY_DATA Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
app_id | NUMBER | NULL | application identifier; foreign key to p_apps |
instance_id | NUMBER | NULL | instance identifier; foreign key to p_instances |
tmp_node_name | VARCHAR2(32) | NULL | node identifier; foreign key to p_nodes |
tmp_app_name | VARCHAR2(32) | NULL | PATROL application name |
tmp_instance_name | VARCHAR2(32) | NULL | PATROL instance name |
tmp_parameter_name | VARCHAR2(32) | NULL | PATROL parameter name |
Oracle Server P_TMP_HISTORY_DATA Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
tmp_time_stamp | VARCHAR2(32) | NULL | date and time of occurrence of value |
tmp_value | VARCHAR2(255) | NULL | value of the PATROL parameter |
Oracle Server Views
The PATROL HL KM populates the following Oracle Server view:
Oracle Server P_VIEW Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
node_name | VARCHAR2(32) | NOT NULL | node name |
app_name | VARCHAR2(32) | NOT NULL | PATROL application name |
instance_name | VARCHAR2(32) | NULL | PATROL instance name |
parameter_name | VARCHAR2(32) | NULL | PATROL parameter name |
time_stamp | DATE | NOT NULL | date and time of occurrence of value |
value | NUMBER | NOT NULL | value of the PATROL parameter |
Oracle Table Sizes
When determining the sizing and administration procedures for these tables, take their growth characteristics into consideration.
Oracle Tables that are Stable in Size
Once their names (app_name and instance_name) have been inserted once, the following tables do not grow; thus, these tables reach a stable size quickly:
- P_APPS
- P_INSTANCES
Oracle Tables that Continuously Grow
The P_HISTORY table incurs inserts after every PATROL parameter polling interval.
For information about keeping the P_HISTORY table size manageable, see "Improving the PATROL HL KM Performance" on page 103.
Oracle Tables that are Purged after Each Transaction
The P_TMP_HISTORY_DATA table is purged of data after each transaction; so it only needs to be large enough to support the largest transaction.
Oracle Server with Archive Mode On
If you are running Oracle Server with ARCHIVELOG mode on, History Loader transaction processing will require more space.
Oracle Triggers
The PATROL HL KM creates the following Oracle triggers:
- P_APPS_B1
- P_INSTANCES_B1
- P_HISTORY_B1