Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/emregistry/latest/emlcm_admin_tables.sql /main/1 2022/10/08 03:52:44 spramani Exp $
Rem
Rem emlcm_admin_tables.sql
Rem
Rem Copyright (c) 2022, Oracle and/or its affiliates.
Rem
Rem    NAME
Rem      emlcm_admin_tables.sql -LCM ADMIN related tables  
Rem
Rem    DESCRIPTION
Rem      NONE 
Rem
Rem    NOTES
Rem      NONE 
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/emregistry/latest/emlcm_admin_tables.sql
Rem    SQL_SHIPPED_FILE:
Rem    SQL_PHASE:
Rem    SQL_STARTUP_MODE: NORMAL
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    spramani    09/20/22 - review comments
Rem    spramani    09/14/22 - compression added
Rem    spramani    09/08/22 - LCM Admin
Rem    spramani    09/08/22 - Created
Rem

DEFINE MY_REPOS_USER = "&1"
DEFINE MY_DBA_USER = "&2"

ALTER SESSION SET CURRENT_SCHEMA=&MY_REPOS_USER;

 
DECLARE
l_tab_sql VARCHAR2(2000) :=
'CREATE TABLE MGMT_LCM_ADMINS_E
(id            INT            NOT NULL,
 lcm_admin     VARCHAR2(64)   NOT NULL,
 lcm_op        NUMBER NOT NULL,
 started       DATE DEFAULT SYSDATE NOT NULL,
 completed     DATE, 
 PRIMARY KEY (id) USING INDEX compress advanced high           
) ROW STORE COMPRESS ADVANCED';

l_prop_tab_sql VARCHAR2(2000) := 
'CREATE TABLE MGMT_LCM_PROPERTIES_E
 (property_name VARCHAR2(256) NOT NULL, 
   property_value VARCHAR2(1024) NOT NULL,
   CONSTRAINT MGMT_LCM_PROPERTIES_PK
   PRIMARY KEY (property_name) USING INDEX compress advanced high
 ) ROW STORE COMPRESS ADVANCED';

l_seq_sql VARCHAR2(2000) := 
'CREATE SEQUENCE MGMT_LCM_ADMIN_SEQ
 INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28
 MINVALUE 1 CYCLE CACHE 20 ORDER';


l_count NUMBER(2);
BEGIN
BEGIN
  SELECT count(*) 
    INTO l_count
    FROM dba_objects
    WHERE object_name='MGMT_LCM_PROPERTIES_E'
    AND owner='&MY_REPOS_USER';

 IF l_count = 0 THEN
   EXECUTE IMMEDIATE l_tab_sql;
   EXECUTE IMMEDIATE l_prop_tab_sql;
   EXECUTE IMMEDIATE l_seq_sql;
 END IF;
 EXCEPTION WHEN OTHERS THEN
   raise;
 END;
END;
/

create or replace editioning view MGMT_LCM_ADMINS as select * from MGMT_LCM_ADMINS_E;
create or replace editioning view MGMT_LCM_PROPERTIES as select * from MGMT_LCM_PROPERTIES_E; 

ALTER SESSION SET CURRENT_SCHEMA=&MY_DBA_USER;

