This is the code for the select on the region for instances
Region select code select * from hc_instance where :P612_HC_DB_ID = db_id
This is the code for the Page Process for the insert, here we are inserting a record, then updating the value for the next time it refreshes the record.
/* Inserts / Updates a machine row */ declare v_date date := sysdate; e_date date := to_date('01-JAN-4000','DD-MON-YYYY'); begin insert into instance columns ( in_id, in_nmb, in_name, mch_id, db_id, in_mem_sga_tgt, in_mem_sga_max, in_mem_tgt, in_mem_max, in_mem_pga, in_cpu ) values ( in_id.NEXTVAL, :P612_DB_INST_NBR, :P612_DB_INST_NM, :P612_DB_MCH, :P612_DB_ID, :P612_DB_MEM_SGA_TGT, :P612_DB_MEM_SGA_MAX, :P612_DB_MEM_TGT, :P612_DB_MEM_MAX, :P612_DB_MEM_PGA, :P612_DB_CPU ); update database set db_mch_id = ' ' where db_id = :P612_DB_ID; end;
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
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;
#!/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;
#!/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