Rem Rem $Header: storage_common_tables.sql 29-jun-2005.01:47:53 gsbhatia Exp $ Rem Rem storage_report_tables.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem common_tables.sql - Rem Rem DESCRIPTION Rem sql generated from storage_report_ecm_metadata.xml for generating ecm schema for storage reports Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem gsbhatia 06/26/05 - New repmgr header impl Rem ajdsouza 05/05/05 - added temporary table mgmt_storage_tmp_nfs_data Rem ajdsouza 04/08/05 - change name to 256 in mgmt_storage_report_data Rem ajdsouza 03/25/05 - Moved from latest Rem gsbhatia 02/13/05 - updating repmgr header Rem gsbhatia 02/09/05 - updating repmgr header Rem gsbhatia 02/07/05 - updating repmgr header Rem ajdsouza 01/26/05 - change long fields to raw, replace message with message counter Rem ktlaw 01/11/05 - add repmgr header Rem ajdsouza 11/29/04 - add field A8 Rem rmenon 08/16/04 - deleted temporary tables containing Rem dummy storage data. Rem ajdsouza 08/10/04 - ajdsouza_ecm_processing Rem ajdsouza 07/30/04 - ajdsouza_fix_storage_metrics Rem ajdsouza 07/28/04 - Moved to new storage component in common functional unit Rem ajdsouza 07/20/04 - nls support for messages Rem rmenon 06/30/04 - Rem rmenon 06/28/04 - storage UI related tables Rem ajdsouza 06/25/04 - storage reporting sources Rem ajdsouza 05/12/04 - Created Rem Rem SET ECHO ON Rem SET FEEDBACK 1 Rem SET NUMWIDTH 10 Rem SET LINESIZE 80 Rem SET TRIMSPOOL ON Rem SET TAB OFF Rem SET PAGESIZE 100 -- Uncomment the following line to see the commands during the script execution -- SET ECHO ON -- Do not use substitution variables in this script so that there is no -- additional restrictions on the string values that are being inserted (such as ampersand -- being interpreted specially by SQLPlus). -- SET DEFINE OFF -------------------------------------------- -- Table (re)creation statements based on metadata Host Storage Layout for target type host and snapshot type host_storage -- Please REVIEW the table (re)creation statements below and modify them if necessary. Examples: -- (1) You meant for a TABLE tag in the metadata to represent a view. Then, remove the corresponding drop-table / create-table statements. -- (2) Generated PRIMARY KEY constraints are supposed to be uniqueness constraints instead. -- (3) You want to impose additional constraints on the tables. -------------------------------------------- -- Drop tables -- DROP TABLE "MGMT_STORAGE_REPORT_DATA"; -- DROP TABLE "MGMT_STORAGE_REPORT_KEYS"; -- DROP TABLE "MGMT_STORAGE_REPORT_ISSUES"; -- DROP TABLE "MGMT_STORAGE_REPORT_ALIAS"; -- Create tables CREATE TABLE "MGMT_STORAGE_REPORT_DATA" ( "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_STORAGE_REPORT_DATA0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE, "KEY_VALUE" RAW(20), "GLOBAL_UNIQUE_ID" RAW(20) NOT NULL, "NAME" VARCHAR2(256), "STORAGE_LAYER" VARCHAR2(32), "EM_QUERY_FLAG" VARCHAR2(64), "ENTITY_TYPE" VARCHAR2(64), "RAWSIZEB" NUMBER, "SIZEB" NUMBER, "USEDB" NUMBER, "FREEB" NUMBER, "A1" VARCHAR2(256), "A2" VARCHAR2(256), "A3" VARCHAR2(256), "A4" VARCHAR2(256), "A5" VARCHAR2(256), "A6" VARCHAR2(256), "A7" VARCHAR2(256), "A8" VARCHAR2(256), CONSTRAINT "MGMT_STORAGE_REPORT_DATA_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "KEY_VALUE") ) MONITORING; CREATE TABLE "MGMT_STORAGE_REPORT_KEYS" ( "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_STORAGE_REPORT_KEYS0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE, "KEY_VALUE" RAW(20), "PARENT_KEY_VALUE" RAW(20), CONSTRAINT "MGMT_STORAGE_REPORT_KEYS_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "KEY_VALUE", "PARENT_KEY_VALUE") ) MONITORING; CREATE TABLE "MGMT_STORAGE_REPORT_ISSUES" ( "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_STORAGE_REPORT_ISSUES0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE, "TYPE" VARCHAR2(32), "MESSAGE_COUNTER" NUMBER, "MESSAGE_ID" VARCHAR2(256), "MESSAGE_PARAMS" VARCHAR2(512), "ACTION_ID" VARCHAR2(256), "ACTION_PARAMS" VARCHAR2(512), CONSTRAINT "MGMT_STORAGE_REPORT_ISSUES_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "TYPE", "MESSAGE_COUNTER") ) MONITORING; CREATE TABLE "MGMT_STORAGE_REPORT_ALIAS" ( "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_STORAGE_REPORT_ALIAS0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE, "KEY_VALUE" RAW(20), "VALUE" VARCHAR2(256), "FILE_TYPE" VARCHAR2(256), CONSTRAINT "MGMT_STORAGE_REPORT_ALIAS_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "KEY_VALUE", "VALUE") ) MONITORING; -- End of table (re)creation for metadata Host Storage Layout -- PURPOSE -- Object to temporarily hold the NFS data -- during post processing -- -- MEMBERS -- key_value, -- server_name, -- server_mac_address, -- server_ip_address, -- filesystem -- nfs serner target_guid -- nfs server target_name -- nfs server target_type -- exported filesystem -- global_unique_id -- CREATE GLOBAL TEMPORARY TABLE mgmt_storage_tmp_nfs_data ( key_value RAW(20), server_name VARCHAR2(256), server_mac_address VARCHAR2(256), server_ip_address VARCHAR2(256), filesystem VARCHAR2(256), target_guid RAW(20), target_name VARCHAR2(256), target_type VARCHAR2(256), exported_fs VARCHAR2(256), global_unique_id RAW(20) ) ON COMMIT DELETE ROWS /