Database Reference
In-Depth Information
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||','||L
ASTDDLOUT||','||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
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))
