#!/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