Category Archives: APEX

APEX TODO List

Add filtering to category type by environment to exclude “decommissioned” databases. done
Add checklist to each database type and/or project to track what has been accomplished
Add standard build lists for SQL Server, Sybase, Informix new lists
Add link for build list for server, *nix, wintel
Add help to all items in the machine QA
Eliminate nulls from the DBA drop down list
bgcolor=”FF0000″
Enhancement table
Public Report site
Add myrock to the site list
Add filters for mgmt folks to be by tech type
Possibly add arch approval for machine and cluster types

DBAdmin Edit Screen

updaud = Update

On Submit – After Computations and Validations
Once Per Page Visit (default)

FOR i in 1..HTMLDB_APPLICATION.G_F01.COUNT
LOOP
   update database
     set db_aud_tp = :P610_aud_tp_chg
    where db_id = htmldb_application.g_f01(i);
END LOOP;

When Button Pressed (Process After Submit When this Button is Pressed)
*P610_AUD_UPDT

The “delete_db” routine under the

Page Processing

On Submit – After Computations and Validations
Once Per Page Visit (default)

FOR i in 1..HTMLDB_APPLICATION.G_F01.COUNT
LOOP
   delete from database 
    where db_id = htmldb_application.g_f01(i);
END LOOP;

 

 

 

Under “Processes”
“Before Header”
“PL/SQL anonymous block”

On Load – Before Header

This is the initialization procedure from a particular table, we are initializing the variables so we can make sure that there are no values already in the variable. We are also pre-calculating some items in here for some non database variables. Such as counts of databases by version, I’m simply doing a sql count for each of these to display on the screen someplace. Also I create a variable GPGFROM which I am setting to the page that it is on in order to go back to this page if I need to.


begin
:P612_DB_NAME:=' ';
:P612_DB_DESC:=' ';
:P612_DB_CMT:=' ';
:P612_QA_DT:=NULL;
/*   :P612_DB_INST_NM:=' '; */
:P612_DB_TYP:=' ';
:P612_DB_VER:=' ';
:P612_OS:=' ';
:P612_OS_VER:=' ';
:P612_DB_DBA_CRT:=' ';
:P612_DB_DBA_QA:=' ';
:P612_CRT_DT:=NULL;
:P612_DB_DBA_PRI:=' ';
:P612_DB_DBA_SEC:=' ';
:P612_CST_ID:=' ';
:P612_DBID:=' ';
/*   :P612_DB_MCH:=' '; */
:P612_DB_DSK_ALLOC:=0;
:P612_DB_DSK_USED:=0;
:P612_DB_BKP_FRQ:=' ';
:P612_DB_DT_UPG:=NULL;
:P612_DB_DT_NXT_UPG:=NULL;

select count(*) into :P610_NO_DBS from database;

select count(*) into :P610_NO_DBS_11G from database where db_ver ='11.2.0.1' or db_ver='11.2.0.2' or db_ver='11.2.0.3' or db_ver='11.2.0.2.0' or db_ver='11.2.0.3.0';

select count(*) into :P610_NO_DBS_10G from database where db_ver ='10.2.0.4' or db_ver='10.2.0.5' or db_ver='10.2.0.3' or db_ver='10.2.0.3.0' or db_ver='10.2.0.5.0' or db_ver='10.2.0.4.0';

select count(*) into :P610_10203 from database where db_ver='10.2.0.3' or db_ver='10.2.0.3.0';

select count(*) into :P610_10204 from database where db_ver='10.2.0.4' or db_ver='10.2.0.4.0';
select count(*) into :P610_10205 from database where db_ver='10.2.0.5' or db_ver='10.2.0.5.0';
select count(*) into :P610_11201 from database where db_ver='11.2.0.1' or db_ver='11.2.0.1.0';
select count(*) into :P610_11202 from database where db_ver='11.2.0.2' or db_ver='11.2.0.2.0';

select count(*) into :P610_11203 from database where db_ver='11.2.0.3' or db_ver='11.2.0.3.0';
select count(*) into :P610_11204 from database where db_ver='11.2.0.4' or db_ver='11.2.0.4.0';

select count(*) into :P610_NO_DB_PRD from database where db_cat_tp ='Production';
select count(*) into :P610_NO_DB_TT from database where db_cat_tp ='Test' or substr(db_cat_tp,1,4)='Load';
select count(*) into :P610_NO_DB_CIT from database where db_cat_tp ='CIT';
select count(*) into :P610_NO_DB_SIT from database where db_cat_tp ='SIT';
select count(*) into :P610_NO_DB_UAT from database where substr(db_cat_tp,1,4)='User';
select count(*) into :P610_NO_DB_DEV from database where substr(db_cat_tp,1,3)='Dev';

