105_cr_dba01.ksh


#!/bin/ksh
# This script assigns the user that will be used in oracle to access tables in APEX
# M. Culp
# 11/06/2010
# 01/04/2010 Made changes to reflect the LS commands
# 02/03/2010
# This should give access to the user in APEX that this is going against
# connect dba01/password;
# this incorporates the apl table from 106
#######################################################################################################

sqlplus -s "/ as sysdba" <<EOF

spool  105_cr_dba01.log

alter session set current_schema=DBA01;

-- This is for the security
drop table global_user_role;

create table global_user_role
   (gur_user_id VARCHAR2(10),
    gur_usr_rl_nm VARCHAR2(10)
   );

-- Add at least one person in there to get in
insert into global_user_role values('MCULP','ADMIN');
insert into global_user_role values('DVENUS','ADMIN');


drop table global_role;

CREATE TABLE global_role
   (    gr_role_name         VARCHAR2(10),
        gr_rl_dscr           VARCHAR2(200)
   ) ;


insert into global_role (gr_role_name,gr_rl_dscr) values('ADMIN','Master Admin');
insert into global_role (gr_role_name,gr_rl_dscr) values('MGMT','Management View');
insert into global_role (gr_role_name) values('DBA1');
insert into global_role (gr_role_name) values('DBA2');
insert into global_role (gr_role_name) values('DBA');
insert into global_role (gr_role_name) values('VIEW1');
insert into global_role (gr_role_name) values('VIEW2');
insert into global_role (gr_role_name) values('UNIX');


drop table cst_awr;

CREATE TABLE  CST_AWR
   (    CST_AWR_CST_ID VARCHAR2(10),
        CST_AWR_IMP_NO VARCHAR2(10),
        CST_AWR_DT DATE,
        CST_AWR_DB_ID NUMBER,
        CST_AWR_CMT VARCHAR2(4000),
        CST_AWR_SNP_STRT NUMBER,
        CST_AWR_SNP_END NUMBER
   ) ;

drop table customer;

CREATE TABLE CUSTOMER
   (    CST_ID      NUMBER,
        CST_NM      VARCHAR2(100),
        CST_TP      NUMBER,
        CST_TEL_NBR VARCHAR2(15),
        CST_EML     VARCHAR2(100),
        CST_CMT     VARCHAR2(4000),
        CST_PRI_CNT VARCHAR2(75),
        CST_PRI_TEL VARCHAR2(15),
        CST_PRI_EML VARCHAR2(100),
        CST_LOB     VARCHAR2(20),
        CST_DPT     VARCHAR2(20),
        CST_BUS_OWR VARCHAR2(20),
        CST_PYMT_TP VARCHAR2(3)
   ) ;


drop table database;

CREATE TABLE DATABASE
   (    DB_ID         VARCHAR2(10),
        DB_NM         VARCHAR2(20),
        DB_DSCR       VARCHAR2(1000),
        DB_CMT        VARCHAR2(4000),
        DB_QADT       DATE,
        DB_INST_NM    VARCHAR2(15),
        DB_TP         VARCHAR2(3),
        DB_VER        VARCHAR2(3),
        DB_OS         VARCHAR2(3),
        DB_OS_VER     VARCHAR2(3),
        DB_UPG_VER    VARCHAR2(3),
        DB_UPG_STAT   VARCHAR2(3),
        DB_DBA_CRT    VARCHAR2(3),
        DB_APL        VARCHAR2(3),
        DB_DBA_CUR    VARCHAR2(3),
        DB_CRT_DT     DATE,
        DB_DT_NXT_UPG DATE,
        DB_DT_UPG     DATE,
        DB_CMP        VARCHAR2(3),
        DB_DBA_PRIM   VARCHAR2(3),
        DB_DBA_SECD   VARCHAR2(3),
        DB_CST_ID     NUMBER,
        DB_CPU        NUMBER,
        DB_DSK_USED   NUMBER,
        DB_DSK_ALLOC  NUMBER,
-- these are really more instance level parameters
        DB_MEM_PGA    NUMBER,
        DB_MEM_SGA    NUMBER,
        DB_MEM_TGT    NUMBER,
-- this is the database DBID
        DB_DBID       VARCHAR2(25),
        DB_BKP_FRQ    VARCHAR2(25),
        DB_SVC_NM     VARCHAR2(25),
        DB_CAT_TP     VARCHAR2(25),
        DB_DBA_LU     VARCHAR2(25),
        DB_DBA_NU     VARCHAR2(25),
        DB_MCH_ID     VARCHAR2(10)
   );

drop table dba;

CREATE TABLE DBA
   (    DBA_ID      NUMBER,
        DBA_FNAME   VARCHAR2(40),
        DBA_LNAME   VARCHAR2(40),
        DBA_TEL_01  VARCHAR2(20),
        DBA_TEL_02  VARCHAR2(20),
        DBA_EML     VARCHAR2(100),
        DBA_DUKE_ID VARCHAR2(10),
        DBA_CMP_ID  VARCHAR2(10)
   );

drop TABLE DB_TYPE ;

