Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="trace/trace_schema_upgrade.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/v102010/user_model_schema_upgrade.sql /st_emcore_10.2.0.4.2db11.2/1 2009/04/05 23:02:50 nmathuri Exp $
Rem
Rem user_model_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      user_model_schema_upgrade.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    aptrived    08/07/07 - Backport aptrived_bug-5493464 from
Rem                           st_emcore_10.2.0.1.0
Rem    aptrived    09/10/06 - Bug#5493464, avoid ORA-00955
Rem    dcawley     09/08/05 - Check for existence of flat role grants 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    dcawley     06/13/05 - Add primary key to callback table 
Rem    dcawley     03/03/05 - Drop context 
Rem    eujang      03/03/05 - add the mgmt_priv_grants_idx01 index 
Rem    dcawley     02/24/05 - Add compression 
Rem    dcawley     02/08/05 - Add migration 
Rem    scgrover    02/07/05 - fix upgrade 
Rem    scgrover    01/28/05 - make more robust 
Rem    scgrover    01/27/05 - scgrover_bug-4143351
Rem    scgrover    01/26/05 - add index rename 
Rem    scgrover    01/26/05 - make user dynamic 
Rem    scgrover    01/26/05 - Created
Rem

Rem Create new table definition for the MGMT_USER_CONTEXT table