:GPGFROM := 610;

end;

Under “Page Processing”
“Processes”

FOR i in 1..HTMLDB_APPLICATION.G_F01.COUNT
LOOP
   delete from database 
    where db_id = htmldb_application.g_f01(i);
END LOOP;

Grants for APEX user

#!/bin/ksh
# This script assigns the user (CFGADM) 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
#
#######################################################################################################

sqlplus -s "/ as sysdba" <<EOF

grant exempt access policy to dba01;
grant sysdba to dba01;
grant select_catalog_role to dba01;
grant create session to dba01;
grant select on DBA_HIST_DATABASE_INSTANCE to dba01;
grant select on DBA_HIST_SNAPSHOT to dba01;
grant select on DBA_HIST_SNAP_ERROR to dba01;
grant select on DBA_HIST_BASELINE to dba01;
grant select on DBA_HIST_WR_CONTROL to dba01;
grant select on DBA_HIST_DATAFILE to dba01;
grant select on DBA_HIST_FILESTATXS to dba01;
grant select on DBA_HIST_TEMPFILE to dba01;
grant select on DBA_HIST_TEMPSTATXS to dba01;
grant select on DBA_HIST_SQLSTAT to dba01;
grant select on DBA_HIST_SQLTEXT to dba01;
grant select on DBA_HIST_SQL_SUMMARY to dba01;
grant select on DBA_HIST_SQL_PLAN to dba01;
grant select on DBA_HIST_SQLBIND to dba01;
grant select on DBA_HIST_OPTIMIZER_ENV to dba01;
grant select on DBA_HIST_EVENT_NAME to dba01;
grant select on DBA_HIST_SYSTEM_EVENT to dba01;
grant select on DBA_HIST_BG_EVENT_SUMMARY to dba01;
grant select on DBA_HIST_WAITSTAT to dba01;
grant select on DBA_HIST_ENQUEUE_STAT to dba01;
grant select on DBA_HIST_LATCH_NAME to dba01;
grant select on DBA_HIST_LATCH to dba01;
grant select on DBA_HIST_LATCH_CHILDREN to dba01;
grant select on DBA_HIST_LATCH_PARENT to dba01;
grant select on DBA_HIST_LATCH_MISSES_SUMMARY to dba01;
grant select on DBA_HIST_LIBRARYCACHE to dba01;
grant select on DBA_HIST_DB_CACHE_ADVICE to dba01;
grant select on DBA_HIST_BUFFER_POOL_STAT to dba01;
grant select on DBA_HIST_ROWCACHE_SUMMARY to dba01;
grant select on DBA_HIST_SGA to dba01;
grant select on DBA_HIST_SGASTAT to dba01;
grant select on DBA_HIST_PGASTAT to dba01;
grant select on DBA_HIST_RESOURCE_LIMIT to dba01;
grant select on DBA_HIST_SHARED_POOL_ADVICE to dba01;
grant select on DBA_HIST_SQL_WORKAREA_HSTGRM to dba01;
grant select on DBA_HIST_PGA_TARGET_ADVICE to dba01;
grant select on DBA_HIST_INSTANCE_RECOVERY to dba01;
grant select on DBA_HIST_JAVA_POOL_ADVICE to dba01;
grant select on DBA_HIST_THREAD to dba01;
grant select on DBA_HIST_STAT_NAME to dba01;
grant select on DBA_HIST_SYSSTAT to dba01;
grant select on DBA_HIST_SYS_TIME_MODEL to dba01;
grant select on DBA_HIST_OSSTAT_NAME to dba01;
grant select on DBA_HIST_OSSTAT to dba01;
grant select on DBA_HIST_PARAMETER_NAME to dba01;
grant select on DBA_HIST_PARAMETER to dba01;
grant select on DBA_HIST_WAITCLASSMET_HISTORY to dba01;
grant select on DBA_HIST_DLM_MISC to dba01;
grant select on DBA_HIST_CR_BLOCK_SERVER to dba01;
grant select on DBA_HIST_CURRENT_BLOCK_SERVER to dba01;
grant select on DBA_HIST_ACTIVE_SESS_HISTORY to dba01;
grant select on DBA_HIST_TABLESPACE_STAT to dba01;
grant select on DBA_HIST_LOG to dba01;
grant select on DBA_HIST_MTTR_TARGET_ADVICE to dba01;
grant select on DBA_HIST_TBSPC_SPACE_USAGE to dba01;
grant select on DBA_HIST_SERVICE_NAME to dba01;
grant select on DBA_HIST_SERVICE_STAT to dba01;
grant select on DBA_HIST_SERVICE_WAIT_CLASS to dba01;
grant select on DBA_HIST_SNAPSHOT to dba01;
grant select on DBA_SEGMENTS to dba01;
grant select on DBA_OUTSTANDING_ALERTS to dba01;
grant select on DICTIONARY to dba01;
grant select on DBA_REGISTRY to dba01;
grant select on DBA_HIST_COMP_IOSTAT to dba01;
grant select on DBA_HIST_UNDOSTAT to dba01;

