Skip to content

Instantly share code, notes, and snippets.

johnboychan

Block or report user

Report or block johnboychan

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View oracle_users.sql
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
b.machine box,
substr(b.username,1,10) username,
b.status,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
View expenses_01.sql
select ec.claim_id, ec.status, ec.pdf_exported, ecl.*, eclf.*
from expenses_claim ec,
expenses_claim_lines ecl,
expenses_claim_line_files eclf
where 1 = 1
and ec.claim_id = ecl.claim_id
and ecl.line_id = eclf.line_id
and ec.claim_id = 111298
order by ecl.line_ref
View sql server table size
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
View oracle send email
DECLARE
v_mail_conn utl_smtp.connection;
v_boundary varchar2(50) := '----=*#abc1234321cba#*=';
v_step pls_integer := 24573;
v_env varchar2(5);
v_mail_server varchar2(64) := 'bulkmailrelay.ucs.ed.ac.uk';
v_from varchar2(100) := 'john.chan@ed.ac.uk';
BEGIN
View GIT.txt
https://www.wiki.ed.ac.uk/display/insite/Git+Task+3+-+Work+on+a+new+support+task
has002-131
project releases
release-1.0.1
feature-has002-131
@johnboychan
johnboychan / Oracle HR - P1703-008
Last active Feb 26, 2019
[P1703-008] HRSS01 HR not informing IDM to end some Staff affiliations every month
View Oracle HR - P1703-008
-- new epp4220 code put live on August 09 2018
-- Adam removed ended accounts on August 20th
select 'Assignment Leaver Start Date: ' || trunc(effective_start_date) || ', Assignment Last Updated: ' || last_update_date, person_id, assignment_id, effective_start_date, effective_end_date, assignment_status_type_id, payroll_id, assignment_number, ass_attribute15, last_update_date
from per_all_assignments_f where person_id = (select distinct person_id from per_all_people_f where employee_number IN ('160286') )
order by assignment_id, effective_start_date asc
View expenses_performace_cfadmin
export COLUMNS=2048
top -Hcb-p 54018
@johnboychan
johnboychan / Org Hierarchy
Last active Sep 22, 2018
Org Hierarchy #ebs
View Org Hierarchy
select haou.date_from,
haou.attribute1 || ' ' || haou.name,
haou.organization_id,
uoepp.uoe_functions.uoe_get_college_org_code(haou.organization_id) L3,
uoepp.uoe_functions.uoe_get_school_org_code(haou.organization_id) L4,
(select listagg(organization_id, ',') within group (order by haou1.organization_id )from hr_all_organization_units haou1
where haou1.attribute1 = haou.attribute1)
from hr_all_organization_units haou
where date_from = '01-AUG-2018 00.00.00'
order by uoepp.uoe_functions.uoe_get_college_org_code(haou.organization_id), uoepp.uoe_functions.uoe_get_school_org_code(haou.organization_id);
View MySQL Misc
-- auto increment values
SELECT table_name, `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'hshp';
View ppipmi
-- run in uoepp
select ut.table_name, utc.*
from user_tables ut, user_tab_columns utc
where ut.table_name = utc.table_name
--and ut.table_name like 'PPIPMI%'
and utc.column_name IN ('ASSIGN_ID', 'ASSIGNMENT_ID', 'PERSON_ID');
You can’t perform that action at this time.