Rem drv: <create type="tables" params="EM_ECM_DEPOT_TABLESPACE"/>
Rem
Rem $Header: admin_tables.sql 14-may-2007.01:03:18 sradhakr Exp $
Rem
Rem admin_tables.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_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    sradhakr    05/14/07 - RFI BACKPORT OF Bug 5869512 FOR INCLUSION IN
Rem                           10.2.0.4 (RFI #55489).
Rem    gsbhatia    06/26/05 - New repmgr header impl 
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    apbharga    02/11/05 - finalizing
Rem    apbharga    01/24/05 - new tables for proxy server and cache project 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    jpyang      12/08/03 - change username length 
Rem    jpyang      11/23/03 - add license table 
Rem    rpinnama    07/11/03 - Add repository mode column
Rem    sgrover     01/10/03 - add rebuild interval table
Rem    mbhoopat    09/25/02 - Add OMS/repository version validation support
Rem    rpinnama    05/16/02 - Add mgmt_versions table.
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

rem
rem  PURPOSE
rem
rem     The MGMT_VERSIONS table contains the current version numbers of
rem     various components in the repository.
rem  
rem
rem  COLUMNS
rem
rem     COMPONENT_NAME      - the name of the component
rem
rem     VERSION             - the version number of the component
rem
rem     COMPAT_CORE_VERSION - The version of core needed for the component
rem                          to work correctly.
rem                          For 'CORE' component this indicates the core 
rem                          version up to which the backward compatibility 
rem                          will be supported.
rem
rem     COMPONENT_MODE      - the mode in which the component is created
rem                           in the repository. Currently, there are two modes :
rem                           'CENTRAL'
rem                           'SYSAUX'
rem
rem     STATUS             - the status of the component.
rem                          0 - READY
rem                          1 - CREATING
rem                          2 - UPGRADING
rem
rem  NOTES
rem
rem
rem

CREATE TABLE MGMT_VERSIONS
    (component_name      VARCHAR2(32)    NOT NULL,
     version             VARCHAR2(32)    NOT NULL,
     compat_core_version VARCHAR2(32)    NOT NULL,
     component_mode      VARCHAR2(32)    DEFAULT 'CENTRAL',
     status              NUMBER          DEFAULT 0)
     MONITORING;

rem
rem  PURPOSE
rem
rem     The MGMT_TABLE_SIZES table contains size information records for all
rem     tables in the MGMT schema for 30 days.
rem
rem  COLUMNS
rem
rem     TABLE_NAME         - the name of the table
rem
rem     ALLOCATED_SIZE     - the total amount of space allocated to the table
rem
rem     SPACE_USED         - the amount of space used in the allocated space
rem
rem  NOTES
rem
rem
rem

CREATE TABLE MGMT_TABLE_SIZES
    (table_name           VARCHAR2(30)    NOT NULL,
     collection_timestamp DATE            DEFAULT SYSDATE,
     allocated_size       NUMBER          DEFAULT 0,
     space_used           NUMBER          DEFAULT 0)
  MONITORING;

rem
rem  PURPOSE
rem
rem     The MGMT_INDEX_SIZES table contains size information records for all
rem     indexes in the MGMT schema for 30 days.
rem
rem  COLUMNS
rem
rem     INDEX_NAME         - the name of the index
rem
rem     TABLE_NAME         - the name of the table the index is on
rem
rem     ALLOCATED_SIZE     - the total amount of space allocated to the index
rem
rem     SPACE_USED         - the amount of space used in the allocated space
rem
rem  NOTES
rem
rem
rem

CREATE TABLE MGMT_INDEX_SIZES
    (index_name           VARCHAR2(30)    NOT NULL,
     table_name           VARCHAR2(30)    NOT NULL,
     collection_timestamp DATE            DEFAULT SYSDATE,
     allocated_size       NUMBER          DEFAULT 0,
     space_used           NUMBER          DEFAULT 0)
  MONITORING;

rem
rem  PURPOSE
rem
rem     The MGMT_REBUILD_INDEXES table contains schedule information for 
rem     rebuilding indexes.
rem
rem  COLUMNS
rem
rem     TABLE_NAME         - the name of the table
rem
rem     INTERVAL           - the number of days between rebuilds
rem
rem     TABLE_TYPE         - type of table, only NULL or IOT
rem
rem  NOTES
rem
rem
rem

CREATE TABLE MGMT_REBUILD_INDEXES
    (table_name           VARCHAR2(30)    NOT NULL,
     interval             NUMBER          NOT NULL,
     table_type           VARCHAR2(10)    DEFAULT NULL,
     CONSTRAINT MGMT_REBUILD_INDEXES_PK PRIMARY KEY (table_name))
  MONITORING;

rem
rem  PURPOSE
rem
rem     The MGMT_LICENSES table contains license information of  
rem     EM users.
rem
rem  COLUMNS
rem
rem     USERNAME         - name of EMUSER
rem
rem     TIMESATMP        - the time user read and clicks I agree
rem
rem     CHECK_DONT_SHOW  - if user marks the don't show me again
rem                        checkbox or not
rem
rem  NOTES
rem
rem
rem

CREATE TABLE MGMT_LICENSES
    (username            VARCHAR2(256)    NOT NULL,
     timestamp           DATE             DEFAULT SYSDATE,
     i_agree             VARCHAR2(32)    DEFAULT 'NO')
     MONITORING;

rem
rem  PURPOSE
rem
rem     The MGMT_URL_PROXY table contains information of all the internet
rem     Proxy Servers in the enterprise.
rem
rem  COLUMNS
rem
rem     PROTOCOL         - the proxy protocol (e.g. HTTP, FTP)
rem
rem     HOST_NAME        - hostname (of the proxy server)
rem
rem     PORT_NUMBER      - the port on the HOST (proxy server is listening on this port)
rem
rem     LOCAL_HOST       - the host that will be using this proxy (this is to support multi-OMS configurations)
rem
rem     CREATED_ON       - when this entry was created
rem
rem     IS_DEFAULT       - is this the default proxy to use ?
rem
rem     DONT_PROXY_FOR   - comma seperated list of all hosts, domains etc for
rem                        which the proxy server will be by-passed
rem
rem     PROXY_REALM      - some proxy servers might need additional authentication
rem                        realm identifies the network which the user is part of
rem
rem     PROXY_USER       - the proxy user id / name
rem
rem     PROXY_PASSWORD   - the password that identifies the user
rem
rem  NOTES
rem
rem
rem

CREATE TABLE MGMT_URL_PROXY
    (PROTOCOL             VARCHAR2(32)   NOT NULL,
     HOST_NAME            VARCHAR2(128)  NOT NULL,
     PORT_NUMBER          NUMBER         NOT NULL,
     LOCAL_HOST           VARCHAR2(128)  NOT NULL,
     CREATED_ON           DATE           DEFAULT SYSDATE,
     IS_DEFAULT           VARCHAR2(32)   DEFAULT 'NO',
     DONT_PROXY_FOR       VARCHAR2(1024),
     PROXY_REALM          VARCHAR2(256),
     PROXY_USER           VARCHAR2(32),
     PROXY_PASSWORD       VARCHAR2(32),
     CONSTRAINT MGMT_URL_PROXY_PK PRIMARY KEY (PROTOCOL, HOST_NAME, PORT_NUMBER, LOCAL_HOST))
     MONITORING;

rem
rem  PURPOSE
rem
rem     The MGMT_URL_CACHE table stores the contents (resources) of a URL
rem     as a BLOB in the table row.
rem     This will enable resource cache enables clients to work in a
rem     disconnected fasion - use URL resource even when not connected to
rem     the internet.
rem
rem  COLUMNS
rem
rem     URL              - the URL
rem
rem     CREATED_ON       - when this row was created
rem
rem     CONTENT_TYPE     - the type of the content (CHAR, BINARY etc)
rem
rem     CONTENT_LENGTH   - the size of the content BLOB
rem
rem     CONTENT          - the resource
rem
rem  NOTES
rem
rem
rem

CREATE TABLE MGMT_URL_CACHE
    (
     URL                VARCHAR2(256)    NOT NULL,
     CREATED_ON         DATE             DEFAULT SYSDATE,
     CONTENT_TYPE       VARCHAR2(32),
     CONTENT_LENGTH     NUMBER,
     CONTENT            BLOB,
     CONSTRAINT MGMT_URL_CACHE_PK PRIMARY KEY (URL)
    ) MONITORING
     LOB(CONTENT) STORE AS ( TABLESPACE &EM_ECM_DEPOT_TABLESPACE );