CREATE TABLE DB_TYPE
   (    DB_TYP_ID   VARCHAR2(3),
        DB_TYP_DSC  VARCHAR2(100)
   );

drop TABLE HOSTINST;

CREATE TABLE HOSTINST
   (    HI_ID       VARCHAR2(4),
        HI_INST     VARCHAR2(15),
        HI_DB_VER   VARCHAR2(3),
        HI_RCVY_REQ VARCHAR2(3)
   ) ;


drop table machine;

CREATE TABLE MACHINE
   (    MCH_ID          VARCHAR2(10),
        MCH_NM          VARCHAR2(80),
        MCH_DSCR        VARCHAR2(1000),
        MCH_IP          VARCHAR2(15),
        MCH_CMT         VARCHAR2(4000),
        MCH_STS         VARCHAR2(4),
        MCH_HSTNME      VARCHAR2(80),
        MCH_LOGIN       VARCHAR2(40),
        MCH_PSWD        VARCHAR2(40),
        MCH_UNAME       VARCHAR2(40),
        MCH_OS_TP       VARCHAR2(4),
        MCH_CMP         VARCHAR2(3),
        MCH_RCVY_REQ    VARCHAR2(3),
        MCH_OS_VER      VARCHAR2(3),
        MCH_MEM_SZE     VARCHAR2(4),
        MCH_MKE         VARCHAR2(4),
        MCH_MDL         VARCHAR2(3),
        MCH_PRC_NO      VARCHAR2(3),
        MCH_UPTIME      TIMESTAMP (6),
        MCH_DNS_NME     VARCHAR2(40),
        MCH_LOB         VARCHAR2(15),
        MCH_TP          VARCHAR2(15),
        MCH_MEM_SZ      number,
        MCH_MAX_MEM_SZE number,
        MCH_PRC_CNT     number,
        MCH_VPRC_CNT    number,
        MCH_FRM         VARCHAR2(15),
        MCH_VIP         VARCHAR2(15),
        MCH_IP_EN1      VARCHAR2(15),
        MCH_IP_SCN01    VARCHAR2(15),
        MCH_IP_SCN02    VARCHAR2(15),
        MCH_IP_SCN03    VARCHAR2(15),
        MCH_CLST_NM     VARCHAR2(15),
        MCH_MNF         VARCHAR2(15),
        MCH_PWD         VARCHAR2(15),
        MCH_DNS_NM      VARCHAR2(15)
   ) ;


drop table saw;

create table saw
   ( saw_id                 number,
     saw_lob                varchar2(10),
     saw_clst_nm            varchar2(20),
     saw_hstnm              varchar2(20),
     saw_db_nm              varchar2(20),
     saw_piccaso_nbr        varchar2(15),
     saw_piccaso_dt         date,
     saw_cust_rqst_dt       date,
     saw_lpar_dlv_dt        date,
     saw_gi_bld_dt          date,
     saw_dbct_bld_dt        date,
     saw_cpl_dt             date,
     saw_prj_nm             varchar2(20),
     saw_tot_cpu            number,
     saw_new_srv_bld        varchar2(1),
     saw_apvl_dt            date,
     saw_sa                 varchar2(25),
     saw_cmt                varchar2(4000),
     saw_srv_nm             varchar2(25),
     saw_cge_mthd           varchar2(25),
     saw_inst_cge_cpu       varchar2(25),
     saw_tot_lpar_mem       varchar2(25),
     saw_vrtl_ip            varchar2(18),
     saw_pub_ip             varchar2(18),
     saw_priv_ip            varchar2(18),
     saw_scan_ip_1          varchar2(18),
     saw_scan_ip_2          varchar2(18),
     saw_scan_ip_3          varchar2(18),
     saw_sox_stat           varchar2(18),
     saw_prd_stat           varchar2(18),
     saw_tier               varchar2(18),
     saw_perf_lvl           varchar2(18),
     saw_pri_cntt_grp       varchar2(18),
     saw_pri_cntt           varchar2(18),
     saw_bus_own            varchar2(18),
     saw_allcn_pl_nm        varchar2(18),
     saw_bus_unit           varchar2(18),
     saw_lcl_cntt           varchar2(18),
     saw_aggr_dsk_iops      number,
     saw_mem_rqst           number,
     saw_aix_ver            number,
     saw_lpar_dsk_chnl      number,
     saw_lpar_dsk_chnl_typ  varchar2(25),
     saw_lpar_dsk_chnl_spd  number,
     saw_dba_mem_rqst       number
   ) ;


drop table cls;

CREATE TABLE CLS
   (    CLS_ID       NUMBER,
        CLS_NM       VARCHAR2(20),
        CLS_SCN_NM   VARCHAR2(20),
        CLS_SCN_IP01 VARCHAR2(20),
        CLS_SCN_IP02 VARCHAR2(20),
        CLS_SCN_IP03 VARCHAR2(20)
   ) ;


drop table os;

CREATE TABLE OS
   (    OS_ID   NUMBER,
        OS_DSCR VARCHAR2(50),
        OS_VNDR VARCHAR2(100)
   )
