Re: Query to retrieve user tables only

<dotlambda_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? Received on Mon Mar 05 2007 - 16:49:21 CST