How to get row counts/sizes for all the tables for the Oracle Cloud ERP application

[amit_kothari]

To ensure that the Incorta cluster is properly sized for the data being ingested from Oracle Cloud ERP (Fusion) we need to ascertain the source data size. There is no direct way to run ad hoc SQL queries on the Oracle Cloud ERP database, unlike traditional on-premises systems. However, there’s a powerful workaround: BI Publisher. All Oracle Cloud ERP environments come with BI Publisher enabled, allowing users to create custom SQL-based reports using data models.

This guide walks you through a simple 2-step process to retrieve row counts and table sizes.

---

✅ Prerequisites

• You need BI Publisher access in your Oracle Cloud ERP environment.
• To access the BI Publisher console, append /analytics to your base URL:
   https://<yourEnv>.oraclecloud.com/analytics

---

🧩 Step 1: Create a Data Model

1. Log into BI Publisher (/analytics).

2. On the top-right, click Create → Data Model.

3. Click + New Data Set → SQL Query.

4. Select a Data Source:
   Choose the appropriate Fusion database based on your module:

   • ApplicationDB_FSCM (Finance/Supply Chain)

   • ApplicationDB_HCM (HCM/HR)

   • ApplicationDB_CRM (Customer Relationship)

5. Set SQL Type: Choose Standard SQL.

6. Enter SQL Query:
   Here's a sample query to get row counts and size estimates:

   sql

   CopyEdit

   SELECT owner, table_name, num_rows, ROUND(blocks * 8 / 1024) AS size_mb FROM all_tables WHERE owner NOT IN ('SYS', 'SYSTEM') AND blocks > 0 ORDER BY owner, table_name;

7. Click View Data. Wait a couple of minutes for the data model to be fully built.

8. Click Save Sample Data — this is required before you can use the data model in a report.

9. Save the data model with a recognizable name.

---

📊 Step 2: Create a BI Publisher Report

1. Go back to the BI Publisher home screen.

2. Click Create → Report.

3. When prompted, choose the data model you just created.

4. Continue through the wizard to design the report layout (table, chart, or raw data).

5. Save and run the report to view live row counts and table size information.

---

📝 Notes

• Ensure your BI Publisher role has appropriate privileges to access these data sources.
• If you run into access errors, reach out to your Oracle Cloud ERP admin to validate BI and security roles.
• This method only reads metadata and summary information — it's safe and won’t impact system performance.