Oracle User Cannot See tables or select

Posted on 2009-02-09
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?

Question by:mattturley
    LVL 10

    Expert Comment

    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 Comment

    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.

    LVL 10

    Accepted Solution

    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

    Author Comment

    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.
    LVL 6

    Expert Comment

    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;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now