Your Guide to Understanding Primavera P6 Database Schema

Understanding Primavera P6 Database Schema: A Comprehensive Guide

Primavera P6 by Oracle is one of the most widely used project management tools in industries like construction, oil & gas, infrastructure, and manufacturing. While most users interact with P6 through its graphical user interface, a deeper understanding of the Primavera P6 database schema is essential for power users, IT teams, and integration specialists.

In this blog, we’ll explore what the P6 database schema is, why it’s important, and how you can use it to enhance reporting, automation, and integration efforts.

What is the Primavera P6 Database Schema?

Primavera P6 stores all its data in a relational database—typically Oracle or Microsoft SQL Server. The database schema refers to the structure of this database: how tables are defined, how they relate to each other, and what kind of data they hold.

When you create or update a project in the P6 interface, these changes are reflected in the underlying database tables. Understanding this schema allows users to:

Generate custom reports and dashboards.
Automate repetitive tasks.
Integrate Primavera with other enterprise tools (e.g., SAP, Power BI).
Troubleshoot and audit project data.

Key Components of the P6 Schema

The Primavera P6 database contains hundreds of tables, but some are more frequently used than others. Here are a few critical ones:

Table Name	Description
`PROJECT`	Stores high-level project metadata.
`TASK`	Contains individual activity (task) details.
`RSRC`	Contains resource definitions.
`TASKRSRC`	Links tasks and resources (assignments).
`UDFTYPE`	Holds definitions of user-defined fields (UDFs).
`UDFVALUE`	Stores the values of those UDFs.
`CALENDAR`	Project and resource calendars.
`WBS`	Work Breakdown Structure details.
`ACTVTYPE`, `ACTVCODE`	Activity codes and their types.

These tables are linked through primary keys and foreign keys, often using columns like proj_id, task_id, wbs_id, and rsrc_id.

Primavera P6 Database Schema Documentation

Below is the detailed schema documentation for the Primavera P6 database.

Project Management Tables
Resource Management Tables
Calendar and Time Management Tables
Organizational Structure Tables
User Management Tables
Configuration and System Tables
Deletion and Reference Tables

Project Management Tables

PROJECT

Stores main project information including project name, dates, and status.

Column	Type	Description
PROJ_ID	INTEGER	Primary key for project
EPS_ID	INTEGER	Enterprise Project Structure ID
PROJ_NAME	TEXT	Project name
PARENT_PROJ_ID	INTEGER	Parent project ID for hierarchical projects
PROJ_SHORT_NAME	TEXT	Short name for the project
GUID	TEXT	Globally unique identifier
OBS_ID	INTEGER	Organizational Breakdown Structure ID
CURRENT_PROJ_STATUS	TEXT	Current status of the project
PLANNED_START_DATE	DATETIME	Planned start date
PLANNED_END_DATE	DATETIME	Planned end date
ACT_START_DATE	DATETIME	Actual start date
ACT_END_DATE	DATETIME	Actual end date

PROJWBS

Project Work Breakdown Structure - hierarchical organization of project elements.

Column	Type	Description
WBS_ID	INTEGER	Primary key for WBS element
PROJ_ID	INTEGER	Project ID this WBS belongs to
PARENT_WBS_ID	INTEGER	Parent WBS ID for hierarchical structure
SEQ_NUM	INTEGER	Sequence number for ordering
WBS_NAME	TEXT	Name of the WBS element
WBS_SHORT_NAME	TEXT	Short name for the WBS element
STATUS_CODE	TEXT	Status code
WBS_LEVEL	INTEGER	Level in the WBS hierarchy

TASK

Individual activities or tasks within projects.

Column	Type	Description
TASK_ID	INTEGER	Primary key for task
PROJ_ID	INTEGER	Project ID this task belongs to
WBS_ID	INTEGER	WBS ID this task belongs to
TASK_NAME	TEXT	Name of the task
TASK_TYPE	TEXT	Type of task
DURATION_TYPE	TEXT	Duration type (Fixed Units, Fixed Duration, etc.)
PHYS_COMPLETE_PCT	REAL	Physical completion percentage
ACT_START_DATE	DATETIME	Actual start date
ACT_END_DATE	DATETIME	Actual end date
LATE_START_DATE	DATETIME	Late start date
LATE_END_DATE	DATETIME	Late end date
EARLY_START_DATE	DATETIME	Early start date
EARLY_END_DATE	DATETIME	Early end date
TARGET_START_DATE	DATETIME	Target start date
TARGET_END_DATE	DATETIME	Target end date
REM_LATE_START_DATE	DATETIME	Remaining late start date
REM_LATE_END_DATE	DATETIME	Remaining late end date
TOTAL_FLOAT_HR_CNT	REAL	Total float in hours
FREE_FLOAT_HR_CNT	REAL	Free float in hours

