Oracle: Get list of functions for user

Question

How do I get a list of all the functions for a particular user?

EDIT for question clarification:

When (as USER1) I run

select * from all_objects
where owner = 'USER2'
and object_type = 'FUNCTION';

it doesn't return all the functions that I know USER2 owns. I suspect that it is only returning those functions that USER1 is allowed to view/execute.

Is that suspicion correct?

Also, if that is true, is there a way to get around this?

Answer 1

Yes, your suspicion is correct. The ALL_OBJECTS view will only list those items that the current user has access to.

If you can log in as USER2, then you can query USER_OBJECTS as that user to see all objects owned by that user.

If you can log in as SYSTEM, then you would have access to all objects regardless of owner, so the list provided by ALL_OBJECTS (or DBA_OBJECTS) would be complete.

If you can't log in as a user that has access to all of USER2's objects, then you can't list all of USER2's objects.

Answer 2

If you mean a list of functions the belong to a particular user then:

select object_name
from   all_objects
where  owner = 'WHOEVER'
and    object_type = 'FUNCTION';

This will return only stand-alone functions, not procedures or function in packages, that belong to the schema 'WHOEVER'.

To obtain a list of all functions that the current user can access:

select object_name
from   all_objects
where  object_type = 'FUNCTION';