Database Size Matters: Understanding the Impact of Attachments on Your Oracle DB…and What You Can Do to Manage It

How Much Space in Your BLOB Oracle Database Is Used by Attachments?

As many of our customers know, our AventX software exists to make our customer’s use of attachments streamlined, simpler, and automated. Whether it is attachments for Work Orders, POs, Invoices, etc., AventX automates the delivery of your primary EBS reports with critical attachments.

As a result, our customers have asked us many times over the years for help determining the “weight” of attachments on their Oracle EBS database. One recent example, more below, was a customer with more than 400 GBs of attachments for maintenance alone.

Usually, our answer is to write a quick query for the customer to check for the data quickly. Now we are finally doing something about it. We’re publishing these queries and providing a solution to help DBAs manage those files that are putting extra strain on their databases.

But first…

…How We Got Here

Oracle’s Document Catalog and Paper Clip features allow users to attach any document to any Record quickly. For many years, the features have been integral components of the Oracle EBS ecosystem.

The features serve as tools to help manage critical attachments stored against Oracle Records.

Using the Attachments Window, EBS Users can view, delete, and create attachments quickly. Standard Oracle functionality lets the user manage various attachment types, including Short Text, Long Text, Images, File, and Web Page.

How to Check Your Database Usage for Attachments

Need a quick way to determine the resources your attachments are eating up in your database?

Run the following query (executed as APPS) to see, in GBs, the DB space consumed by your File Attachments.

Need some help with a specific query? Use the contact form on our website, and one of our local query wizards can try to help you out.

What number did you come up with?

Don’t be scared. You are not alone.

The High Cost of Oracle Database Management

As a DBA, you likely know that a large database means many things. The biggest of which are High Maintenance Costs and Poor Performance.

High maintenance costs come in many different forms…additional hardware and software, licensing, compliance, and training. Plus, the time DBAs spend just to keep the database running—backups, audits, upgrades.

But the real trouble comes with poor performance. If that database is not meeting the needs of your users, you are going to hear about it. Users who need to access the information and files in the database expect their searches to return instant or near-instant results.

While DBAs are there to maintain the database, most of their time is spent ensuring that end users have the information they need when they need it. They should be focused on the big picture…things like uptime, security, backups, not where users are saving their files.

Take Control of Your BLOBs

How to Free Up Precious Database Space Quickly & Permanently

If you ran one of the queries above, you have probably realized that one of the biggest culprits of disk space usage is Oracle EBS File Attachments. Here are a few ways you get back your BLOBs!

Option 1 – Move All the Attachments a NAS Server or Network Folder, then Retrain Users Where to Save & Access Files

Sounds easy enough.

Just retrain your Oracle Paperclip users to:

1. Stop using the Paperclip to save File Attachments to Oracle Records (you need to squash the bad habits first)
2. Instead, save the document to a mapped folder (creating a new folder structure if needed for organization)
3. Copy the Windows Explorer path to the hyperlink (it’s easy: Hold Shift > Right-Click the File > Select Copy as Path)
4. With the file path in hand, now use the Paperclip to save a Web Page attachment to the Oracle Record.

Steps aside, we probably lost you at “Retrain Users,” right? Plus, in the end, it’ll just slow users down and create more support tickets (at least for a while).

All these extra steps defeat the purpose of Oracle EBS Attachments. The beauty of the process is that it’s easy to do, and the users have the files right there, at their fingertips. With the beauty, you get the beast—new, cumbersome process.

Option 2 – Implement File Management Strategies to Reduce Erroneous Files and “Minimize” File Sizes

New procedures are rarely fun and overly time-consuming to implement. But sometimes they can be worthwhile.

Creating procedures about what files can be attached to an Oracle Record is an excellent place to start. For example, DO NOT attach files greater than 5 MBs. And ONLY attach .PNG or .GIF images, because .TIFF files can be huge.

Oracle EBS can be configured to help you manage to these new standards. But the reality is, there will always need to be an exception to the rule.
Maintenance teams will have PDF files that are hundreds of pages long. Accounting will have .XLSX spreadsheets with a few dozen tabs, pivot tables, and who-knows-what-else.

The point being, launching new procedures and maintaining these practices will be nearly impossible to execute.

Option 3 – Migrate the File Attachments to SharePoint as Oracle EBS Web Page Attachments

Crazy idea here. Why move Files from one database to another? The simple answer is: Oracle EBS was not designed to manage documents. SharePoint is a file storage solution that was designed to manage files, make them easily accessible (mapped drives or browser window), make them searchable with metadata and columns, and make the content easy to backup and restore.

In essence, use Oracle EBS as the database it was designed to be and use SharePoint Online as the file management solution it was intended to be.
DBAs will get the database space they need. End users will get a faster experience and more utility from a proper document management system.
Plus, “native” file formats are now “live” documents that can be updated in real-time without reattaching the document to Oracle EBS.

1. SharePoint is designed for storing and managing files – more options, better interface
2. SharePoint directories can be mapped to PCs giving users another option
3. SharePoint can make files easier to search with metadata and information in columns
4. SharePoint is much easier to scale and maintain

The best part is, no retraining is required, and no new procedures need to be implemented.

This option can be completed automatically with a small AventX for Oracle EBS to SharePoint Online connector.

The process automatically (1) migrates old and new File Attachments from Oracle EBS to SharePoint, then (2) reinserts the document back into EBS as a Web Page Attachment. Freeing up database space, creating a better user experience (if desired), and providing a much more efficient tool for long-term document storage and management.

(Plus, if you are planning a move to the cloud—either IaaS or SaaS—a solution such as this can reduce the complexities involved with the transition.)

There you have it, three solutions for freeing up database space consumed by Oracle EBS File Attachments. Each solution carries with it PROs and CONs, but all offer better long-term solutions than continuing to let users freely attach documents to BLOB storage. With the right strategy, you can streamline your database space, provide a better user experience, and implement a solution with almost no training needed. Start by checking out the newest addition to the AventX product suite for application-to-application file transfer.