Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2010
    Posts
    5

    Unanswered: Cant see table data

    Hi
    I am using Oracle 10.2 on AIx platform. I cannot see data on a particular table. I can see the table(desc) and I can do a count(*) but get 0 rows. If someone else looks at the table they see data. The table is set up the same as another that I can see the data on. I dont get error messages as I have access to table just cant see the data. Help!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps "someone else" entered data into a table but didn't commit (yet), so he/she sees it, but you don't.

    Perhaps you and "they" didn't connect to the same schema (or a database).

  3. #3
    Join Date
    Aug 2010
    Posts
    5
    the table has a lot of production data. My user was cloned from the other user that can see the data. He can see table and data I can see table but no data

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What does a "cloned user" mean? Which part of Oracle are we discussing?

  5. #5
    Join Date
    Aug 2010
    Posts
    5
    created my user by copying attributes from another user

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I still don't understand. Where did you do that? How?

  7. #7
    Join Date
    Aug 2010
    Posts
    5
    within toad - look at an existing user and grab the sql that would create that user, edit the user id and run it to create a new user

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So - basically - you have executed a CREATE USER statement. Right? Anything else? Did you grant certain privileges? If so, which ones?

    Because, a newly created user sees only his own objects unless someone granted privileges on someone else's objects (which includes tables). So, the fact that "someone else" sees data in his/her own table doesn't mean that you'll see those data too. Even though you and another user have the same table in your schemas, his/her table contains data while your - probably - doesn't.

    It is difficult to guess what might have happened with information you provided.

  9. #9
    Join Date
    Aug 2010
    Posts
    5
    Its not just a create user though as the script shows all the priveleges that the current user has - so effectively I have been given all those priveleges. Like I said - I can see the table - do a desc etc - just cant see the data

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is the result of the following statement:
    Code:
    select * from user_objects
    where object_name = 'NAME_OF_A_TABLE_WE_ARE_TALKING_ABOUT';

  11. #11
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    davefinch,

    the only way I can reproduce the behavior you describe is, when your "table" is actually not a table, but a view having the username as part of its definition:

    a draft to show my point, where TST is the user owning the objects, T1 is the user "where it works" and t2 the "cloned" T1 user:

    Code:
    SQL> connect system
    Enter password:
    Connected.
    SQL> create user tst identified by tst default tablespace users quota 10M on users;
    User created.
    
    SQL> grant create session to tst;
    Grant succeeded.
    
    SQL> grant create table to tst;
    Grant succeeded.
    
    SQL> grant create view to tst;
    Grant succeeded.
    
    SQL> create user t1 identified by t1;
    User created.
    
    SQL> create user t2 identified by t2;
    User created.
    
    SQL> grant create session to t1;
    Grant succeeded.
    
    SQL> grant create session to t2;
    Grant succeeded.
    
    SQL> connect tst/tst
    Connected.
    
    SQL> create table test (id number, info varchar2(32), usr varchar2(32));
    Table created.
    
    SQL> insert into test select rownum, 'Row #' || rownum, 'TST' from all_objects where rownum < 101;
    100 rows created.
    
    SQL> create view v_test as select * from test where usr = user;
    View created.
    
    SQL> update test set usr = 'T1' where id between 10 and 19;
    10 rows updated.
    
    SQL> commit;
    Commit complete.
    
    SQL> grant select on v_test to t1;
    Grant succeeded.
    
    SQL> grant select on v_test to t2;
    Grant succeeded.
    
    SQL> -- we are still user TST
    SQL> select count(*) from tst.v_test;
      COUNT(*)
    ----------
            90
    
    SQL> connect t1/t1
    Connected.
    SQL> select count(*) from tst.v_test;
      COUNT(*)
    ----------
            10
    
    SQL> connect t2/t2
    Connected.
    SQL> select count(*) from tst.v_test;
      COUNT(*)
    ----------
             0
    SQL>
    Last edited by magicwand; 09-04-10 at 12:21. Reason: highlighting some lines
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    a user does not have privileges assigned to access his own objects, its intrinsic to the schema. When you create a cloned user, you do NOT get the ability to see their objects, those still must be granted by a dba or the original schema.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    It seems like everyone trying to help you actually needs more info in order to do so.
    I would like to know what roles have been granted to your user? You can check this in Toad. Give us a list.
    Also, is the table you are trying to access in a different schema?
    Are you running Oracle Enterprise or Standard edition?

  14. #14
    Join Date
    Sep 2010
    Location
    California
    Posts
    1

    More info

    Hi all,

    I'm new here but may be able to offer some perspective. Agree, though, that some more info would be helpful.

    MrkB1969

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •