How to Shrink ProjectSecurityTable in Primavera P6

The ADMUSER.PROJECTSECURITY Table is one of the costly table in the Primavera P6 . Whenever The ProjectSecurity runs inside the P6 ( See Above) , the PROJECTSECURITY table size get increases. This causes increase in Wasted Space in the table-space . Soon this will cause performance issue in P6 . Also, the Publish Security may got struck in RUNNING state all the time. 

By default, The Oracle P6 does not clear clear the wasted space on the tables. Its our or DBA responsbility to clear the wasted space. Otehrwise we hit the DB space issue. The solution is to re-org the costly table ( large records ) in the ADMUSER schema. In this section, I explained the how to clean the PROJECTSECURITY table in Oracle database by using the Shrink command.  The steps are same for other costly tables, like PAUDIT, RESOUCELIMIT etc. 

Here below  I have provided the two ways to shrink the table: Apply the option  2 if Option 1 does not worl 

Option 1:  Simply re-org the table if the table size is less 

• Turn Off the ProjectSecurity in Global Scheduled Services 

• Run the following queries against ADMUSER schema to re-org the table 

EXECUTE IMMEDIATE 'alter table ADMUSER.PROJECTSECURITY enable row movement';
EXECUTE IMMEDIATE 'alter table ADMUSER.PROJECTSECURITY shrink space';
EXECUTE IMMEDIATE 'alter table ADMUSER.PROJECTSECURITY disable row movement';

Option 2:  Re-Org the table if you have large table records 

• Use this option only if the Option 1 does not work . 

• This method is for when the Project Security table has large table size . We are going to truncate the table so my recommendation is Backup your ADMUSER scheme. . Following are the steps we are going to do 
  • Turn Off the  Project Security in Global Scheduled Services 
  • Create a Temp table 
  • Export the ADMUSER.ProjectSecurityTable data into the TEMP table 
  • Truncate the ADMUSER.PROJECTSECURITY table 
  • Re-Org the ADMUSER.PROJECTSECURITY table 
  • Import the data back from the TEMP table to the ADMUSER.PROJECTSECURITY table 
  • Turn on the Project Security 

Note: Apply all the steps under ADMUSER schema

2.1)  Login to P6, Click on the Administrator on the top right, click on the Global Scheduled Services and Turn Off the Publish Security . If it is Running than wait for it to complete and then Turn Off if   

2.2) Create a Temp Table  from Project Security table. This will copy all the data from ProjectSecurity to Temp table 

create table momentum_gateway.projectsecurity_temp as 
(select * from  admuser.projectsecurity)

2.3) Truncate the ProjectSecurity table 

truncate table admuser.projectsecurity

2.3) Re-org the ProjectSecurity table . This will clear the wasted space 

EXECUTE IMMEDIATE 'alter table ADMUSER.PROJECTSECURITY enable row movement';
EXECUTE IMMEDIATE 'alter table ADMUSER.PROJECTSECURITY shrink space';
EXECUTE IMMEDIATE 'alter table ADMUSER.PROJECTSECURITY disable row movement';

2.4) Insert the data into ProjectSecurity from TEMP table 

insert into admuser.projectsecurity (RDBMSUSERID, PROJECTOBJECTID, COSTFLAG, UPDATE_DATE) 
select RDBMSUSERID, PROJECTOBJECTID, COSTFLAG, UPDATE_DATE from momentum_gateway.projectsecurity_temp;

Commit;

2.5) Drop the TEMP table 

drop table momentum_gateway.projectsecurity_temp

2.6: Go to P6 and Toggle On the Publish Security again & check the wasted space