user_procedures does not exist 2

[greenpee]

Hi,

when I desc user_procedures, it gives me object doesn't exist. But when I check "user_objects" I can see my stored procedured. Did I do something wrong?
also
Is there a way I can check all the sys tables I have in my database?

 

[SantaMufasa]

Greenpee,

"USER_PROCEDURES" exists only in Oracle 9i and above. Even then I'll bet it does not provide the information you want. If you want to see the source code of your user-defined procedures and functions, you want to query "USER_SOURCE".

Then to your question, "Is there a way I can check all the sys tables I have in my database?" I'm not positive what you are wanting to see. When you say "my database", do you mean your Oracle user/schema, or do you mean you own an entire Oracle database which includes multiple Oracle users/schemas?

Each Oracle database has a set of "data dictionary views" that are functionally/logically equivalent to the "sys" tables in other non-Oracle database engines. In Oracle, there are three major groups of data dictionary views:

1) USER_* views: these views show objects that your login owns specifically and individually.

2) ALL_* views: views that show objects to which you have access, whether you own them or someone else owns them

3) DBA_* and V%* views: views that show objects for the entire database. Usually only DBAs have access to these views. Regular users can have access to these views, as well, if a DBA grants them "select_catalog_role".

Let us know if this information resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dbtoo2001]

If you are not in the schema that created the user_procedure then you need to prefix it with the user.
Ex. desc user_who_created_it.user_procedures
or, you spelled it wrong.

Assuming you have dba authority:
select * from dba_tables where owner='SYS';

 

[greenpee]

Thanks Mufasa. These are so helpful. I can see my procs now.

When I am in schema, how can I see user_* views list? If I could see it, I might be able to figure out user_source was available and was the one I wanted.

 

[SantaMufasa]

Here is a script you can use, Greenpee:

select object_name from all_objects
where object_name like 'USER%'
  and object_type in ('TABLE','VIEW')
  and owner = 'SYS';

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[greenpee]

Thanks Mufasa. This code works perfectly. You solve my problem!!