Re: Show user's tables & views

In article <38ibl1$176n_at_ctsc.hkbc.hk>, PM Wong <s11976_at_ctsc.hkbc.hk> wrote:
>Sorry for this trivial question. We have many old users which are to be
>dropped. It is good practice (I think) to know what tables (and/or views)
>they created so we can drop them before we revoke connect & resource
>from such users
>But I don't know how to list all the tables and views created by all users
>of the tablespace.
>Can someone tell me the command please
>--

The following can be done from a DBA Oracle account:

To create a list of DROP TABLE commands to execute for a specific tablespace:

  select 'DROP TABLE '||owner||'.'||table_name||';'   from dba_tables
  where tablespace_name = upper('&tablespace');

To do this for a given list of users in a tablespace add to the WHERE clause:

  and owner in ('Owner1','Owner2'...);

To do this for a given user without considering the tablespace make the WHERE clause:

  where owner = 'Owner1';

In regards to VIEWs, views are not created in a given tablespace. (They are all stored in Oracle data dictionary tables in the SYSTEM taslespace.) The same technique can be used on the DBA_VIEWS table. I will not give the SELECT statement. This will be similar to the technique above.

Generally do:

1. Start SQLPLUS, logg into a DBA Oracle account.
2. Issue SPOOL file_name command to spool drop statements to a file named 'file_name'
3. Execute the appropiate SELECT statement to create DROP statements
4. Issue SPOOL OFF command to close spool file
5. Execute the spool file by _at_file_name

I have done this in the Oracle V6. I would assume that the data dictionary views DBA_TABLES and DBA_VIEWS in Oracle7 can be used in the same way.

Normal disclaimers apply: "Do at your own risk.... Blah...Blah...Blah..." Received on Tue Oct 25 1994 - 21:47:25 CET