JAVA J2EE 4 U

 yes I need sql script that run for 1 given database and generates a CSV Summary of these categories for easy dashboarding.

ChatGPT said:

Perfect, Daidipya! Here's a PL/SQL-based SQL script that you can run from SQL*Plus or sqlcl, which:

• Collects insights for a given Oracle database

• Summarizes:

  • LOB-heavy tables

  • Low-access or stale tables

  • Tiny status/lookup tables

• Generates CSV-like output to easily export or integrate with Confluence/MS Teams

---

✅ Script: generate_db_data_profile_report.sql

sql
SET PAGESIZE 50000
SET LINESIZE 300
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET COLSEP ','
SPOOL db_data_profile_report.csv

PROMPT CATEGORY,OWNER,TABLE_NAME,COLUMN_NAME,LOB_TYPE,BYTES_MB,SECUREFILE
-- A. BLOB/CLOB Tables
SELECT 'LOB_USAGE' AS CATEGORY,
       l.owner,
       l.table_name,
       l.column_name,
       l.column_name AS lob_type,
       ROUND(s.bytes/1024/1024, 2) AS size_mb,
       l.securefile
FROM dba_lobs l
LEFT JOIN dba_segments s ON l.segment_name = s.segment_name AND l.owner = s.owner
WHERE l.data_type IN ('CLOB', 'BLOB', 'NCLOB');

PROMPT CATEGORY,OWNER,TABLE_NAME,ROW_COUNT,INSERTS,UPDATES,DELETES
-- B. Low-Access Tables
SELECT 'LOW_ACCESS' AS CATEGORY,
       t.table_owner AS owner,
       t.table_name,
       COALESCE(tb.num_rows, 0) AS row_count,
       t.inserts,
       t.updates,
       t.deletes
FROM dba_tab_modifications t
LEFT JOIN dba_tables tb ON tb.owner = t.table_owner AND tb.table_name = t.table_name
WHERE (t.inserts + t.updates + t.deletes) < 100;

PROMPT CATEGORY,OWNER,TABLE_NAME,ROW_COUNT,BYTES_MB
-- C. Status / Lookup Tables (small, static)
SELECT 'STATIC_LOOKUP' AS CATEGORY,
       t.owner,
       t.table_name,
       t.num_rows,
       ROUND(s.bytes/1024/1024, 2) AS size_mb
FROM dba_tables t
JOIN dba_segments s ON t.table_name = s.segment_name AND t.owner = s.owner
WHERE t.num_rows < 1000
AND s.bytes < 10*1024*1024
AND (t.table_name LIKE '%STATUS%' OR t.table_name LIKE '%LOOKUP%' OR t.table_name LIKE '%CODE%' OR t.table_name LIKE '%TYPE%');

SPOOL OFF
PROMPT CSV Report Generated: db_data_profile_report.csv

---

📌 How to Run

bash
sqlplus -S / as sysdba @generate_db_data_profile_report.sql

✅ Output will be saved in:

db_data_profile_report.csv

You can then:

• Attach the CSV to your Teams channel

• Embed in Confluence tables

• Use it for capacity planning or optimization reviews

---

Would you also like:

• A version that saves each category in a separate CSV file?

• A shell wrapper to automate this across multiple Oracle homes or DBs?

Let me know how you'd like to expand it!

You said:

I guess no, sepetate CSV needed or not multiple DBS, I will run on each DB. But i would need a robust script that can give me accurate data

ChatGPT said:

Great, Daidipya. Based on that, here's a robust and production-ready version of the SQL script. It includes:

• Accurate joins with DBA_SEGMENTS, DBA_LOBS, and DBA_TAB_MODIFICATIONS

• NULL-safe logic

• Inline formatting for CSV

• Adaptive WHERE clauses to prevent over-reporting

---

✅ Finalized Script: db_data_profile_report.sql

sql
SET PAGESIZE 50000
SET LINESIZE 400
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET COLSEP ','

-- Output File
SPOOL db_data_profile_report.csv

PROMPT CATEGORY,OWNER,TABLE_NAME,COLUMN_NAME,LOB_TYPE,SIZE_MB,SECUREFILE
-- A. LOB-heavy tables
SELECT 'LOB_USAGE',
       l.owner,
       l.table_name,
       l.column_name,
       l.data_type,
       NVL(ROUND(s.bytes/1024/1024, 2), 0) AS size_mb,
       l.securefile
FROM dba_lobs l
LEFT JOIN dba_segments s
  ON l.owner = s.owner AND l.segment_name = s.segment_name AND s.segment_type = 'LOBSEGMENT'
WHERE l.data_type IN ('CLOB', 'BLOB', 'NCLOB');

PROMPT CATEGORY,OWNER,TABLE_NAME,ROW_COUNT,INSERTS,UPDATES,DELETES
-- B. Low-access tables
SELECT 'LOW_ACCESS',
       t.table_owner,
       t.table_name,
       NVL(tb.num_rows, 0),
       NVL(t.inserts, 0),
       NVL(t.updates, 0),
       NVL(t.deletes, 0)
FROM dba_tab_modifications t
LEFT JOIN dba_tables tb ON tb.owner = t.table_owner AND tb.table_name = t.table_name
WHERE (NVL(t.inserts, 0) + NVL(t.updates, 0) + NVL(t.deletes, 0)) < 100;

PROMPT CATEGORY,OWNER,TABLE_NAME,ROW_COUNT,SIZE_MB
-- C. Status / Lookup / Code tables (typically static)
SELECT 'STATIC_LOOKUP',
       t.owner,
       t.table_name,
       t.num_rows,
       ROUND(s.bytes/1024/1024, 2)
FROM dba_tables t
JOIN dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name AND s.segment_type = 'TABLE'
WHERE t.num_rows < 1000
AND s.bytes < 10*1024*1024
AND (t.table_name LIKE '%STATUS%' OR t.table_name LIKE '%TYPE%' OR t.table_name LIKE '%LOOKUP%' OR t.table_name LIKE '%CODE%');

SPOOL OFF
PROMPT >> CSV Summary Saved: db_data_profile_report.csv

---

✅ Validation Features:

• Uses NVL() for safer arithmetic and joins

• Assumes default segment types (LOBSEGMENT, TABLE)

• Avoids NULL data skewing logic

• Handles absence of stats (some DBs may not have stats gathered)

---

📎 Post-Script Recommendation

After running this:

1. Use UTL_FILE or SCP to ship CSV to a reporting location if needed

2. Load CSV into Excel or Power BI for visual profiling

3. Use this report in tuning discussions with application and infra teams

Would you like me to give you a version that:

• Only shows top 10 space consumers in each category?

• Embeds file size summary at the end?

Let me know how deep you'd like to take this — we can enhance further if needed.