Development Technology Administrator Testing

 

 

The changes described in https://www.projects.ed.ac.uk/project/hrs071/system_design-2 have been checked for compliance with the DBA standards in https://www.wiki.ed.ac.uk/display/insite/DBA+Database+Review+Checklist.  The results are shown below.

 

 SDS instructionTest ResultsPass/Fail
1

Create a new table inside the UOEPP schema called UOE_HESA_RESEARCH_INFO_TMP

There are 2 new tables UOE_HESA_RESEARCH_INFO_TMP1 and UOE_HESA_RESEARCH_INFO_TMP2.

Columns RESAST and ECRSTAT are split between the 2 tables.

No grants on either of them.  Shouldn’t they at least be granted to UOEPP_BROWSER?

No public synonyms on either.

Both primary key indexes have been created in a data tablespace.  They should be moved to UOEPP_INDEX, and the object creation script should be amended.

 

Developer notes: These tables have been replaced with UOE_HESA_ECRSTAT_TMP and UOE_HESA_RESAST_TMP. Grants and synonyms added as requested.  Indexes are now located in the correct tablespace and all scripts amended.

DBA notes: Rechecked, all OK.

Pass
2

Create a new table inside the UOEPP schema called UOE_HESA_LEVELS_TMP.

The primary key index has been created in a data tablespace.  It should be moved to UOEPP_INDEX, and the object creation script should be amended.

No grants.

No public synonym.

One of the columns is called HESA_LEVELS rather than just LEVELS (and in various tables listed below to which an equivalent to this column has been added).

 

Developer notes: Grants and synonyms added as requested.  Indexes are now located in the correct tablespace and all scripts amended.  The HESA_LEVELS column name is correct - SDS updated to reflect this.

 

DBA notes: Rechecked, all OK.

 

Pass
3

Create a new table inside the UOEPP schema called UOE_HESA_CURACCDIS_TMP

The primary key index has been created in a data tablespace.  It should be moved to UOEPP_INDEX, and the object creation script should be amended.

No grants.

No public synonym.

Column CURACCDIS has been created as varchar2(4) rather than varchar2(2).

 

Developer notes: Grants and synonyms added as requested.  Indexes are now located in the correct tablespace and all scripts amended.  The column length of 4 is correct SDS updated to reflect.

 

DBA notes: Rechecked, all OK.

 

Pass
4

Addition of new columns to HESA extract tables

UOE_HESA_ASSIGNMENTS_SUMMARY has had 3 columns added ACTSOC1, ACTSOC2 and ACTSOC3, rather than just ACTSOC.

As has UOE_HESA_ASSIGNMENTS_RAW.

UOE_HESA_PERSON_DETAILS.LOCLEAVE has been created as varchar2(2) rather than varchar2(1).

UOE_HESA_CONTRACT_DETAILS has had an extra column added PERIOD_OF_SERVICE_ID which isn’t mentioned in the SDS.

 

Developer notes: The changes noted here are all correct. SDS updated to reflect where necessary.

 

DBA notes: SDS rechecked, some changes concerning actsoc columns have not been made.

 Developer notes: these changes have been applied to the SDS.

Pass
5

Increase size of CCENTRE1, CCENTRE2, CCENTRE3 column

These columns don’t exist on UOE_HESA_PERSON_DETAILS and UOE_HESA_PERSON_DETAILS_ARCHIVE.

Developer notes: Ths is correct, SDS updated to reflect.

 

DBA notes: Rechecked, all OK.

 

Pass
6

Removal of redundant columns from HESA extract tables

All OK.

Pass
7

Drop obsolete extract tables

UOE_HESA_ADDITIONAL_POSITIONS dropped.

UOE_HESA_GRADE_DETAILS dropped, but there are 3 synonyms still pointing to it.

 

Developer notes:  The synonyms have been removed and scripts updated appropriately.

 

DBA notes: Rechecked, all OK.

 

Pass
8

APEX

On HRDEV:

New user APEXHRLINK created.

New role APEX_HR_BROWSER created.

SELECT, INSERT and UPDATE on UOE_HESA_PERSON_DETAILS and UOE_HESA_CONTRACT_DETAILS, and SELECT on UOE_HESA_PERSON_DETS_ARCHIVE, UOE_HESA_CONTRACT_DETS_ARCHIVE and UOE_HESA_GRADE_DETS_ARCHIVE have been granted to APEXHRLINK via the role APEX_HR_BROWSER.

 

On APPSDEV:

New user APEXHR created.

Private database link APEXHRHRLINK has been created for this user to the relevant HR environment.  No other new objects created for this user.

A new administrator APEXHRDEV has been created in the existing HR workspace.

Pass

 

Exceptions from DBA Testing

05-Apr-2013:  As detailed in items 1, 2, 3, 4, 5 and 7 above.

10-Apr-2013:  Retested.  Items 1, 2, 3, 5 adn 7 now Pass.  Item 4 remains Failed.

10-Apr-2013: Rechecked.  Item 4 now passed.

 

Link to completed DBA test review document

 

StatePassor Fail

Pass

 Gordon McKenna, 10-Apr-13