| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-12-10, 05:23
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 5
|
|
|
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!
|
|
08-12-10, 05:57
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
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).
|
|
08-12-10, 06:00
|
|
Registered User
|
|
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
|
|
08-12-10, 07:21
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
What does a "cloned user" mean? Which part of Oracle are we discussing?
|
|
08-12-10, 07:30
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 5
|
|
created my user by copying attributes from another user
|
|
08-12-10, 07:49
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
I still don't understand. Where did you do that? How?
|
|
08-12-10, 07:53
|
|
Registered User
|
|
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
|
|
08-12-10, 08:01
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
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.
|
|
08-12-10, 08:07
|
|
Registered User
|
|
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
|
|
08-12-10, 08:19
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
What is the result of the following statement:
Code:
select * from user_objects
where object_name = 'NAME_OF_A_TABLE_WE_ARE_TALKING_ABOUT';
|
|
09-04-10, 11:06
|
|
Registered User
|
|
Join Date: Mar 2010
Location: Vienna, Austria
Posts: 130
|
|
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>
__________________
If A is a success in life, then A = x + y + z.
Work is x; y is play; and z is keeping your mouth shut. After all the years, I'm still working on the correct value for z.
(Albert Einstein)
|
Last edited by magicwand; 09-04-10 at 11:21.
Reason: highlighting some lines
|
09-07-10, 08:22
|
|
Lead Application Develope
|
|
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,222
|
|
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. 
|
|
09-07-10, 11:24
|
|
Registered User
|
|
Join Date: Oct 2002
Location: Cape Town, South Africa
Posts: 161
|
|
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?
|
|
09-08-10, 03:16
|
|
Registered User
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Cant see table data
