create table PACKAGESTATESORAGOL2(OWNERIN VARCHAR2(30),USER$NAME VARCHAR2(30),OBJ$OWNER VARCHAR2(30),
NAMEIN VARCHAR2(30),
SOURCE$OBJID NUMBER,
OBJ$TYPE VARCHAR2(30),
COUNTOUT NUMBER,
CTIMEOUT TIMESTAMP,
STIMEOUT TIMESTAMP,
LASTDDLOUT TIMESTAMP,
HASH NUMBER);

CREATE OR REPLACE PROCEDURE PACKAGESTATE(OWNERIN VARCHAR2) AS TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
USER$NAME VARCHAR2(30); --
OBJ$OWNER VARCHAR2(30);
NAMEIN VARCHAR2(30);
SOURCE$OBJID NUMBER;
OBJ$TYPE VARCHAR2(30);
COUNTOUT NUMBER;
CTIMEOUT TIMESTAMP;
STIMEOUT TIMESTAMP;
LASTDDLOUT TIMESTAMP;
HASH NUMBER;
BEGIN
OPEN CV FOR 'SELECT  sys.user$.NAME , sys.obj$.owner#, sys.obj$.NAME, sys.source$.obj#, sys.OBJ$.TYPE#, Count(sys.source$.line), ctime, stime, mtime from (sys.source$@oragol2  join sys.obj$@oragol2
ON sys.source$.obj#=sys.obj$.obj#)
inner join sys.user$@oragol2 ON sys.obj$.owner# = sys.user$.user#
where sys.obj$.TYPE#=12
And  sys.user$.NAME = :x GROUP BY  sys.user$.NAME, sys.obj$.owner#, sys.obj$.NAME, sys.source$.obj#, sys.OBJ$.TYPE#, ctime, stime, mtime' using OWNERIN;
LOOP
FETCH CV INTO USER$NAME, OBJ$OWNER, NAMEIN, SOURCE$OBJID, OBJ$TYPE, COUNTOUT, CTIMEOUT, STIMEOUT, LASTDDLOUT;
DBMS_OUTPUT.ENABLE(200000);
SELECT SUM(dbms_utility.get_hash_value(source,1000000000,power(2,30))) INTO HASH from sys.source$@oragol2 where sys.source$.obj#=SOURCE$OBJID;
DBMS_OUTPUT.PUT_LINE(OWNERIN||','||USER$NAME||','||OBJ$OWNER||','||NAMEIN||','||SOURCE$OBJID||','||OBJ$TYPE||','||COUNTOUT||','||CTIMEOUT||','||STIMEOUT||','||LASTDDLOUT||','||HASH);
insert into PACKAGESTATESORAGOL2 values(OWNERIN,USER$NAME,OBJ$OWNER,NAMEIN,SOURCE$OBJID,OBJ$TYPE,COUNTOUT,CTIMEOUT,STIMEOUT,LASTDDLOUT,HASH);
EXIT WHEN CV%NOTFOUND;
END LOOP;
CLOSE CV;
END;
/
show errors

--useful queries below.

exec packagestate('SYS');
exec packagestate('SYSMAN');
exec packagestate('SYSTEM');

COMMIT;

SELECT * FROM PACKAGESTATESORAGOL2;
select * from PACKAGESTATESORAJAN ORDER BY NAMEIN;


((select * from PACKAGESTATESORAGOL2)
MINUS
(select * from PACKAGESTATESORAJAN))
UNION
((select * from PACKAGESTATESORAJAN)
MINUS
(select * from PACKAGESTATESORAGOL2))


SELECT * FROM dba_dependencies WHERE name='CDC_DROP_CTABLE_BEFORE';

SELECT * FROM dba_objects WHERE object_NAME='DBMS_CDC_IPUBLISH';

SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME='oracle/CDC/SubscribeApi';