TASKPRED

Task predecessors - defines relationships between tasks.

Column	Type	Description
TASK_PRED_ID	INTEGER	Primary key for task predecessor relationship
TASK_ID	INTEGER	Task ID
PRED_TASK_ID	INTEGER	Predecessor task ID
PROJ_ID	INTEGER	Project ID
PRED_PROJ_ID	INTEGER	Predecessor project ID
PRED_TYPE	TEXT	Predecessor type (Finish-Start, Start-Start, etc.)
LAG_HR_CNT	REAL	Lag time in hours

TASKRSRC

Task resources - links tasks to resources assigned to them.

Column	Type	Description
TASKRSRC_ID	INTEGER	Primary key for task resource assignment
TASK_ID	INTEGER	Task ID
RSRC_ID	INTEGER	Resource ID
PROJ_ID	INTEGER	Project ID
REMAIN_QTY	REAL	Remaining quantity
TARGET_QTY	REAL	Target quantity
REMAIN_COST	REAL	Remaining cost
TARGET_COST	REAL	Target cost
ACT_QTY	REAL	Actual quantity
ACT_COST	REAL	Actual cost

TASKUSER

Links tasks to users.

Column	Type	Description
TASK_ID	INTEGER	Task ID (Primary key)
USER_ID	INTEGER	User ID (Primary key)
PROJ_ID	INTEGER	Project ID

Resource Management Tables

RSRC

Resources (people, equipment, materials).

Column	Type	Description
RSRC_ID	INTEGER	Primary key for resource
PARENT_RSRC_ID	INTEGER	Parent resource ID for hierarchical resources
RSRC_NAME	TEXT	Resource name
RSRC_SHORT_NAME	TEXT	Short name for the resource
RSRC_TITLE_NAME	TEXT	Resource title
EMAIL_ADDR	TEXT	Email address
OFFICE_PHONE	TEXT	Office phone number
RSRC_TYPE	TEXT	Resource type (Labor, Material, etc.)
AUTO_COMPUTE_ACT_FLAG	TEXT	Auto compute actuals flag
OBS_ID	INTEGER	Organizational Breakdown Structure ID

RSRCROLE

Resource roles.

Column	Type	Description
RSRC_ID	INTEGER	Resource ID (Primary key)
ROLE_ID	INTEGER	Role ID (Primary key)
PROF_ID	INTEGER	Proficiency ID
EFFECTIVE_DATE	DATETIME	Effective date of the role assignment

RSRCRATE

Resource rates (costs).

Column	Type	Description
RSRC_RATE_ID	INTEGER	Primary key for resource rate
RSRC_ID	INTEGER	Resource ID
COST_PER_QTY	REAL	Cost per quantity
COST_PER_QTY2	REAL	Secondary cost per quantity
COST_PER_QTY3	REAL	Tertiary cost per quantity
COST_PER_QTY4	REAL	Quaternary cost per quantity
COST_PER_QTY5	REAL	Quinary cost per quantity
EFFECTIVE_DATE	DATETIME	Effective date of the rate

Calendar and Time Management Tables

CALENDAR

Calendar definitions.

Column	Type	Description
CLNDR_ID	INTEGER	Primary key for calendar
CLNDR_NAME	TEXT	Calendar name
CLNDR_TYPE	TEXT	Calendar type
DAY_HR_CNT	REAL	Hours per day
WEEK_HR_CNT	REAL	Hours per week
MONTH_HR_CNT	REAL	Hours per month
YEAR_HR_CNT	REAL	Hours per year
BASE_CLNDR_ID	INTEGER	Base calendar ID

CALENDARS

Calendar assignments.

Column	Type	Description
CLNDR_ID	INTEGER	Calendar ID
OBS_ID	INTEGER	OBS ID
PROJ_ID	INTEGER	Project ID
RSRC_ID	INTEGER	Resource ID

CALENDARDATA

Calendar detailed data.

Column	Type	Description
CLNDR_DATA_ID	INTEGER	Primary key for calendar data
CLNDR_ID	INTEGER	Calendar ID
SEQ_NUM	INTEGER	Sequence number
DAY_OF_WEEK	INTEGER	Day of week (1-7)
START_DATE	DATETIME	Start date
END_DATE	DATETIME	End date
START_TIME	INTEGER	Start time (minutes from midnight)
END_TIME	INTEGER	End time (minutes from midnight)
SHIFT_ID	INTEGER	Shift ID
WORK_FLAG	TEXT	Work flag (Y/N)

Organizational Structure Tables

OBS

Organizational Breakdown Structure.

Column	Type	Description
OBS_ID	INTEGER	Primary key for OBS element
PARENT_OBS_ID	INTEGER	Parent OBS ID for hierarchical structure
OBS_NAME	TEXT	OBS name
OBS_DESCR	BLOB	OBS description
SEQ_NUM	INTEGER	Sequence number