CREATE TABLE MGMT_USER_CONTEXT_INT
(client_identifier  VARCHAR2(256),
 attribute          NUMBER,
 value              NUMBER,
 CONSTRAINT MGMT_USER_CONTEXT_PK_INT PRIMARY KEY (client_identifier, attribute)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;

Rem redefine the table to be an IOT
DECLARE
  no_errors NUMBER := 0;
BEGIN
  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_USER_CONTEXT', 'MGMT_USER_CONTEXT_INT');
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_USER_CONTEXT', 'MGMT_USER_CONTEXT_INT');
    no_errors := 1;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_REDEFINITION.ABORT_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_USER_CONTEXT', 'MGMT_USER_CONTEXT_INT');
  END;

  EXECUTE IMMEDIATE 'DROP TABLE MGMT_USER_CONTEXT_INT';

  IF no_errors = 1 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_USER_CONTEXT RENAME CONSTRAINT MGMT_USER_CONTEXT_PK_INT TO MGMT_USER_CONTEXT_PK';
    EXECUTE IMMEDIATE 'ALTER INDEX MGMT_USER_CONTEXT_PK_INT RENAME TO MGMT_USER_CONTEXT_PK';
  END IF;
END;
/

Rem Create the new table definition for the MGMT_USER_CALLBACKS table

CREATE TABLE MGMT_USER_CALLBACKS_INT
(callback VARCHAR2(100),
 type     NUMBER,
 CONSTRAINT MGMT_USER_CALLBACKS_PK_INT PRIMARY KEY (callback, type)
) ORGANIZATION INDEX MONITORING;

Rem redefine the table to be an IOT
DECLARE
  no_errors NUMBER := 0; 
BEGIN

  BEGIN
    -- Add a primary key
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_USER_CALLBACKS ADD CONSTRAINT MGMT_USER_CALLBACKS_PK PRIMARY KEY (callback, type)';
  EXCEPTION
    WHEN OTHERS THEN
     EXECUTE IMMEDIATE 'DROP TABLE MGMT_USER_CALLBACKS_INT';
     RETURN; 
  END;
   
  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_USER_CALLBACKS', 'MGMT_USER_CALLBACKS_INT');
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_USER_CALLBACKS', 'MGMT_USER_CALLBACKS_INT');
    no_errors := 1;
  EXCEPTION
    WHEN OTHERS THEN
      EXECUTE IMMEDIATE 'ALTER TABLE MGMT_USER_CALLBACKS DROP CONSTRAINT MGMT_USER_CALLBACKS_PK';
      DBMS_REDEFINITION.ABORT_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_USER_CALLBACKS', 'MGMT_USER_CALLBACKS_INT');
  END;

  EXECUTE IMMEDIATE 'DROP TABLE MGMT_USER_CALLBACKS_INT';

  IF no_errors = 1 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_USER_CALLBACKS RENAME CONSTRAINT MGMT_USER_CALLBACKS_PK_INT TO MGMT_USER_CALLBACKS_PK';
    EXECUTE IMMEDIATE 'ALTER INDEX MGMT_USER_CALLBACKS_PK_INT RENAME TO MGMT_USER_CALLBACKS_PK';
  END IF;
END;
/



Rem
Rem New table definitions
Rem

rem
rem  PURPOSE
rem
rem   The MGMT_FLAT_ROLE_GRANTS table contains the list of flattened roles
rem   granted to a role. It does not contain the flattened list granted to a
rem   user.
rem
rem  COLUMNS
rem
rem   ROLE_GRANTEE - the role that has been granted the role
rem
rem   ROLE_NAME - the name of the granted role
rem
rem  NOTES
rem   The purpose of this table is to avoid the use of recursive queries
rem   that use CONNECT BY
rem
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE MGMT_FLAT_ROLE_GRANTS(role_grantee VARCHAR2(256) NOT NULL, role_name VARCHAR2(30) NOT NULL) MONITORING';

  EXECUTE IMMEDIATE 'CREATE INDEX mgmt_flat_role_grants_idx1 ON MGMT_FLAT_ROLE_GRANTS (role_grantee)';

  EXECUTE IMMEDIATE 'ALTER TABLE MGMT_FLAT_ROLE_GRANTS ADD CONSTRAINT mgmt_flat_role_grants_fk FOREIGN KEY (role_name) REFERENCES MGMT_ROLES (role_name) ON DELETE CASCADE';
EXCEPTION WHEN OTHERS
THEN
  NULL;
END;
/

rem   
rem  PURPOSE
rem
rem   The MGMT_USER_TEMPLATES table contains the list of template guids on which
rem   a user has VIEW_TEMPLATE privilege
rem
rem  COLUMNS
rem  
rem   GRANTEE - the user who has been granted the privilege
rem
rem   TEMPLATE_GUID - template guid
rem
CREATE TABLE MGMT_USER_TEMPLATES
(grantee       VARCHAR2(256), 
 template_guid RAW(16),
 CONSTRAINT MGMT_USER_TEMPLATES_PK PRIMARY KEY (grantee, template_guid)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;

rem
rem  PURPOSE
rem
rem   The MGMT_USER_REPORT_DEFS table contains the list of report defintion
rem   guids on which a user has VIEW_REPORT privilege
rem   
rem  COLUMNS 
rem
rem   GRANTEE - the user who has been granted the privilege
rem
rem   REPORT_GUID - report guid
rem
CREATE TABLE MGMT_USER_REPORT_DEFS
(grantee     VARCHAR2(256),
 report_guid RAW(16),
 CONSTRAINT MGMT_USER_REPORT_DEFS_PK PRIMARY KEY (grantee, report_guid)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;

rem
rem  PURPOSE
rem
rem   The MGMT_USER_CAS table contains the list of corrective actions ids on
rem   which a user has VIEW_CA privilege
rem   
rem  COLUMNS 
rem
rem   GRANTEE - the user who has been granted the privilege
rem
rem   JOB_ID - job id
rem
CREATE TABLE MGMT_USER_CAS
(grantee     VARCHAR2(256),
 job_id      RAW(16),
 CONSTRAINT MGMT_USER_CAS_PK PRIMARY KEY (grantee, job_id)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;

Rem
Rem Modify columns for increased size of user name
Rem
ALTER TABLE mgmt_privs MODIFY (creator VARCHAR2(256));
ALTER TABLE mgmt_role_grants MODIFY (grantee VARCHAR2(256));
ALTER TABLE mgmt_priv_grants MODIFY (grantee VARCHAR2(256));
ALTER TABLE mgmt_user_targets MODIFY (grantee VARCHAR2(256));
ALTER TABLE mgmt_user_jobs MODIFY (grantee VARCHAR2(256));
ALTER TABLE mgmt_created_users MODIFY (user_name VARCHAR2(256));


Rem
Rem Upgrade the MGMT_VIEW user
Rem

Rem Create public synonyms
create or replace public synonym MGMT_VIEW_UTIL FOR MGMT_VIEW_UTIL;
create or replace public synonym MGMT_MESSAGES FOR MGMT_MESSAGES;


Rem Drop the global context

DECLARE
 l_context_cnt NUMBER := 0;
BEGIN
  SELECT count(*) INTO l_context_cnt
    FROM dba_context 
   WHERE NAMESPACE = 'EM_GLOBAL_CONTEXT';
  
  IF l_context_cnt > 0 
  THEN
    EXECUTE IMMEDIATE 'DROP CONTEXT em_global_context';
  END IF;
END;
/

Rem
Rem Indices for MGMT_PRIV_GRANTS table
Rem
CREATE INDEX mgmt_priv_grants_idx01
 ON MGMT_PRIV_GRANTS(guid, direct_grant) COMPRESS 1;