User Tools

Site Tools


Custom Fields and Oracle Info

General Oracle stuff


Here’s the path to the Oracle backups:



Backup documentation can be found here: Oracle Backup and Restore

Create a datapump:

c:\oracle\scripts Export.bat PSPRODDB filename.dmp

Importing datapumps:

> sqlplus / as sysdba
SYS@PSPRODDB AS SYSDBA> shutdown immediate

c:\oracle\scripts DropSchema.bat PSPRODDB
c:\oracle\scripts Import.bat PSPRODDB [dumpfilename] Y

Note: 'Y' in the import will clear settings, useful if copying data over to the test server.

Note: The sqlplus steps insure that all users/connections are not in use. Seems to be necessary most times, so worth doing every time.

Useful SQL

For all active students:

SELECT * FROM students WHERE enroll_status = 0

Custom Fields

Custom field API:



Unique id of the record, normally the ID column, except for the following:

  • Schools: p_id = schools.school_number
  • StoredGrades: p_id = storedgrades.dcid
  • SPEnrollments: p_id = spenrollments.dcid
  • Virtualtablesdata: p_id = unique_id
  • Virtualtablesdata2: p_id = unique_id
  • Virtualtablesdata3: p_id = unique_id

Basic custom field information is in the fieldstable table. Key fields in this table are:

  • FieldNo: this is the key used in the values table
  • FileNo: indicates which table the custom field is attached to
  • Name: field name

These values are linked to the customtext and customvarchars tables via the KeyNo field which links to students.dcid.

Field Types from PowerSchool

To look up field names and types from PowerSchool itself:

techprivate/powerschool/customfieldsoracle.txt · Last modified: 2017/07/25 08:16 by afrink