grant select on DBA_VIEWS to dba01;
grant select on DBA_DB_LINKS to dba01;

grant select on DBA_TABLES to dba01;

grant select on v_\$session to dba01;
grant select on v_\$sqlarea to dba01;
grant select on v_\$sql_plan to dba01;
grant select on v_\$database to dba01;
grant select on v_\$instance to dba01;
grant select on v_\$datafile_copy to dba01;
grant select on v_\$parameter to dba01;
grant select on v_\$spparameter to dba01;
grant select on v_\$asm_disk to dba01;
grant select on v_\$asm_diskgroup to dba01;
grant select on v_\$system_fix_control to dba01;
grant select on gv_\$parameter to dba01;
grant select on gv_\$event_histogram to dba01;
grant select on sysman.BHV_SERVER_DETAILS to dba01;
grant select on sysman.MGMT_TARGETS to dba01;
grant select on sysman.MGMT_TABLE_SIZES to dba01;
grant select on sysman.MGMT_TARGET_CREDENTIALS to dba01;

cr_dblink_gateway.ksh

#!/bin/ksh

sqlplus -s "/ as sysdba" <<EOF

-- create a public link for all to use

-- drop public database link sybase;


-- CREATE PUBLIC DATABASE LINK sybase CONNECT TO "automation" IDENTIFIED BY "password" USING 'db4sybase';

drop public database link testora;

CREATE PUBLIC DATABASE LINK testora CONNECT TO "automation" IDENTIFIED BY "password" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.commitdba.com)(PORT=49129))(CONNECT_DATA=(SID=dg4sybase))(HS=OK))';

-- select host from dba_db_links;

select * from dual@TESTORA;
-- select * from dual@db4sybase;

-- select * from dual@sybase;

EOF


bounce_apex.ksh

#!/bin/ksh
############################################################################
# Script Name..:
# Description..: This script will reset the APEX port in case of hang
# Author.......: Michael Culp
# Date.........: 10/11/2009
# Version......: 2.1
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################

sqlplus -s "/ as sysdba" <<EOF

exec dbms_xdb.sethttpport(0);
exec dbms_xdb.sethttpport(8081);

EOF

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

restart_wl.ksh


# Script Name..: restart_weblogic.ksh
# Description..: Checks if weblogic process is running, if no start it
# Author.......: Susheel Tamrakar
# Date.........: April 2016
# Version......: v.01
# Modified By..: Susheel T.
# Date Modified:
# Comments.....:

############################################################################
#set -xv
############################################################################
# Apex server -  lkcme179.kcm-p01.chp.bankofamerica.com
# lkcme179.kcm-p01.chp.bankofamerica.com = New APEX server (version 5 using weblogic 12c)
# Oracle user -         DBADMD01
# important files
# /var/opt/oracle/wls.env - contains environment variables for wls home and sample syntax
# /var/opt/oracle/start_weblogic.sh - starts weblogic server
# /var/opt/oracle/stop_weblogic.sh - stops weblogic server
# /var/opt/oracle/restart_weblogic.sh - restarts weblogic server
############################################################################
echo "##################################################################"
echo "# Welogic server process check                                    "
echo "##################################################################"
# check if weblogic process is running
if [ "$(ps -ef|grep weblogic| grep -v 'grep' |grep -v 'restart'| wc -l)" -gt 0 ] ; then
echo "Weblogic application server process is running"
echo " "
echo " "
echo " "

else
echo "Weblogic application server process is NOT running.....STARTING now and sleeping for 1 minute"
echo " "
echo " "
echo " "

/var/opt/oracle/start_weblogic.sh
sleep 1m
echo "Weblogic application server started, check process below..."
echo " "
echo " "
echo " "

echo "$(ps -ef|grep weblogic| grep -v 'grep'|grep -v 'restart')"
fi