APEX | IT Remote

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
&nbsp;

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;

RESTful Data Services

APEX 5.1 Docs

APEX 5.1 Validations

APEX 5.x Install

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

sub-menus

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" &lt;&lt;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

ITRemote

Ready for Action?