Re: Query to retrieve user tables only

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. Received on Mon Mar 05 2007 - 17:21:08 CST