How to Fetch Oracle Apps R12 Oracle E-Business Suite (EBS) All Table Counts. Query to Fetch Oracle Apps R12 Oracle E-Business Suite (EBS) All Table Counts.

 How to Fetch Oracle Apps R12 Oracle E-Business Suite (EBS) All Table Counts.

Query to Fetch Oracle Apps R12 EBS (E Business Suite) All Table Counts.

Solution :

 I have made the Custom table to Store the Oracle Apps Total Tables Count.

Table Script:

-------------------------- Table Script Start ----------------

DROP TABLE XXECO.XXECO_OWNER_CNT_TAB_DH_CP;

CREATE TABLE XXECO.XXECO_OWNER_CNT_TAB_DH_CP

(

  OWNER           VARCHAR2(35 BYTE),

  OBJECT_NAME     VARCHAR2(120 BYTE),

  TAB_STATEMENT   VARCHAR2(240 BYTE),

  TOT_ROWS_COUNT  NUMBER,

  STATUS          VARCHAR2(120 BYTE),

  CREATED         DATE,

  LAST_DDL_TIME   DATE,

  EXECUTTION_TIME DATE,

  ERROR_STATUS    VARCHAR2(1500 BYTE),

  ATTRIBUTE1      VARCHAR2(120 BYTE),

  ATTRIBUTE2      VARCHAR2(120 BYTE),

  ATTRIBUTE3      VARCHAR2(120 BYTE),

  ATTRIBUTE4      VARCHAR2(120 BYTE),

  ATTRIBUTE5      VARCHAR2(120 BYTE)

);

DROP SYNONYM XXECO_OWNER_CNT_TAB_DH_CP;

CREATE SYNONYM APPS.XXECO_OWNER_CNT_TAB_DH_CP FOR XXECO.XXECO_OWNER_CNT_TAB_DH_CP;

CREATE INDEX XXECO_CNT_TAB_DH_CP_IDX

  ON XXECO.XXECO_OWNER_CNT_TAB_DH_CP (OWNER,OBJECT_NAME);

---------------------------Table Script End ----------------

--*************************************************** --

-- XXECO_OWNER_CNT_TAB_PRC (Procedure) 

-- Custom Table : XXECO_OWNER_CNT_TAB_DH_CP 

-- Created By : Danish Halim -- 

-- Remarks : Count the ALL Oracle Apps Table -----

--

----- Concurrent Program Name Details : ----

-- Program : Econet Oracle Apps All Table Counts 

-- Short Name: XXECO_OWNER_CNT_TAB_PRC 

-- ProcedureName  : XXECO_OWNER_CNT_TAB_PRC 

-- Method : PL/SQL Stored Procedure 

-- 

-- ---- Request Group : ----

-- Responsibility : Application Developer 

-- Application : Application Object Library 

-- Request Group : Application Developer Reports 

--*************************************************** --

CREATE OR REPLACE PROCEDURE XXECO_OWNER_CNT_TAB_PRC (errnum         OUT      NUMBER,

                                                     errdus         OUT      VARCHAR2,

                                                     p_owner        IN       VARCHAR2

                                                    )

IS

 

   sql_qry           VARCHAR2 (1500);

   lv_tot_rows_count NUMBER;

   lv_error_status   VARCHAR2 (1500);

   

   

   CURSOR cur_owner_cnt

   IS      

        SELECT owner

          FROM all_objects

         WHERE object_type = 'TABLE' 

           AND owner = NVL(p_owner,owner)

      GROUP BY owner;

   

   

   CURSOR cur_insert_tab (p_owner VARCHAR2)

   IS

      SELECT owner, object_name,

             ('select count(*) C from ' || owner || '.' || object_name ) tab_statement,

             status, created, last_ddl_time

        FROM all_objects

       WHERE object_type = 'TABLE'

            -- AND owner IN ('HR'); 

        AND owner IN (p_owner);

BEGIN

   DELETE FROM XXECO_OWNER_CNT_TAB_DH_CP;

   COMMIT;

   

   FOR var1 IN cur_owner_cnt

   LOOP

           FOR var2 IN cur_insert_tab (var1.owner)

           LOOP

              lv_tot_rows_count := 0;

              lv_error_status   := NULL;

              --sql_qry:= 'select count(*) Count_HZ_CONTACT_POINTS from AR.HZ_CONTACT_POINTS';

              --DBMS_OUTPUT.PUT_LINE ('var1.TOT_ROWS_COUNT: '||var1.TAB_STATEMENT);

              sql_qry := var2.tab_statement;

              BEGIN

                 EXECUTE IMMEDIATE sql_qry INTO lv_tot_rows_count;

              EXCEPTION

                 WHEN OTHERS

                 THEN

                    lv_error_status := 'ERROR - ' || SQLERRM;

              END;

              --DBMS_OUTPUT.PUT_LINE ('Count of the Table : '||var1.object_name ||' : '||tot_rows_count1);

              INSERT INTO XXECO_OWNER_CNT_TAB_DH_CP

                          (OWNER, OBJECT_NAME, 

                           TAB_STATEMENT,

                           TOT_ROWS_COUNT, 

                           STATUS, 

                           CREATED,

                           LAST_DDL_TIME, 

                           EXECUTTION_TIME, 

                           ERROR_STATUS )

                   VALUES (VAR2.OWNER, 

                           VAR2.OBJECT_NAME, 

                           VAR2.TAB_STATEMENT,

                           LV_TOT_ROWS_COUNT, 

                           VAR2.STATUS, 

                           VAR2.CREATED,

                           VAR2.LAST_DDL_TIME, 

                           SYSDATE, 

                           LV_ERROR_STATUS 

                           );

              COMMIT;

           END LOOP;

   END LOOP;

END;

---------------- End of Procedure------

Run the Procedure with Parameter for Particulars Owner like (AP,AR,APPS,HR) Or Run with Blank for ALL OWNER Of Oracle Apps.

Data is Stored in the Custom Table XXECO_OWNER_CNT_TAB_DH_CP

For view the data Run the Below SQL Statement.

SELECT * FROM XXECO_OWNER_CNT_TAB_DH_CP;