ACCOUNT

Account information.

Column	Type	Description
ACCT_ID	INTEGER	Primary key for account
PARENT_ACCT_ID	INTEGER	Parent account ID for hierarchical accounts
ACCT_SEQ_NUM	INTEGER	Account sequence number
ACCT_NAME	TEXT	Account name
ACCT_SHORT_NAME	TEXT	Short name for the account
ACCT_DESCR	BLOB	Account description

ROLES

User roles in the system.

Column	Type	Description
ROLE_ID	INTEGER	Primary key for role
PARENT_ROLE_ID	INTEGER	Parent role ID for hierarchical roles
SEQ_NUM	INTEGER	Sequence number
ROLE_NAME	TEXT	Role name
ROLE_SHORT_NAME	TEXT	Short name for the role
DEF_COST_QTY_LINK_FLAG	TEXT	Default cost quantity link flag
DEF_COST_PER_QTY	REAL	Default cost per quantity
DEF_MAX_QTY_PER_HR	REAL	Default maximum quantity per hour

User Management Tables

USERS

User accounts.

Column	Type	Description
USER_ID	INTEGER	Primary key for user
GLOBAL_FLAG	TEXT	Global flag
EMAIL_TYPE	TEXT	Email type
USER_NAME	TEXT	Username
PROF_ID	INTEGER	Proficiency ID
CURR_ID	INTEGER	Currency ID
GUID	TEXT	Globally unique identifier
EMAIL_SRV_USER_NAME	TEXT	Email server username
OFFICE_PHONE	TEXT	Office phone number
ACTUAL_NAME	TEXT	Actual name of the user
EMAIL_SEND_SERVER	TEXT	Email send server
EMAIL_ADDR	TEXT	Email address
EMAIL_SRV_PASSWD	TEXT	Email server password (encrypted)
PASSWD	TEXT	User password (encrypted)
ALL_RSRC_ACCESS_FLAG	TEXT	All resource access flag

USERDATA

User-specific data.

Column	Type	Description
USER_DATA_ID	INTEGER	Primary key for user data
USER_ID	INTEGER	User ID
TOPIC_NAME	TEXT	Topic name
USER_DATA	BLOB	User data

USERCOL

User-defined columns.

Column	Type	Description
USER_COL_ID	INTEGER	Primary key for user column
TABLE_NAME	TEXT	Table name
LOGICAL_DATA_TYPE	TEXT	Logical data type
USER_COL_NAME	TEXT	User column name
USER_COL_LABEL	TEXT	User column label

USERSET

User settings.

Column	Type	Description
NAMESPACE	TEXT	Namespace (Primary key)
SETTING_NAME	TEXT	Setting name (Primary key)
SETTING_VALUE	TEXT	Setting value
USER_ID	INTEGER	User ID (Primary key)
USERSET_BLOB	BLOB	User settings blob

USESSION

User sessions.

Column	Type	Description
SESSION_ID	INTEGER	Primary key for session
LOGIN_TIME	DATETIME	Login time
LAST_ACTIVE_TIME	DATETIME	Last active time
HOST_NAME	TEXT	Host name
USER_ID	INTEGER	User ID
PROCESS_NUM	INTEGER	Process number
APP_NAME	TEXT	Application name
HARD_DRIVE_CODE	TEXT	Hard drive code
DB_ENGINE_TYPE	TEXT	Database engine type
OS_USER_NAME	TEXT	Operating system username
VDB_INSTANCE_GUID	TEXT	Virtual database instance GUID
SPID	INTEGER	System process ID

Configuration and System Tables

ADMIN_CONFIG

Administrative configuration.

Column	Type	Description
CONFIG_NAME	TEXT	Configuration name (Primary key)
CONFIG_TYPE	TEXT	Configuration type
FACTORY_VERSION	TEXT	Factory version
LAST_CHANGE_DATE	DATETIME	Last change date
CONFIG_VALUE	TEXT	Configuration value
CONFIG_DATA	BLOB	Configuration data

UDFTYPE

User-defined field types.

Column	Type	Description
UDF_TYPE_ID	INTEGER	Primary key for UDF type
TABLE_NAME	TEXT	Table name
UDF_TYPE_NAME	TEXT	UDF type name
UDF_TYPE_LABEL	TEXT	UDF type label
LOGICAL_DATA_TYPE	TEXT	Logical data type
SUPER_FLAG	TEXT	Super flag
INDICATOR_EXPRESSION	TEXT	Indicator expression
SUMMARY_INDICATOR_EXPRESSION	TEXT	Summary indicator expression
FORMULA	TEXT	Formula

UDFVALUE

User-defined field values.

