Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Something strange - am puzzled.
Was experimenting this morning and have found something puzzling.
XP Pro SP2, Oracle 9.2.0.1.0 (don't ask - broadband's a comin!).
Log in as scott/tiger - run select count(*) from all_tables, and get the result 35. So, I think to myself that there's the scott schema, and then tables to which scott has access as a lowly user of the system, such as dual and a few others.
Then I created a system DSN in the admin tools for XP, using the "Oracle in OraHome92" driver, which I presume is supplied by Oracle itself.
I have the username as scott/tiger in the DSN!
Then I open Excel, New Workbook, then Data -> Get External Data ->
I then choose the Oracle datasource, and am prompted for the password which I enter.
Then I am presented with a query wizard and low and behold, I can see a whole load of tables, much more than 33 - approx. 250, which is less than the 834 I can count as sys/xxxx as sysdba.
Now, if try and place data from the db into the Excel spreadsheet and I choose one of the tables visible to scott in SQLPlus, I can do it.
If I choose one of the tables that appears in the query wizard, but not in the list visible to scott in SQLPlus, then I am given the error message ORA-00903 invalid table name.
*_BUT_*, when I go back into SQLPlus (as scott) I *_can_* select from these tables.
So, obviously scott can access 250 odd tables, but I can't see these using select owner, table_name from all_tables.
How can I see *_all_* the tables to which scott has access?
TIA. Paul...
-- plinehan __at__ yahoo __dot__ __com__ XP Pro, SP 2, Oracle, 9.2.0.1.0 (Enterprise Ed.) Interbase 6.0.1.0; When asking database related questions, please give other posters some clues, like operating system, version of db being used and DDL. The exact text and/or number of error messages is useful (!= "it didn't work!"). Thanks. Furthermore, as a courtesy to those who spend time analysing and attempting to help, please do not top post.Received on Sat Jul 23 2005 - 07:53:55 CDT