Expert, flexible training in the use of the most powerful scheduling software program in the world: Primavera P6 by Oracle. Call today! (916) 779-4145
Primavera Scheduling

All posts tagged Oracle XE Database

Delete POBS Data in an Oracle XE Database

Categories: Oracle XE Database, P6 Professional, POBS Table
Comments Off on Delete POBS Data in an Oracle XE Database
Primavera Scheduling

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:

Oracle XE_Database Home Page

 

 

 

 

 

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:

Oracle XE_Database Login

 

 

 

 

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:

Oracle XE_SQL 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:

Oracle XE_Select Count

 

 

 

 

 

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:

  1. 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.
  2. 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.

 


Put Your Primavera XER File on a Diet

Categories: Oracle XE Database, P6 Professional, POBS Table, Primavera P6, Primavera XER
Comments Off on Put Your Primavera XER File on a Diet
Primavera Scheduling

You have probably noticed that after using Primavera P6 for a while your XER files seem to be getting bigger. Actually, a lot bigger. Files that used to be under 1 MB are now more than 10 MB. This slows down the importing of XER files considerably and makes it harder to email files without using a large-file service  like Hightail or Dropbox.


According to Oracle, this problem affects Versions 7.0 and later.


The XER file format is the most popular way to exchange project data between databases. As a consultant, I always have to send my files to the client. There is another Primavera format – XML – but these files tend to be rather large compared to Primavera XER. The beauty of the XER format is that it is text-based, which normally results in a very small file.

Nevertheless, XER files have a bad habit of getting rather chubby, if not downright HUGE. This would be understandable if it served some purpose. Unfortunately, the increased size is a complete waste of space. And in some cases it takes so long to import the XER file that P6 effectively locks up. Not good.

Specifically, the problem is the POBS Table. Join the club if you have never heard of this data field. Here is the official explanation from Oracle as to the purpose:

Functions related to table POBS have not been implemented yet so the table has not been put to use. The table may be removed in a future release.

You do have to wonder, if Oracle has not found a purpose for the POBS Table yet, why has its use persisted for so long? This is not the first time a feature showed up without a purpose. For several years Primavera P6 displayed a colored bar next to the Activity Code Values. The default color was blue. Regardless, changing the color did nothing because the feature was intended for Visualizer, which had not been incorporated yet:

Activity Codes Menu

Since the POBS Table is not used at all, we might as well delete it. Granted, if your XER files are rather small it is not a necessary step. Until recently all of my XER files were reasonably sized considering the size of the projects. But starting several months ago my XER files started gorging themselves. A POBS Table with a few dozen lines suddenly had thousands of lines. Deleting the POBS Table became a necessity for me.

Assuming you are using an Oracle database it is rather easy to delete the POBS Table in the database so that export files do not include this table by running the following statement inside the database after backing it up:

  • delete from pobs;
  • commit;

**UPDATE** Please see my post regarding how to Delete POBS Data in an Oracle XE Database for more detailed instructions on how to execute the above commands.


Files being imported into the database, however, most likely will have the POBS Table. So it may become necessary to modify the XER files prior to importing them. Here are the steps:

  1. Make a copy of the XER file (just in case!)
  2. Open the XER file in a text editor like Notepad
  3. Find (Ctrl+F) the line that starts with %T POBS
  4. Select the following lines that start with:
    1. %T POBS
    2. %F (including all line content)
    3. %R (including all line content)
  5. Stop at the next %T
  6. Delete the above lines
  7. Save the XER file
  8. Import the XER file

Below is a screenshot showing each type of line that must be deleted:

POBS Table

Note that lines that start with %R may number in the hundreds or even thousands. The above image does not reflect the thousands of %R lines that existed in this particular XER file.

The next %T will be very close to the end of the file. The rest of the lines pertain to project data that should not be deleted, which is why we stop at the next %T. The biggest headache is getting all of the %R lines highlighted.

Still, deleting the POBS Table can yield startling results. Starting with a XER file that was 14.4 MB (14,377 KB) in size, I employed the technique described above to delete the POBS Table. The new XER file was just 105 KB (!)

It is always a good idea to make a backup copy of the XER file just in case you delete the wrong lines. But otherwise this is a quick and easy way to drastically reduce the size of XER files.

Update: starting in 2016, Oracle introduced SQLite as the default database for standalone users. This new database does not use the POBS table and therefore the problem I am describing no longer occurs.

Any comments or questions? Please email me.