Rem drv: <migrate type= "schema_upgrade" version= "10.2.0.1"/>
Rem
Rem $Header: user_model_schema_upgrade.sql 23-sep-2005.08:32:43 dcawley Exp $
Rem
Rem user_model_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. 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    dcawley     09/23/05 - Add PK to flat table before redefinition 
Rem    dcawley     09/01/05 - Truncate flat role table 
Rem    dcawley     08/30/05 - dcawley_bug-4578680
Rem    dcawley     08/30/05 - Ad header 
Rem    dcawley     08/29/05 - Created
Rem

Rem Create the new table definition for the MGMT_FLAT_ROLE_GRANTS table

CREATE TABLE MGMT_FLAT_ROLE_GRANTS_INT
(role_grantee    VARCHAR2(256) NOT NULL,
 role_name       VARCHAR2(30) NOT NULL,
 CONSTRAINT MGMT_FLAT_ROLE_GRANTS_PK_INT PRIMARY KEY (role_grantee, role_name)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;

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

    -- Clean up the table since there was a problem with the previous
    -- version of UPDATE_FLAT_ROLE_GRANTS
    EXECUTE IMMEDIATE 'TRUNCATE TABLE MGMT_FLAT_ROLE_GRANTS';

    -- Add a primary key to allow for redefinition to occur
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_FLAT_ROLE_GRANTS ADD CONSTRAINT mgmt_flat_role_grants_pk PRIMARY KEY (role_grantee, role_name)';

    DBMS_REDEFINITION.START_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_FLAT_ROLE_GRANTS', 'MGMT_FLAT_ROLE_GRANTS_INT');
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_FLAT_ROLE_GRANTS', 'MGMT_FLAT_ROLE_GRANTS_INT');
    no_errors := 1;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_REDEFINITION.ABORT_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_FLAT_ROLE_GRANTS', 'MGMT_FLAT_ROLE_GRANTS_INT');

     EXECUTE IMMEDIATE 'ALTER TABLE MGMT_FLAT_ROLE_GRANTS DROP CONSTRAINT mgmt_flat_role_grants_pk'; 
  END;

  EXECUTE IMMEDIATE 'DROP TABLE MGMT_FLAT_ROLE_GRANTS_INT';

  IF no_errors = 1 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_FLAT_ROLE_GRANTS RENAME CONSTRAINT MGMT_FLAT_ROLE_GRANTS_PK_INT TO MGMT_FLAT_ROLE_GRANTS_PK';
    EXECUTE IMMEDIATE 'ALTER INDEX MGMT_FLAT_ROLE_GRANTS_PK_INT RENAME TO MGMT_FLAT_ROLE_GRANTS_PK';
    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';

  END IF;
END;
/

Rem
Rem Indices for MGMT_ROLE_GRANTS table
Rem
CREATE INDEX mgmt_role_grants_idx1
  ON MGMT_ROLE_GRANTS (role_name);