Several people responded kindly to my post regarding how to Put Your Primavera XER File on a Diet. Mostly I focused on how to shrink the size on an XER file after it has been exported from a database, Microsoft (SQL) or Oracle (XE). I also explained – briefly – how to delete POBS data in an Oracle database so that it is not exported in the first place. Both solutions are important. For example, if someone sends you a bloated XER file you want to delete the worthless POBS data before you import it into your database. Otherwise, your database becomes bloated. Also, it significantly reduces the amount of time required to import XER files. We have heard from users saying it takes hours to import larger schedules. That is ridiculous! It should never take more than a few minutes to import one schedule.
However, I realize that some users need more specific instructions regarding how to delete POBS data from an Oracle XE database. So today I am going to describe each step in detail:
Step One – Locate the Oracle XE Database Home Page
Unless you are using Windows 8 or later, simply click on Start > All Programs. Oracle XE is a program, after all. Starting with Windows 8, all programs are treated like apps so I have to look in my apps group. Ugh! But here is what you are looking for in Windows 8:
Select Got to Database Home Page. Note that all of the Oracle/Primavera apps are grouped together. Double-click to launch the home page. This might take fifteen seconds or so.
Step 2 – Log Into the Database Home Page
Once the Database Home Page loads you will be presented with the following login screen:
You must use ADMPRM$PM as the username. Elsewhere you may have read that SYS is the username, but this username does not give you permission to execute commands. Your password is the same password you used when setting up the database. Hopefully you wrote it down because recovering it is not fun.
Step 3 – Select SQL Commands
After logging in you will be presented with a screen similar to the one below. You need to select SQL Commands > Enter Command:
Step 4 – Enter the Count Command
The Oracle Knowledge Base suggests that the following command be entered:
select * from pobs;
This does work. However, it will display all of the POBS data without giving you too much appreciation for how many lines are actually in the database. Use the command below instead:
select count(*) from pobs;
And yes, you do type the semicolon!
Then click on Run.
This command (and the results) is shown in the next screenshot:
Note that in the bottom part of the pane the count is zero. This is because I had already run the command a few days ago. At that time, the count was 86,987 (!) Not surprisingly, my XER export files were getting pretty chunky. A typical XER file for me is less than 1 MB but until I ran this command I had no exports smaller than 14 MB.
Step 5 – Enter the Delete Command
The final step (yeah!) is to delete the unwanted POBS data. Modify the previous command to read:
delete from pobs;
Again, make sure you include the semicolon. Also, note that you do not necessarily have to run the other command mentioned in my previous posting:
commit;
Oracle XE has an option to autocommit all commands. You will see this option in the upper left-hand corner of the screen. You won’t hurt anything by running the command but it is superfluous (just like my brother-in-law, but he thinks I’m giving him a compliment…)
A couple of other things:
- The Oracle Knowledge Base also suggests running a couple of “trigger” commands after deleting the POBS data. This is not necessary in an Oracle XE database.
- If you continue to import XER files that contain POBS data you will need to delete this data on a regular basis. Again, my original posting explains one way to clean up XER files prior to importing them.
In another post I’ll discuss three methods of backing up an Oracle XE database.
