Rem drv:
Rem
Rem $Header: db_outofbox_config.sql 28-nov-2006.06:33:46 mnihalan Exp $
Rem
Rem db_outofbox_config.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem db_outofbox_config.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem !!! Please update upgrade script if updates are made. !!!
Rem
Rem MODIFIED (MM/DD/YY)
Rem mnihalan 11/28/06 - Fix bug 5683385
Rem chyu 05/13/06 - RCU Compliant
Rem lgloyd 06/24/05 - normalize category names
Rem lgloyd 05/10/05 - fix param type
Rem xshen 03/21/05 - Add comments
Rem xshen 02/14/05 - change to use public views
Rem xshen 02/10/05 - support translation of row data
Rem xshen 02/03/05 - xshen_config_report
Rem xshen 02/02/05 - Created
Rem
SET DEFINE OFF
BEGIN
DECLARE
l_target_types MGMT_IP_TARGET_TYPES;
l_param_classes MGMT_IP_PARAM_CLASSES;
l_param_values MGMT_IP_PARAM_VALUE_LIST;
l_targets MGMT_IP_TARGET_LIST;
l_report_guid RAW(16);
l_element_guid RAW(16);
l_current_row NUMBER := 1;
l_current_order NUMBER := 1;
BEGIN
-- Create DB Config Report Definition
l_target_types := MGMT_IP_TARGET_TYPES();
l_target_types.extend(1);
l_target_types(1) := 'oracle_database';
l_report_guid := mgmt_ip.create_report_definition (
p_title_nlsid => 'DBCONFIG_REPORT_TITLE',
p_description_nlsid => 'DBCONFIG_REPORT_DESC',
p_owner => 'SYSMAN',
p_category_nlsid => 'IPMSG_DEPLOYMENT_AND_CONFIGURATION_CATEGORY',
p_sub_category_nlsid => 'IPMSG_ORACLE_DATABASE_CONFIGURATION_CATEGORY',
p_late_binding_target_types => l_target_types,
p_late_binding_multi_targets => 0,
p_show_table_of_contents => 0,
p_pack_name => 'db_config',
p_system_report => 1);
-- Element Definition - Instance Information
-- ------------------------------------------------------------
-- Data Scope: Repository (Current Snapshot Data)
-- Chart Type: Label Value Pairs
-- Description: This section will report on how inst is configured
-- Columns:
-- Option
-- Selected [TRUE|FALSE]
-- ------------------------------------------------------------
-- Element Definition: Instance Information
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(6);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'begin
declare
type cursor_type IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
begin
result_cursor_out := ?;
tgt_guid_in := ?;
start_date_in := ?;
end_date_in := ?;
query_string :=
''select o.database_name "DATABASE_NAME",
o.characterset "CHARACTERSET",
o.instance_name "INSTANCE_NAME",
o.national_characterset "NATIONAL_CHARACTERSET",
o.global_name "GLOBAL_NAME",
o.log_mode "LOG_MODE",
o.banner "BANNER",
o.default_temp_tablespace "DEFAULT_TEMP_TABLESPACE"
from mgmt$db_dbninstanceinfo o
where o.target_guid = :1 '';
open result_cursor_out for query_string using tgt_guid_in(1);
end;
end;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
'true');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay',
'2');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.nullDataStringSubstitue',
'UNAVAILABLE');
mgmt_ip.add_element_definition
(p_element_name_nlsid => 'DBCONFIG_INSTANCE_INFO',
p_element_type_nlsid => 'database_target_type',
p_description_nlsid => 'DBCONFIG_INSTANCE_INFO_DESC',
p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
p_internal_use_only => 1,
p_default_parameters => l_param_values
);
-- Add Element 1 to report.
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBCONFIG_INSTANCE_INFO',
p_element_type_nlsid => 'database_target_type',
p_header_nlslid => 'DBCONFIG_INSTANCE_INFO',
p_element_order => l_current_order,
p_element_row => l_current_row,
p_parameters => l_param_values,
p_targets => null);
l_current_order := l_current_order + 1;
l_current_row := l_current_row + 1;
-- Element Definition - Database Options - table
-- ------------------------------------------------------------
-- Data Scope: Repository (Current Snapshot Data)
-- Chart Type: Table
-- Description: This table will report on what options are
-- currently valid for a database.
-- Columns:
-- Option
-- Selected [TRUE|FALSE]
-- TBD: Until framework supports translating row DATA (communicated
-- with Leslie that it can be done) we use decode in English
-- for option names.
-- ------------------------------------------------------------
-- Element Definition: Database Options
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(8);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'begin
declare
type cursor_type IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
begin
result_cursor_out := ?;
tgt_guid_in := ?;
start_date_in := ?;
end_date_in := ?;
query_string :=
''select o.name as OPTION_NAME,
decode(o.selected, ''''TRUE'''', 15, null) as CONFIGURED
from mgmt$db_options o
where o.target_guid = :1
order by name asc'';
open result_cursor_out for query_string using tgt_guid_in(1);
end;
end;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
'true');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
'0');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
'ascending');
l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.translateColumnValueColumns',
'0');
l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
'1');
mgmt_ip.add_element_definition
(p_element_name_nlsid => 'DBCONFIG_OPTIONS',
p_element_type_nlsid => 'database_target_type',
p_description_nlsid => 'DBCONFIG_OPTIONS_DESC',
p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
p_internal_use_only => 1,
p_default_parameters => l_param_values
);
-- Add Element 2 to report.
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBCONFIG_OPTIONS',
p_element_type_nlsid => 'database_target_type',
p_header_nlslid => 'DBCONFIG_OPTIONS',
p_element_order => l_current_order,
p_element_row => l_current_row,
p_parameters => l_param_values,
p_targets => null);
l_current_order := l_current_order + 1;
l_current_row := l_current_row + 1;
-- Element Definition - Initialization Parameters - table
-- ------------------------------------------------------------
-- Data Scope: Repository (Current Snapshot Data)
-- Chart Type: Table
-- Description: This table will report on all initialization
-- parameters for a database.
-- Columns:
-- Parameter
-- Value
-- Default
-- ------------------------------------------------------------
-- Element Definition: Initialization Parameters
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(6);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'begin
declare
type cursor_type IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
begin
result_cursor_out := ?;
tgt_guid_in := ?;
start_date_in := ?;
end_date_in := ?;
query_string :=
''select o.name as PARAMETER_COL,
o.value as VALUE_COL
from mgmt$db_init_params o
where o.target_guid = :1
and o.isdefault = ''''FALSE''''
order by name asc'';
open result_cursor_out for query_string using tgt_guid_in(1);
end;
end;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
'true');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
'0');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
'ascending');
mgmt_ip.add_element_definition
(p_element_name_nlsid => 'DBCONFIG_INITPARAMS',
p_element_type_nlsid => 'database_target_type',
p_description_nlsid => 'DBCONFIG_INITPARAMS_DESC',
p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
p_internal_use_only => 1,
p_default_parameters => l_param_values
);
-- Add Element 3 to report.
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBCONFIG_INITPARAMS',
p_element_type_nlsid => 'database_target_type',
p_header_nlslid => 'DBCONFIG_INITPARAMS',
p_element_order => l_current_order,
p_element_row => l_current_row,
p_parameters => l_param_values,
p_targets => null);
l_current_order := l_current_order + 1;
l_current_row := l_current_row + 1;
-- Element Definition - Controlfiles - table
-- ------------------------------------------------------------
-- Data Scope: Repository (Current Snapshot Data)
-- Chart Type: Table
-- Description: This table will report on all controlfile
-- configuration data for a database.
-- Columns:
-- File Name
-- Status
-- Creation Date
-- Modification Date
-- OS Storage Entity
-- ------------------------------------------------------------
-- Element Definition: Controlfiles
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(6);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'begin
declare
type cursor_type IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
begin
result_cursor_out := ?;
tgt_guid_in := ?;
start_date_in := ?;
end_date_in := ?;
query_string :=
''select o.file_name as FILE_NAME_COL,
o.status as STATUS_COL,
o.creation_date as CREATION_DATE_COL,
o.mod_date as MOD_DATE,
o.os_storage_entity as OS_STORAGE_ENTITY
from mgmt$db_controlfiles o
where o.target_guid = :1
order by file_name asc'';
open result_cursor_out for query_string using tgt_guid_in(1);
end;
end;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
'true');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
'0');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
'ascending');
mgmt_ip.add_element_definition
(p_element_name_nlsid => 'DBCONFIG_CONTROLFILES',
p_element_type_nlsid => 'database_target_type',
p_description_nlsid => 'DBCONFIG_CONTROLFILES_DESC',
p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
p_internal_use_only => 1,
p_default_parameters => l_param_values
);
-- Add Element 3 to report.
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBCONFIG_CONTROLFILES',
p_element_type_nlsid => 'database_target_type',
p_header_nlslid => 'DBCONFIG_CONTROLFILES',
p_element_order => l_current_order,
p_element_row => l_current_row,
p_parameters => l_param_values,
p_targets => null);
l_current_order := l_current_order + 1;
l_current_row := l_current_row + 1;
-- Element Definition - Tablespaces - table
-- ------------------------------------------------------------
-- Data Scope: Repository (Current Snapshot Data)
-- Chart Type: Table
-- Description: This table will report on all tablespaces
-- configuration data for a database.
-- Columns:
-- Tablespace Name
-- Contents
-- Status
-- Extent Management
-- Allocation Type
-- Segment Space Management
-- Logging
-- Initial Extent Size
-- Next Extent
-- Increment By
-- Maximum Extents
-- Minimum Extents
-- Minimum Extent Length
-- Block Size
-- Big File
-- ------------------------------------------------------------
-- Element Definition: Tablespaces
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(6);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'begin
declare
type cursor_type IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
begin
result_cursor_out := ?;
tgt_guid_in := ?;
start_date_in := ?;
end_date_in := ?;
query_string :=
''select o.tablespace_name as TABLESPACE_NAME,
o.status as STATUS_COL,
o.contents as TYPE_COL,
o.extent_management as EXTENT_MANAGEMENT,
o.allocation_type as ALLOCATION_COL,
o.segment_space_management as SEGMENT_SPACE_MANAGEMENT,
o.logging as ENABLE_LOGGING_COL,
TO_CHAR(NVL(o.initial_ext_size/1024,0),''''99G999G990'''') as INIT_EXT_K_COL,
TO_CHAR(NVL(o.next_extent/1024,0),''''99G999G990'''') as NEXT_SIZE_K_COL,
TO_CHAR(NVL(o.increment_by,0),''''999999990D00'''') as PCT_INCREASE,
DECODE(o.max_extents,null,''''UNLIMITED'''',TO_CHAR(max_extents)) as MAX_EXTENTS,
o.min_extents as MIN_EXTENTS,
TO_CHAR(NVL(o.min_extlen/1024,0),''''99G999G990'''') as MIN_EXTLEN_K,
o.block_size as BLOCK_SIZE_COL,
TO_CHAR(NVL(o.bigfile,''''NA'''')) as BIGFILE
from mgmt$db_tablespaces o
where o.target_guid = :1
order by tablespace_name asc'';
open result_cursor_out for query_string using tgt_guid_in(1);
end;
end;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
'true');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
'0');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
'ascending');
mgmt_ip.add_element_definition
(p_element_name_nlsid => 'DBCONFIG_TABLESPACES',
p_element_type_nlsid => 'database_target_type',
p_description_nlsid => 'DBCONFIG_TABLESPACES_DESC',
p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
p_internal_use_only => 1,
p_default_parameters => l_param_values
);
-- Add Element 4 to report.
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBCONFIG_TABLESPACES',
p_element_type_nlsid => 'database_target_type',
p_header_nlslid => 'DBCONFIG_TABLESPACES',
p_element_order => l_current_order,
p_element_row => l_current_row,
p_parameters => l_param_values,
p_targets => null);
l_current_order := l_current_order + 1;
l_current_row := l_current_row + 1;
-- Element Definition - Datafiles - table
-- ------------------------------------------------------------
-- Data Scope: Repository (Current Snapshot Data)
-- Chart Type: Table
-- Description: This table will report on all tablespaces
-- configuration data for a database.
-- Columns:
-- File Name
-- Tablespace Name
-- Status
-- Autoextensible
-- File Size
-- Max File Size
-- OS Storage Entity
-- ------------------------------------------------------------
-- Element Definition: Datafiles
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(6);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'begin
declare
type cursor_type IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
begin
result_cursor_out := ?;
tgt_guid_in := ?;
start_date_in := ?;
end_date_in := ?;
query_string :=
''select o.file_name as FILE_NAME_COL,
o.tablespace_name as TABLESPACE_NAME,
o.status as STATUS,
o.autoextensible as AUTOEXTENSIBLE,
TO_CHAR(NVL(o.file_size/1024/1024,0),''''99999990D000'''') as SIZE_M_COL,
DECODE(o.max_file_size,0,''''UNLIMITED'''',TO_CHAR(NVL(max_file_size/1024/1024,0),''''99999990D000'''')) as MAX_FILE_SIZE_M_COL,
TO_CHAR(o.increment_by,''''999999990D000'''') as INCREMENT_BLOCKS_COL,
o.os_storage_entity as OS_STORAGE_ENTITY
from mgmt$db_datafiles o
where o.target_guid = :1
order by tablespace_name asc'';
open result_cursor_out for query_string using tgt_guid_in(1);
end;
end;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
'true');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
'0');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
'ascending');
mgmt_ip.add_element_definition
(p_element_name_nlsid => 'DBCONFIG_DATAFILES',
p_element_type_nlsid => 'database_target_type',
p_description_nlsid => 'DBCONFIG_DATAFILES_DESC',
p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
p_internal_use_only => 1,
p_default_parameters => l_param_values
);
-- Add Element 5 to report.
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBCONFIG_DATAFILES',
p_element_type_nlsid => 'database_target_type',
p_header_nlslid => 'DBCONFIG_DATAFILES',
p_element_order => l_current_order,
p_element_row => l_current_row,
p_parameters => l_param_values,
p_targets => null);
l_current_order := l_current_order + 1;
l_current_row := l_current_row + 1;
-- Element Definition - Redo Log Groups - table
-- ------------------------------------------------------------
-- Data Scope: Repository (Current Snapshot Data)
-- Chart Type: Table
-- Description: This table will report on all datafiles
-- configuration data for a database.
-- Columns:
-- Group Num
-- Status
-- Members
-- File Name
-- Log Size
-- OS Storage Entity
-- Thread Num
-- ------------------------------------------------------------
-- Element Definition: Datafiles
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(6);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'begin
declare
type cursor_type IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
begin
result_cursor_out := ?;
tgt_guid_in := ?;
start_date_in := ?;
end_date_in := ?;
query_string :=
''select o.group_num as GROUP_NUM_COL,
o.status as STATUS,
o.members as MEMBERS,
o.file_name as FILE_NAME,
TO_CHAR((logsize/1024), ''''99G999G990'''') as SIZE_K_COL,
o.thread_num as THREAD_NUM,
o.os_storage_entity as OS_STORAGE_ENTITY
from mgmt$db_redologs o
where o.target_guid = :1
order by group_num asc'';
open result_cursor_out for query_string using tgt_guid_in(1);
end;
end;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
'true');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
'0');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
'ascending');
mgmt_ip.add_element_definition
(p_element_name_nlsid => 'DBCONFIG_REDOLOGS',
p_element_type_nlsid => 'database_target_type',
p_description_nlsid => 'DBCONFIG_REDOLOGS_DESC',
p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
p_internal_use_only => 1,
p_default_parameters => l_param_values
);
-- Add Element 6 to report.
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBCONFIG_REDOLOGS',
p_element_type_nlsid => 'database_target_type',
p_header_nlslid => 'DBCONFIG_REDOLOGS',
p_element_order => l_current_order,
p_element_row => l_current_row,
p_parameters => l_param_values,
p_targets => null);
l_current_order := l_current_order + 1;
l_current_row := l_current_row + 1;
-- Element Definition - Rollback Segments - table
-- ------------------------------------------------------------
-- Data Scope: Repository (Current Snapshot Data)
-- Chart Type: Table
-- Description: This table will report on all rollback segs
-- configuration data for a database.
-- Columns:
-- Segment Name
-- Status
-- Tablespace Name
-- Extents
-- Size
-- Initial Size
-- Next Size
-- Max Extents
-- Min Extents
-- PCT Increase
-- Optimal Size
-- Average Active
-- Wraps
-- Shrinks
-- Average Shink
-- HWM Size
-- ------------------------------------------------------------
-- Element Definition: Datafiles
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(6);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'begin
declare
type cursor_type IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
begin
result_cursor_out := ?;
tgt_guid_in := ?;
start_date_in := ?;
end_date_in := ?;
query_string :=
''select o.rollname as ROLLNAME,
o.status as STATUS,
o.tablespace_name as TABLESPACE_NAME,
o.extents as EXTENTS,
TO_CHAR(NVL(o.rollsize/1024/1024,0),''''99G999G990D900'''') as SIZE_M_COL,
TO_CHAR(NVL(o.next_size/1024,0),''''99G999G990'''') as NEXT_SIZE_K_COL,
o.maximum_extents as MAXIMUM_EXTENTS,
o.minimum_extents as MINIMUM_EXTENTS,
o.pct_increase as PCT_INCREASE,
o.optsize as OPTSIZE,
o.aveactive as AVEACTIVE,
o.wraps as WRAPS,
o.shrinks as SHRINKS,
o.aveshrink as AVESHRINK,
TO_CHAR(NVL(o.hwmsize/1024/1024,0),''''99G999G990D900'''') as HWMSIZE_M
from mgmt$db_rollback_segs o
where o.target_guid = :1
order by rollname asc'';
open result_cursor_out for query_string using tgt_guid_in(1);
end;
end;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
'true');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
'0');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
'ascending');
mgmt_ip.add_element_definition
(p_element_name_nlsid => 'DBCONFIG_ROLLBACKSEGS',
p_element_type_nlsid => 'database_target_type',
p_description_nlsid => 'DBCONFIG_ROLLBACKSEGS_DESC',
p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
p_internal_use_only => 1,
p_default_parameters => l_param_values
);
-- Add Element 7 to report.
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBCONFIG_ROLLBACKSEGS',
p_element_type_nlsid => 'database_target_type',
p_header_nlslid => 'DBCONFIG_ROLLBACKSEGS',
p_element_order => l_current_order,
p_element_row => l_current_row,
p_parameters => l_param_values,
p_targets => null);
-- End
COMMIT;
END;
END;
/
SET DEFINE ON