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;
