set showmode off echo off
set heading off pagesize 0 timing off feedback off linesize 80
set trimspool on termout off verify off
rem
rem Script: formtables.sql
rem
rem Purpose: Get the list of non-system tables for formtables.shl.
rem    You must be logged in as SYS or SYSTEM or as a DBA user ID.
rem
rem Author: Stephen Rea <srea@maristream.org>
rem    Maristream, Inc.
rem
rem History:
rem    9/16/99: Original Version.
rem    9/21/99: Exclude user tables.
rem    9/23/99: Exit at end for running from formtables.shl.
rem    6/25/02: Add switch (include_views) to include views in
rem             addition to the tables, if wanted.
rem
define include_views=n
rem
spool formtables.tables
select distinct upper(table_name) from dba_tables
  where owner not in ('SYS','SYSTEM','SCOTT')
  and tablespace_name != 'USERS' and '&include_views' != 'y';
select distinct upper(object_name) from dba_objects
  where owner not in ('SYS','SYSTEM','SCOTT')
  and object_type in ('TABLE','VIEW')
  and not exists (select 'x' from dba_users where
  username = owner and default_tablespace = 'USERS')
  and '&include_views' = 'y';
spool off
set pagesize 24 linesize 80 heading on termout on
set timing on feedback 6 verify on echo on showmode both
exit