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