Thursday, June 15, 2006

roles,grants,User session,object dependencies Version 1.0

1.ABC_RW_ROLE what grants on which CC_schema tables?
select * from role_tab_privs where owner='CC_schema' and ROLE='ABC_RW_ROLE';
2.User is running what SQL?
select * from v$sql s where
s.SQL_ID in (select SQL_ID from v$session where username like 'XXX%')
3. Object dependency:

create or replace procedure find_object_dependency
(
p_OBJECT_NAME all_objects.OBJECT_NAME%type,
p_OWNER all_objects.OWNER%type,
p_OBJECT_TYPE all_objects.OBJECT_TYPE%type
)
is

vObjID all_objects.OBJECT_ID%type;

lObjID all_objects.OBJECT_ID%type;

cursor csr1
(
p_OBJECT_NAME all_objects.OBJECT_NAME%type,
p_OWNER all_objects.OWNER%type,
p_OBJECT_TYPE all_objects.OBJECT_TYPE%type
)
is
SELECT ao.OBJECT_ID FROM all_objects ao
WHERE
ao.OBJECT_NAME=UPPER(p_OBJECT_NAME) AND
ao.OWNER=UPPER(p_OWNER) AND
ao.OBJECT_TYPE=UPPER(p_OBJECT_TYPE);


cursor csr2(vObjID all_objects.OBJECT_ID%type) is
SELECT
ao.OWNER,
ao.OBJECT_NAME,
ao.OBJECT_TYPE,
ao.STATUS
FROM all_objects ao
WHERE
ao.OBJECT_ID IN
(SELECT pd.OBJECT_ID FROM public_dependency pd
WHERE
pd.REFERENCED_OBJECT_ID=vObjID);


begin

open csr1(p_OBJECT_NAME,p_OWNER,p_OBJECT_TYPE);

fetch csr1 into vObjID;

dbms_output.put_line('Source object ID '||vObjID);

dbms_output.put_line('*************************************************************************************');

for rs2 in csr2(vObjID)

loop

dbms_output.put_line('dependent object owner # '||rs2.OWNER||' name # '||rs2.OBJECT_NAME||' TYPE # '||rs2.OBJECT_TYPE||' status #'||rs2.STATUS);

end loop;

close csr1;

end find_object_dependency;

--exec find_object_dependency('emp','sbiswas','table');

No comments:

Post a Comment