REM script to document extended table parameters

REM
REM     Name:      tab_rep.sql
REM     FUNCTION:  Document table extended parameters
REM     Use:       From SQLPLUS 
REM     MRA 6/13/97 Created for ORACLE8
REM   MRA 5/08/99 Updated for ORACLE8i
REM  MRA 10/08/01 Updated for Oracle9i
REM
COLUMN owner            FORMAT a10 HEADING 'Owner'
COLUMN table_name       FORMAT a15 HEADING 'Table'
COLUMN tablespace_name  FORMAT a13 HEADING 'Tablespace'
COLUMN table_type_owner FORMAT a10 HEADING 'Type|Owner'
COLUMN table_type       FORMAT a13 HEADING 'Type'
COLUMN iot_name         FORMAT a10 HEADING 'IOT|Overflow'
COLUMN iot_type         FORMAT a12 HEADING 'IOT or|Overflow'
COLUMN nested           FORMAT a6  HEADING 'Nested'
COLUMN extern           FORMAT a3  HEADING 'Ext'
UNDEF owner
SET LINES 130 VERIFY OFF feedback OFF PAGES 58
START title132 'Extended Table Report'
SPOOL rep_out\&&db\ext_tab.lis
SELECT
     owner,                          
     table_name,                     
     tablespace_name,                              
     iot_name,                       
     logging,                                      
     partitioned,                    
     iot_type,
      'N/A' table_type_owner,
      'N/A' table_type,                       
     DECODE(temporary,'N','No',temporary),                      
     nested,
     'N/A' extern 
FROM 
     Remote DBA_tables 
WHERE 
     owner LIKE UPPER('%&&owner%')
UNION 
SELECT
     owner,                          
     table_name,                     
     tablespace_name,                              
     iot_name,                       
     logging,                                      
     partitioned,                    
     iot_type,                       
     table_type_owner,               
     table_type,                                     
     DECODE(temporary,'N','No',temporary),
     nested,
      'N/A' extern 
FROM 
     Remote DBA_object_tables 
WHERE 
     owner LIKE UPPER('%&&owner%')
UNION
SELECT 
     Owner,
     'None' tablespace_name,
      'N/A' Iot_name,
      'N/A' logging,
      'N/A' partitioned,
      'N/A' Iot_type,
      type_owner table_type_owner,
      type_name table_type,
      'N/A' temporary,
      'N/A' nested,
      'Yes' extern
FROM
     Remote DBA_external_tables
WHERE
     Owner LIKE UPPER('%&&owner%');           
SPOOL OFF
SET VERIFY ON LINES 80 PAGES 22 feedback ON
TTITLE OFF
UNDEF OWNER
CLEAR COLUMNS

