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 instruction | Test Results | Pass/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 |