Rem drv: <migrate type="schema_upgrade" version="10.2.0.1"/> 
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/v102020/stgAdv_tables.sql /st_emdbsa_11.2/1 2008/09/30 08:01:51 mnihalan Exp $
Rem
Rem stgAdv_tables.sql
Rem
Rem Copyright (c) 2005, 2008, Oracle. All rights reserved.
Rem
Rem    NAME
Rem      stgAdv_tables.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    mnihalan    07/31/08 - Fix bug 6266117
Rem    mnihalan    04/03/07 - Bug 5969011 remove hardcoding of sysman
Rem    rreilly     08/31/05 - rreilly_bug-4570353
Rem    rreilly     08/30/05 - bug 4571494 obj_lob_col needs size of 4000
Rem    rreilly     08/30/05 - Created
Rem

Rem
Rem Bug 4571494
Rem Table sysman.mgmt_db_recsegmentsettings_ecm needs to be altered
Rem to make the obj_lob_col column size 4000, it was size 30.
Rem This is because it sometimes gets data from sys.attrcol$ name
Rem which is varchar2 4000.
Rem The associated ECM Data must also be updated. (oracle_storage)
Rem
Rem Fix bug 6266117 change size to 500

alter table mgmt_db_recsegmentsettings_ecm 
      modify (obj_lob_col VARCHAR2(500));



Rem
Rem ECM MetaData upgrade for:    'oracle_storage' 
Rem

ALTER SESSION SET CONSTRAINTS = DEFERRED;

DECLARE
  metadata_id RAW(16) := SYS_GUID();
BEGIN
  -- Remove all related metadatas
  DELETE FROM MGMT_ECM_SNAPSHOT_MD_TABLES WHERE METADATA_ID IN (SELECT METADATA_ID FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = 'oracle_database' AND SNAPSHOT_TYPE = 'oracle_storage');
  DELETE FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = 'oracle_database' AND SNAPSHOT_TYPE = 'oracle_storage';

  -- Register metadata as loader metadata
  INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, HISTORY_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE) 
    VALUES ('oracle_storage', 'oracle_database', 'L', metadata_id, 'Recommended Settings Problems', 'Y', 'N', 'N', NULL, NULL, NULL, '1.0', NULL, NULL, NULL, 'H');

  -- Register metadata as presentation metadata
  INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, HISTORY_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE) 
    VALUES ('oracle_storage', 'oracle_database', 'P', metadata_id, 'Recommended Settings Problems', 'Y', 'N', 'N', NULL, NULL, NULL, '1.0', NULL, NULL, NULL, 'H');

  -- Register metadata for table MGMT_DB_RECUSERSETTINGS_ECM
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, NAME, UI_NAME, UI_ON, COMPARE_ON, HISTORY_ON, PARENT_TABLE_NAME, IS_SINGLE_ROW, TBL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECUSERSETTINGS_ECM', 'User Recommended Settings Problems', 'Y', 'N', 'N', NULL, 'N', 0);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECUSERSETTINGS_ECM', 'USER_NAME', 'User Name', 'S', '30', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 0);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECUSERSETTINGS_ECM', 'PROBLEM_CODE', 'Object', 'N', '2', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 1);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECUSERSETTINGS_ECM', 'TABLESPACE', 'Tablespace', 'S', '30', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 2);


  -- Register metadata for table MGMT_DB_RECTSSETTINGS_ECM
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, NAME, UI_NAME, UI_ON, COMPARE_ON, HISTORY_ON, PARENT_TABLE_NAME, IS_SINGLE_ROW, TBL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECTSSETTINGS_ECM', 'Tablespace Recommended Settings Problems', 'Y', 'N', 'N', NULL, 'N', 1);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECTSSETTINGS_ECM', 'TABLESPACE', 'Tablespace', 'S', '30', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 0);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECTSSETTINGS_ECM', 'PROBLEM_CODE', 'Object', 'N', '2', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 1);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECTSSETTINGS_ECM', 'VALUE1', 'Value 1', 'N', '10', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 2);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECTSSETTINGS_ECM', 'VALUE2', 'Value 2', 'N', '10', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 3);

  -- Register metadata for table MGMT_DB_RECSEGMENTSETTINGS_ECM
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, NAME, UI_NAME, UI_ON, COMPARE_ON, HISTORY_ON, PARENT_TABLE_NAME, IS_SINGLE_ROW, TBL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'Segment Recommended Settings Problems', 'Y', 'N', 'N', NULL, 'N', 2);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'OBJ_TYPE', 'Object Type', 'N', '2', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 0);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'OBJ_OWNER', 'Owner', 'S', '30', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 1);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'OBJ_NAME', 'Name', 'S', '30', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 2);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'OBJ_PARTITION', 'Partition', 'S', '30', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 3);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'OBJ_LOB_COL', 'LOB Column', 'S', '500', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 4);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'TABLESPACE', 'Tablespace', 'S', '30', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 5);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'SEGMENT_TYPE', 'Segment Type', 'N', '2', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 6);
  INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
    VALUES (metadata_id, 'MGMT_DB_RECSEGMENTSETTINGS_ECM', 'PROBLEM_CODE', 'Problem', 'N', '2', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 7);
END;
/

ALTER SESSION SET CONSTRAINTS = IMMEDIATE;
COMMIT;