Re: help for unknown oracle database

From: Jason Baugher <jason_at_baugher.pike.il.us>
Date: Fri, 05 Jul 2002 15:23:41 GMT
Message-ID: <Xns924269BDA8E4Ejasonbaugherpikeilus@209.242.76.10>

ob112_at_web.de (Oliver Brausch) wrote in
news:cd11d95c.0207042325.1dcb2773_at_posting.google.com:

> Hallo,
>
> following problem:
> I have access to an unknown oracle 8.1.7 database.
> now I want to know and view the tables. how can I do
> it without having any further information?
>
> thanks, Oliver
>
> PS:
> I have already done the following:
>

>> select table_name,owner from dba_tables where table_name like
>> 'EXTERNAL_ID%'; 

>
> hence:
>
> TABLE_NAME OWNER
> ------------------------------ ------------------------------
> EXTERNAL_ID_INFO PP_SF3
> EXTERNAL_ID_INFO PP_SF6
> EXTERNAL_ID_INFO PP_SF8
> EXTERNAL_ID_INFO PP_TEST
> EXTERNAL_ID_INFO DBFA
> EXTERNAL_ID_INFO PP_SF1
> EXTERNAL_ID_INFO PP_SF4
> EXTERNAL_ID_INFO PP_SF2
> EXTERNAL_ID_INFO DBRB
> EXTERNAL_ID_INFO PP_SF5
> EXTERNAL_ID_INFO PP_SF7
>
> 11 rows selected.
>
> now I have tried to access a table with
>

>> describe EXTERNAL_ID_INFO;

>
> ERROR:
> ORA-04043: object EXTERNAL_ID_INFO does not exist
>
> or with:
>

>> describe DBFA.EXTERNAL_ID_INFO;

>
> ERROR:
> ORA-04043: object DBFA.EXTERNAL_ID_INFO does not exist
>
> what did I do wrong?
> how do you get the right scheme? (??.EXTERNAL_ID_INFO)
> you see, I am an database beginner, so I really need help.
> thanks again.
>

You're onto the right answer here. Use the format schema.tablename to access the tables.

For instance:

describe DBRB.EXTERNAL_ID_INFO
describe PP_SF5.EXTERNAL_ID_INFO
etc...

However, since you tried that and you could not do a describe, you better check your user privs. Apparently the user you are currently connected as has privs that allow them to query dba_tables, but not the dbfa.external_id_info table. As the user that cannot query the table, do this:

SELECT * from user_sys_privs;
SELECT * from all_tab_privs_recd;

Here's a quick and dirty example, of which I'm sure there are many:

USER1 creates table TABLE1.
USER2 is only granted CREATE SESSION and SELECT ON DBA_TABLES.

USER2 can query DBA_TABLES and see the names of tables owned by USER1, but cannot do a DESCRIBE on those tables or a SELECT or anything else, since they have not be granted SELECT ANY TABLE or SELECT ON USER1.TABLE1.

You might find a copy of the Oracle 8i Reference from technet.oracle.com useful, as it lists all the data dictionary views, including those that contain the privs. I can never remember all the views and their names, so this is a good reference to have.

-- 
Jason Baugher 
Virtual Adept Professional Consulting Services
1406 Adams St.
Quincy, IL 62301
(217) 221-5406
http://baugher.pike.il.us/virtualadept
jason_at_baugher.pike.il.us

Received on Fri Jul 05 2002 - 10:23:41 CDT