Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\blackout\blackout_tables.sql
Rem drv: <create type="tables" params="EM_ECM_DEPOT_TABLESPACE"/> Rem Rem $Header: blackout_tables.sql 04-jan-2008.03:05:29 smudumba Exp $ Rem Rem blackout_tables.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem blackout_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 smudumba 12/27/07 - Blackout Reasons NLS Rem sbhagwat 09/19/06 - Retroactive Blackout Rem rkpandey 08/02/06 - Backport rkpandey_fix_host_notif from main Rem rkpandey 03/24/06 - Fix comments on MGMT_BLACKOUT_FLAT_TARGETS Rem rkpandey 07/20/06 - Backport rkpandey_bug-5061352 from main 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 skini 01/13/05 - PK constraint for reasons table Rem ktlaw 01/11/05 - add repmgr header Rem dcawley 07/07/04 - Increase user name size Rem skini 12/12/03 - Fix bug 3300351 Rem skini 12/05/03 - Add MGMT_BLACKOUT_PROXY_TARGETS Rem skini 09/25/03 - Convert repos columns to utc columns Rem skini 08/28/03 - Add occurrence_number column to bo windows table Rem skini 08/19/03 - Add unique key Rem skini 08/19/03 - Add blackout occurrence, history table Rem skini 06/18/03 - Add MONITORING clause Rem rpinnama 02/27/03 - Parameterize the ECM depot table space Rem skini 12/09/02 - Add status to primary key Rem skini 11/24/02 - Add repos timestamps to windows table Rem skini 11/12/02 - Add status to blackout window table Rem skini 11/06/02 - Remove primary key constraint from BW table Rem skini 09/18/02 - blackout windows Rem kduvvuri 09/04/02 - update the comments for MGMT_BLACKOUT_STATE w.r.t blackout_code and target_status Rem skini 08/25/02 - Add edit_state to flat targets table Rem skini 08/20/02 - New blackout states Rem skini 08/02/02 - Move job_ids to blackouts table Rem skini 07/26/02 - Change schedule table to refer to blackouts Rem skini 07/25/02 - Change schedule table definition Rem skini 07/23/02 - Handle overlapping blackouts Rem skini 07/17/02 - Continue blackout development Rem rpatti 07/18/02 - remove post creation sql Rem skini 07/17/02 - Continue blackout development Rem skini 06/20/02 - Fix syntax Rem skini 06/14/02 - Continue implementing blackout support Rem skini 06/12/02 - Create blackout schedule table Rem skini 06/05/02 - Repository blackout project Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/14/02 - Created Rem rem rem This script requires the following arguments rem 1. Name of the ecm_depot tablespace where all LOBs are stored rem DEFINE EM_ECM_DEPOT_TABLESPACE = "&1" rem rem MGMT_BLACKOUT_SCHEDULE rem rem PURPOSE rem The schedule table rem rem COLUMNS rem BLACKOUT_GUID The id of the schedule rem SCHEDULE_INDEX The index of the schedule rem FREQUENCY_CODE The frequency code (type) of the schedule. rem Possible Values are rem 1 ONE_TIME_FREQUENCY_CODE rem 2 INTERVAL_FREQUENCY_CODE rem 3 DAILY_FREQUENCY_CODE rem 4 WEEK_FREQUENCY_CODE rem 5 MONTH_FREQUENCY_CODE rem 6 YEAR_FREQUENCY_CODE rem rem START_TIME The schedule start time. Use SYSDATE if the job is to rem start immediately. For daily, days of week and days of month rem schedules, the start_time denotes when the job should start. rem Only the time part is used, and the date part is ignored. rem rem END_TIME For all periodic schedules, the last date (and time) to rem run the job. For daily, day of week and day of month schedules, rem only the date portion is used. For interval schedules, the time is rem taken into account as well. rem rem EXECUTION_HOURS The "execution time" for daily, day of rem week/month/year schedules. Indicates the time of the day at which rem the job will execute. Hours are specified in 24-hour format rem (0 to 23), and minutes is a number between 0 and 59. rem rem EXECUTION_MINUTES See EXECUTION_HOURS rem rem INTERVAL The interval, in minutes, for interval jobs. rem rem MONTHS For days-of-year schedules, this indicates the "month" rem portion of each (month, day) pair in the schedule. The "days" rem portion is specified using "days", below. rem rem DAYS For day of week/month jobs, the day(s) of the week, rem or month as the case may be. rem For days of week, this is a set of numbers from 1-7 indicating rem the days sunday through saturday, in that order. rem For days of month, this is a set of numbers from 1-31 indicating the rem date of the month. A special constant called LAST_DAY (-1) can be used rem for day-of-month schedules to indicate the last day of the month rem For days of the year, this is a set of numbers from 1-32 indicating rem the date of the month. A special constant called LAST_DAY (-1) can rem be used for day-of-year schedules to indicate the last day of the month. rem For day-of-year schedules, the corresponding months are taken from the rem "months" field above rem rem DURATION This applies to blackout schedules only. This indicates rem the duration of the blackout, in minutes rem rem TIMEZONE_INFO Specifies which timezone the start and end times rem are specified in rem TIMEZONE_REPOSITORY: The timezone of the repository rem TIMEZONE_SPECIFIED: The timezone delta is specified, and stored rem in the TIMEZONE_OFFSET column rem rem rem TIMEZONE_OFFSET The offset between GMT and the timezone of the rem schedule. Only relevant when timezone_info=TIMEZONE_SPECIFIED rem rem DURATION_SOURCE Specifies whether the duration came from the rem duration or end time. Possible values are rem 1 End time rem 2 Duration rem 3 Indefinite rem rem NOTES rem rem CREATE TABLE MGMT_BLACKOUT_SCHEDULE ( blackout_guid RAW(16), schedule_index NUMBER(3), frequency_code NUMBER(2), start_time DATE, end_time DATE, execution_hours NUMBER(3), execution_minutes NUMBER(3), interval NUMBER, months MGMT_JOB_INT_ARRAY, days MGMT_JOB_INT_ARRAY, duration NUMBER DEFAULT 0, timezone_info NUMBER(1), timezone_offset NUMBER, timezone_region VARCHAR2(64), duration_source NUMBER(1) DEFAULT 1 ) TABLESPACE &EM_ECM_DEPOT_TABLESPACE MONITORING; rem rem PURPOSE rem rem Blackout master table rem rem The MGMT_BLACKOUTS table contains information on rem 1. what blackouts are created. rem 2. General information such as description, annotation(reason), and rem schedule of the blackout rem 3. The blackout is created from GUI or CLI. rem 4. Blackout history : User who last modified the blackout. rem rem COLUMNS rem rem BLACKOUT_GUID - The unique is of this blackout. rem rem BLACKOUT_NAME - Display name of the blackout. rem rem REASON_ID - Purpose of the blackout. requirement from GIT. rem rem BLACKOUT_DESC - More description of this blackout. rem rem BLACKOUT_STATUS - status of blackout rem blackout_status is a number from 0-2 rem 0 - PENDING rem 1 - SCHEDULED rem 2 - STARTED rem 3 - COMPLETED rem rem rem JOB_FLAG - A flag that indicates whether jobs should execute rem during the blackout rem rem rem CREATED_BY - User who creates this blackout. EMD would be sending rem <SYSTEM> for blackouts created from CLI. rem rem CREATED_THRU - Contains the EMD_URL for blackouts created by CLI. rem don't care for blackouts from console. rem rem LAST_UPDATED_BY - User who last updated/modified this blackout. rem rem LAST_UPDATED_TIME - Date/Time when this blackoutwas last updated. rem rem START_JOB_ID - The id of the job that starts the blackout rem rem END_JOB_ID - The id of the job that ends the blackout rem rem OCCURRENCE_NUMBER - The blackout occurrence number rem NOTES rem rem CREATE TABLE MGMT_BLACKOUTS (blackout_guid RAW(16) NOT NULL, blackout_name VARCHAR2(64) NOT NULL, reason_id NUMBER, blackout_desc VARCHAR2(2000), blackout_status NUMBER(2) DEFAULT 0, job_flag NUMBER(1) DEFAULT 0, created_by VARCHAR2(256) NOT NULL, created_thru VARCHAR2(256), last_updated_by VARCHAR2(256), last_updated_time DATE DEFAULT SYSDATE, last_start_time DATE DEFAULT NULL, last_end_time DATE DEFAULT NULL, scheduled_time DATE DEFAULT NULL, start_job_id RAW(16) DEFAULT NULL, end_job_id RAW(16) DEFAULT NULL, occurrence_number NUMBER(9) DEFAULT 1, CONSTRAINT blackouts_primary_key PRIMARY KEY (blackout_guid) ) STORAGE (FREELISTS 4) INITRANS 2 MONITORING; rem rem PURPOSE rem rem The MGMT_BLACKOUT_TARGET_DETAILS table contains blackout information rem per target. rem rem COLUMNS rem rem BLACKOUT_GUID - The unique id of the blackout. rem TARGET_GUID - The unique id of target rem INCLUDE_MEMBERS - True (1) if member targets are to be considered rem for host, composite targets and groups. This has no effect on rem other types of targets rem LAST_UPDATED_TIME - Date/time when this blackout info was last updated rem EDIT_STATE - For internal use only; used when editing the blackout rem CREATE TABLE MGMT_BLACKOUT_TARGET_DETAILS (blackout_guid RAW(16) NOT NULL, target_guid RAW(16) NOT NULL, include_members NUMBER(1) DEFAULT 0, last_updated_time DATE DEFAULT SYSDATE, edit_state NUMBER(1) DEFAULT 0 ) STORAGE (FREELISTS 4) INITRANS 2 MONITORING; rem rem PURPOSE rem rem The MGMT_BLACKOUT_FLAT_TARGETS table contains blackout information rem per each flattened out target. rem rem COLUMNS rem rem BLACKOUT_GUID - The unique id of the blackout. rem TARGET_GUID - The unique id of target rem HOST_BLACKOUT - True (1) for a host if a host blackout rem must be performed on the agent. Has no effect on other rem target types. rem JOB_STATUS - status of the job submission rem 0 - START PENDING rem 1 - START FAILURE rem 2 - START SUCCESS rem 3 - STOP PENDING rem 4 - STOP FAILURE rem 5 - STOP SUCCESS rem BLACKOUT_STATUS - current status of the blackout on this target rem 1 - In blackout rem 0 - Not in blackout rem ERROR_MESSAGE - error message from job rem LAST_UPDATED_TIME - Date/time whenthis blackout info was last updated rem CREATE TABLE MGMT_BLACKOUT_FLAT_TARGETS (blackout_guid RAW(16) NOT NULL, target_guid RAW(16) NOT NULL, host_blackout NUMBER(1) DEFAULT 0, job_status NUMBER(2) DEFAULT 0, blackout_status NUMBER(2) DEFAULT 0, error_message VARCHAR2(2000) DEFAULT NULL, last_updated_time DATE DEFAULT SYSDATE, edit_state NUMBER DEFAULT 0 ) MONITORING; rem rem rem PURPOSE rem rem The MGMT_BLACKOUT_REASON table contains blackout reason rem information. rem rem COLUMNS rem rem REASON_ID - The unique id of reason rem REASON - The reason. rem REASON_NLS_ID - The NLS ID for the reason CREATE TABLE MGMT_BLACKOUT_REASON (reason_id NUMBER NOT NULL, reason VARCHAR2(64) NOT NULL, reason_nls_id VARCHAR2(256) DEFAULT NULL, CONSTRAINT PK_BLACKOUT_REASON PRIMARY KEY(reason_id) ) MONITORING; rem rem rem PURPOSE rem rem The MGMT_BLACKOUT_HISTORY table keeps track of blackout history rem (for each occurrence of the blackout) rem rem COLUMNS rem rem BLACKOUT_GUID - The blackout guid rem OCCURRENCE_NUMBER - The blackout occurrence number rem START_TIME - The start time of the blackout rem END_TIME - The end time of the blackout rem CREATE TABLE MGMT_BLACKOUT_HISTORY (blackout_guid RAW(16), occurrence_number NUMBER(9) DEFAULT 0, start_time DATE, end_time DATE, CONSTRAINT PK_BLK_HISTORY PRIMARY KEY(blackout_guid, occurrence_number), CONSTRAINT FK_BLK_HISTORY FOREIGN KEY(blackout_guid) REFERENCES MGMT_BLACKOUTS(blackout_guid) ON DELETE CASCADE ) MONITORING; rem rem PURPOSE rem rem The MGMT_BLACKOUT_REASON_SEQ sequence is used to allocate unique rem IDs for reason table rem rem NOTES rem CREATE SEQUENCE MGMT_BLACKOUT_REASON_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER; rem rem PURPOSE rem rem The MGMT_BLACKOUT_STATE table contains the information on blackout rem start and end periods for a target rem rem rem COLUMNS rem rem BLACKOUT_GUID - The unique id of the blackout resulting in this rem blackout. rem rem TARGET_GUID - The unique id of the target rem rem COLLECTION_TIMESTAMP rem rem - the date/time when the alert was triggered rem rem BLACKOUT_CODE rem rem - a code that indicates whether the record contains a rem start or end of a blackout period rem rem 1 - blackout start rem 0 - blackout end rem rem TARGET_STATUS rem - a code that indicates whether the target has entered or rem exited a blackout. rem rem 1 - blackout start. rem 0 - blackout end. rem 2 - in blackout. rem rem NOTES rem rem CREATE TABLE MGMT_BLACKOUT_STATE (blackout_guid RAW(16), target_guid RAW(16) NOT NULL, collection_timestamp DATE NOT NULL, load_timestamp DATE DEFAULT SYSDATE, blackout_code NUMBER(1) NOT NULL, target_status NUMBER(1) ) STORAGE (FREELISTS 4) INITRANS 2 MONITORING; CREATE PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS FOR MGMT_BLACKOUT_STATE; rem rem PURPOSE rem rem The MGMT_BLACKOUT_WINDOWS table keeps a record of the rem start and end periods for a blackout and target. Windows rem could also indicate future times (ie when the specified rem blackout has not yet started) rem rem rem COLUMNS rem rem BLACKOUT_GUID - The unique id of the blackout resulting in this rem blackout. rem rem TARGET_GUID - The unique id of the target rem rem START_TIME, END_TIME: The start and end times of the blackout. rem For indefinite blackouts, end_time is null. rem rem UTC_START_TIME, UTC_END_TIME: The start and end times of the rem blackout in utc, used by the job system to put executions under rem blackout rem CREATE TABLE MGMT_BLACKOUT_WINDOWS ( target_guid RAW(16), end_time DATE, start_time DATE, utc_start_time DATE, utc_end_time DATE, blackout_guid RAW(16), occurrence_number NUMBER(9) DEFAULT 1, status NUMBER(2) DEFAULT 0, CONSTRAINT PK_MGMT_BWINDOWS PRIMARY KEY(blackout_guid, target_guid, start_time, status) ) ORGANIZATION INDEX MONITORING; REM REM PURPOSE REM REM This table is used as a proxy table for locking rows in the REM blackout state insert trigger. This is an internal table only REM and is there because we want to avoid locking MGMT_TARGETS REM CREATE TABLE MGMT_BLACKOUT_PROXY_TARGETS ( target_guid RAW(16), CONSTRAINT PK_BLK_PROXY_TARGETS PRIMARY KEY(target_guid) ) MONITORING; -- Calling the table creation script from the migration path @&EM_SQL_ROOT/core/10.2.0.3/blackout/blackout_tables.sql
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de