We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Oracle User Cannot See tables or select

mattturley
mattturley asked
on
Medium Priority
2,652 Views
Last Modified: 2012-05-06
I am having problems with a  user not being able to see tables, select data, or do anything other than logging in.  I granted the user all priveleges using

grant all privileges to UPGRADE

If I login to Enterprise Manager with that user, I can see all objects in the database of concern (DOLPLAT).  However, if I connect via SQLDeveloper or SQL Plus, I can only login.  I cannot select data or in any other way work with the database - outside of logging in.

I am sure this could be one of many things.  Can you suggest some troubleshooting steps/tips I might look towards?

Thanks,
Matt
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Walk me through some of the steps.. You created an user and provided all the privileges.. Now you are trying to view objects.. What command are you using in SQL Plus? Try executing the below queries

select * from dba_objects;

select * from dba_objects where owner='UPGRADE';

First query would return all the objects in the DB. Second query will return only the objects owned by UPGRADE

Author

Commented:
I created the user in Enterprise Manager, granted all privileges.  If I try to query the db, for example:

select * from pa_student;

I get ORA-00942: table or view does not exist.

I can easily browse to the table and view data when logged into EM as this user.

Your first query pulled back thousands of rows - I stopped it.

The second pulls back zero rows.

CERTIFIED EXPERT
Commented:
Ok.. This means the user UPGRADE does not own any object.. When you are accessing other users objects, you should access like this..

select * from <username>.table;

If you see EM, it would be displaying the tablename in the same way. Owner.objectname

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
It isn't indicating that way in EM - just has the table name.It doesn't show that way in EM, but when I query that way in SQL Plus, I get no rows returned, instead of table or view does not exist.

Commented:
When you navigate in EM you choose "schema" -> "schema name" -> "tables" -> "your table" right?

Schema is the owner, the name of the owner.

Like ravindran_eee wrote..
select * from <username>.table; = select * from <schema_name/owner>.table;
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.