;

drop table prj;

CREATE TABLE PRJ
   (    PRJ_ID             VARCHAR2(4),
        PRJ_BUS_FCN        VARCHAR2(4),
        PRJ_STS            VARCHAR2(15),
        PRJ_DBA            VARCHAR2(4),
        PRJ_CMT            VARCHAR2(4000),
        PRJ_CMTS           VARCHAR2(4000),
        PRJ_STG            VARCHAR2(10),
        PRJ_PRTY           VARCHAR2(10),
        PRJ_DSCN           VARCHAR2(255),
        PRJ_NM             VARCHAR2(100),
        PRJ_LOB            VARCHAR2(20),
        PRJ_TEAM           VARCHAR2(10),
        PRJ_TP             VARCHAR2(10),
        PRJ_LEAD           VARCHAR2(10),
        PRJ_RDBMS          VARCHAR2(10),
        PRJ_FTE_ALLCN      VARCHAR2(10),
        PRJ_STRT_DT        DATE,
        PRJ_CMPLT_DT       DATE,
        PRJ_PRJTD_CMPLT_DT DATE
   );

drop table prj_mnt_tm;

CREATE TABLE PRJ_MNT_TM
   (    PRJ_MNT_ID       VARCHAR2(4),
        PRJ_MNT_DT       DATE,
        PRJ_MNT_HOSTINST VARCHAR2(3),
        PRJ_MNT_TFROM    TIMESTAMP (6),
        PRJ_MNT_TUNTIL   TIMESTAMP (6)
   )
;

drop table equipment;

create table equipment
   ( eqp_id      varchar2(10),
     eqp_dscr    varchar2(4),
     eqp_nm      varchar2(25),
     eqp_tp      varchar2(4),
     eqp_sn      varchar2(50),
     eqp_lcn_id  varchar2(50)
   ) ;

drop table schema;

CREATE TABLE SCHEMA
   (    SCHM_ID     VARCHAR2(4),
        SCHM_DSCR   VARCHAR2(200),
        SCHM_PWD    VARCHAR2(40),
        SCHM_VSN    VARCHAR2(10),
        SCHM_APP    NUMBER,
        SCHM_CMP    VARCHAR2(4),
        SCHM_USER   VARCHAR2(50),
        SCHM_CRT_DT DATE,
        SCHM_MDF_DT DATE
   ) ;

drop table apl;

CREATE TABLE apl
   (    apl_ID               VARCHAR2(4),
        apl_dscr             VARCHAR2(200),
        apl_nm               VARCHAR2(40),
        apl_cmp_id           VARCHAR2(10),
        apl_inf_tier         NUMBER,
        apl_rto              VARCHAR2(4),
        apl_rpo              VARCHAR2(4),
        apl_abbr             VARCHAR2(50),
        apl_tier             VARCHAR2(20),
        apl_cat_tp           VARCHAR2(4),
        apl_ait              VARCHAR2(15),
        apl_fnct_intgn_team  VARCHAR2(50)
   ) ;

drop SEQUENCE CST_AWR_CST_ID;
CREATE SEQUENCE CST_AWR_CST_ID
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop SEQUENCE CST_ID ;
CREATE SEQUENCE   "CST_ID"
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 10001 NOCACHE  NOORDER  NOCYCLE
;

drop SEQUENCE DB_ID;
CREATE SEQUENCE   "DB_ID"
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 22 NOCACHE  NOORDER  NOCYCLE
;

drop SEQUENCE HI_ID;
CREATE SEQUENCE   "HI_ID"
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop SEQUENCE MCH_ID;
CREATE SEQUENCE MCH_ID
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop SEQUENCE MC_ID;
CREATE SEQUENCE   "MC_ID"
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop SEQUENCE OS_ID;
CREATE SEQUENCE   "OS_ID"
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop SEQUENCE PRJ_MNT_ID;
CREATE SEQUENCE   "PRJ_MNT_ID"
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop SEQUENCE PRJ_ID;
CREATE SEQUENCE   "PRJ_ID"
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop sequence schm_id;
CREATE SEQUENCE SCHM_ID
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE;

drop sequence cls_id;

CREATE SEQUENCE CLS_ID
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop sequence eqp_id;

CREATE SEQUENCE eqp_ID
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;

drop sequence apl_id;

CREATE SEQUENCE apl_ID
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
;


drop table apl;

CREATE TABLE apl
   (    apl_ID               VARCHAR2(4),
        apl_DSCR             VARCHAR2(200),
        apl_abbr             VARCHAR2(50),
        apl_nm               VARCHAR2(40),
        apl_cmp_id           VARCHAR2(50),
        apl_inf_tier         VARCHAR2(20),
        apl_tier             VARCHAR2(20),
        apl_rto              VARCHAR2(4),
        apl_rpo              VARCHAR2(4),
        apl_cat_tp           VARCHAR2(4),
        apl_ait              VARCHAR2(15),
        apl_fnct_intgn_team  VARCHAR2(50)
   ) ;


EOF

Leave a Reply

Your email address will not be published. Required fields are marked *