Re: Query to retrieve user tables only

<dotlambda_at_gmail.com> wrote in message
news:1173136868.154976.199330_at_n33g2000cwc.googlegroups.com...
> On Mar 5, 5:49 pm, "Andreas Sheriff" <spamt..._at_iion.com> wrote:
>> <dotlam..._at_gmail.com> wrote in message
>>
>> news:1173134195.202236.229890_at_64g2000cwx.googlegroups.com...
>>
>>
>>
>> > Hi,
>>
>> > I need to retrieve a list of tables created by users.
>>
>> > The following query is not suitable because it retrieves system (or
>> > not user defined) tables as well:
>>
>> > SELECT * FROM user_tables
>>
>> > So far I've been using the following one:
>>
>> > SELECT table_name, tablespace_name FROM user_tables WHERE table_name
>> > NOT LIKE '%$%' AND tablespace_name <> 'SYSAUX'
>>
>> > Can you confirm that this is the right way of achieving this? Or is
>> > there a more accurate query?
>>
>> > Thank you in advance.
>>
>> Please note:
>>
>> a.. DBA_TABLES describes all relational tables in the database.
>>
>> b.. USER_TABLES describes the relational tables owned by the current
>> user.
>> This view does not display the OWNER column.
>>
>> You should be selecting from dba_tables. Also, look in dba_users and
>> filter
>> out from your query any user that belongs to Oracle.
>>
>> You do know who your users are, right?
>
> Hi Andreas,
>
> thanks for your answer. The user provides his credentials to my
> application that connects to an Oracle database and run some
> statistics on all the accessible user tables. I need to consider all
> the tables this user has access to, so even if the table were created
> by other users as long as the credentials provided do have permission
> on them, they should be considered. This user pretty much has to be an
> admin (it is expected). A simplified scenario where I use a clause
> OWNER = 'myuser' wouldn't be sufficient then.
>
> I appreciate and understand the principle of your solution:
> essentially retrieve a list of users from dba_users and then obtain
> the table list from dba_tables by looking up the OWNER column for each
> of these users. Unfortunately I'm not too familiar with Oracle and its
> SQL, would it be possible for you to translate these two steps into a
> single SQL query?
>
> Thank you so much for your help.
>

Hi,

technet.oracle.com has loads of Oracle information and documentation.

I believe you need the view ALL_ALL_TABLES. ALL_ALL_TABLES describes the object tables and relational tables accessible to the current user.

Just login as the user you want to query and

select owner, table_name from all_all_tables;

It's *one* of the simplest ways to see what objects the current user can see (disclaimer). Received on Mon Mar 05 2007 - 17:45:41 CST