Column	Type	Description
UDF_TYPE_ID	INTEGER	UDF type ID (Primary key)
FK_ID	INTEGER	Foreign key ID (Primary key)
PROJ_ID	INTEGER	Project ID
UDF_DATE	DATETIME	UDF date value
UDF_NUMBER	REAL	UDF number value
UDF_TEXT	TEXT	UDF text value
UDF_CODE_ID	INTEGER	UDF code ID
TABLE_NAME	TEXT	Table name

VIEWPROP

View properties.

Column	Type	Description
VIEW_ID	INTEGER	Primary key for view
USER_ID	INTEGER	User ID
PROJ_ID	INTEGER	Project ID
VIEW_TYPE	TEXT	View type
VIEW_NAME	TEXT	View name
VIEW_DATA	BLOB	View data

Deletion and Reference Tables

REFRDEL

Reference deletion.

Column	Type	Description
DELETE_DATE	DATETIME	Delete date
TABLE_NAME	TEXT	Table name
PK1	TEXT	Primary key 1
PK2	TEXT	Primary key 2
PK3	TEXT	Primary key 3
PK4	TEXT	Primary key 4
PROJ_ID	INTEGER	Project ID

DLTACCT, DLTACTV, DLTOBS, DLTROLE, DLTRSRC, DLTRSRL, DLTUSER

Deletion tracking tables for various entities.

Column	Type	Description
SESSION_ID	INTEGER	Session ID
[ENTITY]_ID	INTEGER	Entity ID (e.g., ACCT_ID, ACTV_CODE_ID, etc.)

PKXREF

Primary key cross-reference.

Column	Type	Description
SYSTEM_ID	INTEGER	System ID
CONTEXT_NAME	TEXT	Context name
PK1	INTEGER	Primary key 1
PK2	INTEGER	Primary key 2
OLD_PK1	INTEGER	Old primary key 1
OLD_PK2	INTEGER	Old primary key 2
OLD_STR	TEXT	Old string
NEW_STR	TEXT	New string
ADD_DATE	DATETIME	Add date

Common Fields

Most tables in the database include these standard fields:

Column	Type	Description
CREATE_DATE	DATETIME	Creation date
CREATE_USER	TEXT	User who created the record
UPDATE_DATE	DATETIME	Last update date
UPDATE_USER	TEXT	User who last updated the record
DELETE_SESSION_ID	INTEGER	Session ID when record was deleted
DELETE_DATE	DATETIME	Date when record was deleted

These fields are used for auditing and tracking changes to records in the database.

Reporting and Querying the P6 Database

Example: Get All Activities for a Project

sql
SELECT 
    t.task_id, 
    t.task_name, 
    t.status_code, 
    w.wbs_name, 
    p.proj_short_name 
FROM 
    task t
JOIN 
    wbs w ON t.wbs_id = w.wbs_id
JOIN 
    project p ON t.proj_id = p.proj_id
WHERE 
    p.proj_short_name = 'DELHI_METRO_PHASE3';

This query retrieves all tasks for a project named "DELHI_METRO_PHASE3" along with their WBS names and status.

⚠️ Note: Querying directly on a live P6 production database should be done with read-only access and with proper IT approvals.

Benefits of Understanding the Schema

Custom Reporting: Build targeted reports using SQL or integrate with tools like Power BI or Tableau.
Data Validation: Cross-check user-entered data with standard metrics.
Integration: Sync P6 with ERP systems or other project tools using ETL pipelines.
Audit Trails: Identify who changed what and when—especially useful for claims and disputes.

Accessing the Schema Documentation

Oracle published a complete schema in a public-facing manual, which is available on the link link on Oracle Doc website. Additionally we can:

Use SQL Developer or SSMS to browse tables, columns, and relationships.
Install a data modeling tool (like ER/Studio or dbForge Studio) to generate ER diagrams.
Reference unofficial resources and community wikis that offer schema diagrams.

Tips for Working with the P6 Schema

Use aliases when writing complex joins.
Filter by delete_session_id IS NULL to exclude soft-deleted records.
Be cautious with UPDATE and DELETE operations—prefer to work on reporting replicas.
Keep a data dictionary handy. Create your own if needed.

Real-World Use Cases

Progress Tracking Dashboards: Pull live activity and resource status for executive dashboards.
Earned Value Analysis: Calculate CPI/SPI based on planned vs. actual cost and schedule data.
Time-Phased Forecasts: Extract time-distributed data for advanced forecasting.

Final Thoughts

While Primavera P6 offers powerful out-of-the-box capabilities, unlocking its full potential requires going under the hood. Familiarity with the P6 database schema is a superpower for schedulers, project controllers, and IT professionals alike.

Whether you're building a custom dashboard, integrating with enterprise systems, or validating data for a high-stakes claim—the database is your ally.